Update Table 1 From Table 2

[dbaccess]

I have a main database with about 20 fields. One of the field is default access record "ID", which is always unique. Another field is employee ID, which can be repetitive over time.

Once I get a 'case', I enter the info in the main DB. However, initially I get info for only 10 fields, which includes the employee ID. I note the unique ID that access creates, and enter it in a dummy table along with the employee id. A few days later, I get the information for the remaining 10 fields. At that time what I do is dump that remaining info in the dummy tabl alongisde the employee ID and record ID. Then I use a simple "command button" to have access update the remaining fields in the main table using the criteria:

"Update remaining fields where the "EMPLOYEE ID" and "RECORD" from DUMMY table matches the same info in the main table". Unfortunately, the code is not working.

Fields in Main table: "EmpID" and "ID"
Fields in Dummy table: "Employee ID" and "RECORD"

I am not sure which is the best method to do this?

1. With rst?
2. DLookup?
3. FindFirst?

Any help is much appreciated. Here is the code

Private Sub Command1164_Click()
Form.Refresh

Dim dbs As Database
Dim rst As Recordset
Dim rst2 As Recordset2

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("MainTable") 'This is the MAIN record where I have created Employee ID and DB creates default record ID
Set rst2 = dbs.OpenRecordset("DummyTable") 'This is the DUMMY table where I have created dummy fields RECORD and Employee ID

If Nz(DCount("[Employee ID]", "DummyTable"), 0) = 0 Then
MsgBox "There is nothing to import.", , "ERROR"
Form.Refresh

Else

DoCmd.OpenQuery "CaseFormatting"

rst.MoveFirst

Do While rst2.EOF = False
If rst.[EmpID] = [rst2.EmployeeID] And rst.[ID] = [rst2.Record]
rst.Edit
rst![BeneGender] = rst2![Gender]
rst![EmPEmailId] = rst2![EmployeeesEmailID]
rst![BeneDateofBirth] = rst2![Date of Birth (mm/dd/yyyy)]
rst![BeneUSPhone] = rst2![Mobile Phone]
rst![BeneCountryOfBirth] = rst2![Country of Birth]
rst![BeneCountryOfCitizenship] = rst2![Country of Citizenship]
rst.Update
rst.MoveFirst
rst2.MoveNext
End IF
Loop

rst2.MoveFirst
Do While rst2.EOF = False
rst2.Edit
rst2.Delete
rst2.MoveNext
Loop

dbs.Close
Set dbs = Nothing
Set rst = Nothing
Set rst2 = Nothing
Form.Refresh
End If
End Sub

 

[Ranman256]

you dont do updates via loops.
you use an update query.

 

[dbaccess]

Thank you very much Ranman256.

Since I may have to update multiple records in main table in a single go, drawing the data from dummy table, would an update query work?

If yes, would you know what the criteria should be for the update query?

 

[Ranman256]

you join the 2 tables on the common field,
set query to update,
update the table.field.