Access VBA DAO recordset loop using two recordsets

[MMcCarthy]

The following code is simply an example of some code that processes through two recordsets. It can be helpful for anyone curious as to how to start processing with recordsets (Which objects to refer to & how to set them up correctly etc), as well as for those who want a quick run-through of some basic multi-recordset logic.

Function yourFunctionName() 
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

  Set db = CurrentDb() 
  Set rs1 = db.OpenRecordset("Query1") 
  Set rs2 = db.OpenRecordset("Query2")

  If rs1.RecordCount=0 Then Exit Sub
    
  rs1.MoveFirst 
  ' loop through each record in the first recordset
  Do Until rs1.EOF
    ' If matching record is found then update field in 
    ' second recordset to value you determine
   If rs2.RecordCount=0 Then Exit Sub
    rs2.MoveFirst
    Do Until rs2.EOF
      If rs1![FieldName] = rs2!FieldName Then
        rs2.Edit
        rs2![FieldName] = 'Your Value'
        rs2.Update
      End If
      rs2.MoveNext
    Loop
    rs1.MoveNext
  Loop

  rs1.Close 
  rs2.Close 
  Set rs1 = Nothing
  Set rs2 = Nothing
  Set db = Nothing

End Function

Remember this is just an example and not by any means the only way of achieving this.

[william.purcell]

This was a clear, concise example for a novice like me. Thanks for the effort. I sure wish I had seen this on Friday.
Bill

[Sunshard]

This was really helpful, so thank you for posting.

Brian

[ahmedtharwat19]

perfect code

i use it in my project

thank you

ahmed tharwat

[Avi Pawar]

This the best example source code for me. I am going to use it in my project. I am sure, after making some changes I will get the result I want. Thanks a lot