Welcome to SPN

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

Sign Up Now!
  1. Guest ji, please consider donating today!   Become a Supporter    ::   Make a Contribution   
    Monthly (Recurring) Target: $300 :: Achieved: $95

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!
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page