orange -- thank you for the feedback.
First, allow me to provide additional background:
- In v1, the VBA worked since I had the required field [MSHP_CLASS_ID] in the same table. Thus, when looping through the recordset (rs), all records/fields are in sync.
- However, in contrast to v1, v2 has the [MSHP_CLASS_ID] in a different table. Now, when looping through the rs, my records are somehow no longer synchronized. That is, prior to your posting the feedback, I actually came up w/ a solution that executes (see below). When comparing the values in the calculated field [PERS_INJURY_COST] between v1 and v2, both versions now have different calculated values for a *subset* of records (while others have the same calculated values in v1 and v2).
Code:
Public Sub Update_PERS_INJURY_COST()
Dim db As DAO.Database
Dim rs_mshp As DAO.Recordset
Dim rs_pers As DAO.Recordset
Dim n As Integer
Set db = CurrentDb
Set rs_mshp = db.OpenRecordset("tbl_MISHAP")
Set rs_pers = db.OpenRecordset("tbl_PERSON")
Do While Not rs_pers.EOF
rs_pers.Edit
'Increase counter
n = n + 1
' **** BUSINESS RULES (calculation of injury costs) ****
Select Case rs_mshp!MSHP_CLASS_ID
'Class A
Case 1
Select Case Nz(rs_pers!PERS_EMPLOYMENT_STATUS2_ID, 0)
' ... more code
rs_pers.Update
rs_mshp.MoveNext
rs_pers.MoveNext
Loop
'Throw message box
MsgBox n & " PERS_INJURY_COST records in table 'tbl_INJURY' have been updated.", vbInformation, "Status"
End Sub
Now, after I modified the code, you just posted a recommendation to use a SQL statement. Where should the SQL statement go in the posted version 2?
Thanks,
EEH