Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Looping Through 2 Recordsets

Discussion in 'Information Technology' started by SamboRambo02, Jul 28, 2006.

  1. SamboRambo02

    SamboRambo02
    Expand Collapse
    Guest

    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!
     
  2. Loading...


  3. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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.
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand

    "SamboRambo02" <Sami_G02@hotmail.com> wrote in message
    news:1153256840.270812.309900@b28g2000cwb.googlegroups.com...
    >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!
    >
     

Share This Page