dawn Master Poster
Joined: 26 Jun 2004 Posts: 311 Location: Mishawaka, IN
|
Posted: Tue Oct 07, 2008 4:03 pm Post subject: Looping through 2 Recordsets |
|
|
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.
Code: |
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
|
|
|