Nested looping and Recordsets

[k4ghg]

I have two files and want to update a field from one file based on the "Number" filed ("Num" is second file. I tried using a nested do loop to test my code but it seems that only one loop works. Any suggestion would be appreciated. Thanks Ron

Sub description()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Data")
Set rs2 = db.OpenRecordset("ABC")

rs1.MoveFirst

Do Until rs1.EOF
' test loop - see first record
Debug.Print rs1![Number], "1"

Do Until rs2.EOF
If rs1![Number] = rs2!Num Then
' Testing loop
Debug.Print rs1(0), (1)
End If
rs2.MoveNext
Loop
rs1.MoveNext
rs2.MoveFirst
Loop

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

End Sub

 

[MajP]

Sub description()
  Dim rs1 As DAO.Recordset
  Dim rs2 As DAO.Recordset
  Set rs1 = CurrentDb.OpenRecordset("Data")
  Set rs2 = CurrentDb.OpenRecordset("ABC")
 
  Do While Not rs1.EOF
    rs2.MoveFirst
    Do While Not rs2.EOF
        Debug.Print rs1!num1 & " " & rs2!num1
      rs2.MoveNext
    Loop
    rs1.MoveNext
  Loop
End Sub

 

[RoyVidar]

You are looping two recordsets (two tables), and you wish something to occur whenever there's a match - an equal value - in the two tables?

Wouldn't that be achieved more effectively with a query?

You should, in my opinion, reduce recordset approaches to the absolute minimum, an use queries in stead whenever possible, which is much more effective.

There's probably more going on than this, but the most simple version might look something like either

[tt]UPDATE ABC INNER JOIN Data ON ABC.Num = Data.Number SET ABC.TheField = Data.TheField[/tt]

or

[tt]UPDATE Data INNER JOIN ABC ON Data.Number = ABC.Num SET Data.TheField = ABC.TheField[/tt]

Roy-Vidar