Retrospectively populate an access form field

Question

I have an access database that I have been asked to add a field to (KeyStage). This field will auto populate from the NCY field that is already known and populated. The code for this is;

Dim KeyStage As String
Dim NCY As Integer

NCY = Me.NationalCurriculumYear.Value

If NCY < 1 Then
Me.KeyStage = "Early Years"
ElseIf NCY > 0 And NCY < 3 Then
Me.KeyStage = "KS1"
ElseIf NCY > 2 And NCY < 7 Then
Me.KeyStage = "KS2"
ElseIf NCY > 6 And NCY < 10 Then
Me.KeyStage = "KS3"
ElseIf NCY > 9 And NCY < 12 Then
Me.KeyStage = "KS4"
Else: Me.KeyStage = "Left School"
End If

If I put this into a GotFocus sub, it works fine. The problem is I already have 2000 records and unless I tab through each record, I can't get the KeyStage field to auto populate. I tried putting it in an onLoad sub for the form but that didn't work either. Can anyone suggest how I would approach this?

Thank you in advance for your help.

Is this a continuous form? Why add keystage? Why not have a query? — Fionnuala

Kiran Kiran · Accepted Answer · 2020-02-16T16:55:30

You would need to execute an UPDATE statement against records in the table that hadn't yet been updated. Something like this might work:

UPDATE MyTable AS T

SET T.KeyStage =
    Iif(T.NationalCurriculumYear = 0, "Early Years",
    Iif(T.NationalCurriculumYear >=  1 And T.NationalCurriculumYear <  3, "KS1", 
    Iif(T.NationalCurriculumYear >=  3 And T.NationalCurriculumYear <  7, "KS2", 
    Iif(T.NationalCurriculumYear >=  7 And T.NationalCurriculumYear < 10, "KS3", 
    Iif(T.NationalCurriculumYear >= 10 And T.NationalCurriculumYear < 12, "KS4", "Left School")))))

WHERE T.KeyStage Is Null

Retrospectively populate an access form field

1 Answers