Case Statement

[RussH]

Can you loop through Case Statements?



Currently I have a table with information in field 1 that I need to change and drop into field 2. I created a form with these 2 fields and with VBA code on the onload event and tried oncurrent event. The code is like this:

Select Case [ResultsCourse]
Case [ResultsCourse] = "12 month competency"
ULTICOUCODES = "12MO"
Case [ResultsCourse] = "18 month competency"
ULTICOUCODES = "18MO"

End Select


End Sub

This works great but I have to click on the record for it to run/update the ulticoucodes field.

Is there a way to loop through the entire "resultsCourse" field to test if condition is met and uopdate the ulticodes field if it is?

Thanks in advance

[Bob Fitz]

Perhaps the following code in the form's On Current event would work:

If [ResultsCourse] = "12 month competency" Then
  ULTICOUCODES = "12MO"
ElseIf [ResultsCourse] = "18 month competency" Then
  ULTICOUCODES = "18MO"
End If

You would need to click through every record though.
Alternatively, you could create a function that opens a recordset of the data and and moves from the first record to the last record and make changes to each record as required before moving to the next record.