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.
