- Local time
- Today, 14:45
- Feb 8, 2012
Brother (arnelgp) helped me in writing this code and I am very grateful to him for the help. I have added two new fields in the two tables as well as the query. But I tried a lot to add them to this code and it didn't work. Please help me, thank you very much. Your friend AZ
Dim db As DAO.Database Dim rs As DAO.Recordset Dim strUnifiedNumber As Long Dim strSpecialization As Variant Dim intCounter As Integer Dim strSQL As String Set db = CurrentDb Set rs = db.OpenRecordset("qryUnifiedNumber", dbOpenSnapshot) 'Make sure we have records and then 'make sure we are at the first record If rs.RecordCount < 1 Then MsgBox "No records require an update" Set rs = Nothing Set db = Nothing Exit Sub End If 'rs.MoveFirst rs.MoveLast rs.MoveFirst intCounter = rs.RecordCount MsgBox "You are about to update " & intCounter & " records." 'We need to loop through all of the records 'that our query object found While rs.EOF = False strUnifiedNumber = rs![UnifiedNumber] strSpecialization = rs![Specialization] strSQL = "UPDATE Table2 SET Table2.Specialization = '" & strSpecialization & "' WHERE ((Table2.UnifiedNumber)=" & strUnifiedNumber & ")" db.Execute strSQL, dbFailOnError rs.MoveNext Wend rs.Close Set rs = Nothing Set db = Nothing MsgBox "Complete"