Movefirst not working nested loops,, movefirst is not working so that loop can restar

[naeem ayub]

Do While Not rss.EOF
Do While Not prers.EOF
 
If rss.Fields(0).Value = prers.Fields(1).Value Then
///match found   
some action..............
 
If Not rss.EOF Then rss.MoveNext
prers.MoveFirst          
 
Else
If Not prers.EOF Then prers.MoveNext     /match not found, reched with against another prers value
 
End If
 
Loop
 
prers.MoveFirst
 
//////////some action
 
If Not rss.EOF Then rss.MoveNext
prers.MoveFirst
 
Loop
 
 
 

movefirst is not working so that loop can restard from first record each time, any help would be highly appriciated.

[ADezii]

Subscribing, will return later.

[FishVal]

• What is the type of recordset cursor?
• Thwe code looks quite kludge. What does it actually do?
• If the aim is to find matches, then did you consider to use table join instead of recordsets nested iteration?

[ADezii]

I, like FishVal, am also confused as to exactly what your are looking for. If you are simply looking to find Matches on Fields from 2 Data Sources, using Nested Recordset Loops, then this should at least get you started. The following code will search the 1st Field of every Record in Table1 against possible matches on the 2nd Field in every Record of Table2. This is simply a guideline, kindly make your objectives a little clearer.

Dim MyDB As DAO.Database
Dim rss As DAO.Recordset
Dim prers As DAO.Recordset
 
Set MyDB = CurrentDb
Set rss = MyDB.OpenRecordset("Table1", dbOpenForwardOnly)
Set prers = MyDB.OpenRecordset("Table2", dbOpenSnapshot)
 
With rss
  Do While Not .EOF
    Do While Not prers.EOF
      If .Fields(0) = prers.Fields(1) Then
        Debug.Print "Match on: " & .Fields(0) & " ==> " & prers.Fields(1)
      End If
        prers.MoveNext
    Loop
      prers.MoveFirst
      .MoveNext
  Loop
End With
 
prers.Close
rss.Close
Set prers = Nothing
Set rss = Nothing

[naeem ayub]

Thank you all for your reply.
I am beginner in access VBA, as for the cursor type I have declared recordset as following

Dim precn As ADODB.Connection
Set precn = CurrentProject.Connection
Dim prers As New ADODB.Recordset
prers.ActiveConnection = precn
prers.CursorType = adOpenDynamic
prers.LockType = adLockOptimistic

-Actually It is simple credit based sales and purchase system; I have table receivable _closing which is updated last date of every month with autoexec by using pasted code and saves pending balance.
-Sales table has all the record of transactions made in this month.
Now end of each month previously pasted code will update Receivable _ closing Table using autoexec.
First Case: it will check matching record and add one by one in receivable closing table.
Second Case : A client has pending balance but did not do any new transaction in this month , so one do while loop in the pasted code will check each record of receivable closing against sale. If found it will add
If not found
It means he is a new client or has zero pending balance so it will add record in receivable closing accordingly.
Third case:Some clients would have pending balance in Receivable closing and have no activity in last months, and I want this pending balance to be save again in Receivable closing with new closing date.
So another loop will check this thing by using same recordset “ PRERS “which is not pasted.
So prers have to movefirst many time, which is not working I don’t know why. 
I hope you can help
Thanks