Experts:
I need some assistance with tweaking/validating my CASE statements which are used to update a record set. Please see VBA code below...
Code:
Public Sub METHOD_2_FOR_INJ_TYPE()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim n As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("02_tbl_INJ_TYPE_After")
Do While Not rs.EOF
rs.Edit
Debug.Print rs!INJ_INJURY_TYPE_TIER1_ID & " " & rs!INJ_INJURY_TYPE_TIER2_ID
'Increase counter
n = n + 1
Select Case rs!INJ_INJURY_TYPE_TIER1_ID
Case 45
rs!INJ_INJURY_TYPE_TIER2_ID = 85
Case 105
rs!INJ_INJURY_TYPE_TIER2_ID = 110
Case 135
rs!INJ_INJURY_TYPE_TIER2_ID = 140
Case 285
rs!INJ_INJURY_TYPE_TIER2_ID = 15
Case 290
rs!INJ_INJURY_TYPE_TIER2_ID = 25
Case 295
rs!INJ_INJURY_TYPE_TIER2_ID = 170
Case Null
Select Case rs!INJ_INJURY_TYPE_TIER2_ID
Case 5 To 15
rs!INJ_INJURY_TYPE_TIER1_ID = 285
Case 20 To 25
rs!INJ_INJURY_TYPE_TIER1_ID = 290
Case 30 To 100
rs!INJ_INJURY_TYPE_TIER1_ID = 45
Case 105 To 110
rs!INJ_INJURY_TYPE_TIER1_ID = 105
Case 115 To 140
rs!INJ_INJURY_TYPE_TIER1_ID = 135
Case 145 To 170
rs!INJ_INJURY_TYPE_TIER1_ID = 295
End Select
End Select
rs.Update
rs.MoveNext
Loop
MsgBox n & " INJ_INJURY_TYPE records have been updated.", vbInformation, "Status"
End Sub
Per attached JPG "Test Results", the first 6 records are converted correctly. That is, on TIER2, I converted 1st record = 120 into 85.
Now, once I get to the 7th record (NULL value in TIER1), I expected the code to replace the Null value with 285 (1st "child" CASE statement within the "parent" CASE statement). Unfortunately, none of the records which contain a NULL value in TIER1 are updated.
My question: How do I need to restructure my CASE statements so that the null value in TIER1 are updated to 285, 290, 45, 105, 135, 295?