Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Looping through 2 Recordsets

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access
View previous topic :: View next topic  
Author Message
dawn
Master Poster


Joined: 26 Jun 2004
Posts: 311
Location: Mishawaka, IN

PostPosted: Tue Oct 07, 2008 4:03 pm    Post subject: Looping through 2 Recordsets Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group