Looping Through 2 Recordsets

  • Thread starter Thread starter SamboRambo02
  • Start date Start date
S

SamboRambo02

I am trying to loop through 2 recordsets - rsMainTable and rsNewTable
which both have PermID1 as their primary keys. The simplistic thing
I'm trying to do is this -- find matching PermID1's in both tables,
look at their fields and if they're different, check the 'date last
updated' field and keep the newer info. As easy as this sounds, I've
tried 3 different ways but all them have the same problem: it will move
record by record through one table but forget to match the PermID1 to
the other table, i.e. PermID1 #139's first name in NewTable gets saved
in PermID1 #8's first name spot in Maintable. I'm currently using
rsMainTable.FindFirst ([PermID1] = i . i being equal to the PermID1#
the cursor is currently on in NewTable. Its like NewTable.MoveNext is
working fine but then it forgets to find where that equals PermID1 in
MainTable. I've even copied verbatim from textbooks, etc. on how to
use FindFirst. Please help! It's so frustrating!
 
The syntax of your FindFirst should be:

rsMainTable.FindFirst "PermID1=" & i

Also, you should check rsMainTable.NoMatch after the .FindFirst:

If Not rsMainTable.NoMatch Then
' edit and update
End If

It would be much easier to create a query with the two tables joined by
PermID1. Then you have only one recordset to go through.

In fact, it's probably possible to perform the entire operation with a
single update query, selecting those records where NewTable.DateUpdated >
MainTable.DateUpdated, and update each MainTable field with the value in the
corresponding NewTable field.
 
Back
Top