Resetting Seek Criteria Inside Loop

[Mike_10]

I'm looking for a way to reset my .seek method criteria inside an outer loop so that the inner loop will start over with a new set of account numbers. See below:

    'Run through each unique account number and assign values to each field
    For Gi = 1 To UniqueAccountNumbersCOL.Count
        Debug.Print "Gi = " & Gi
        MasterRS.MoveFirst
        MasterRS.Index = "ACCOUNT"
        MasterRS.Seek "=", UniqueAccountNumbersCOL.Item(Gi)
        Debug.Print "MasterRS ACCOUNT = " & MasterRS.Fields("ACCOUNT")
        Debug.Print "Unique Account Number = " & UniqueAccountNumbersCOL.Item(Gi)
        Do While MasterRS.Fields("ACCOUNT") = UniqueAccountNumbersCOL.Item(Gi)
            'Do a bunch of stuff
            MasterRS.MoveNext
            If MasterRS.EOF Then
                Exit Do
            End If
        Loop
        'Do a bunch more stuff
    Next Gi

Immediate Window Output:
Gi = 1
MasterRS ACCOUNT = 016990020115
Unique Account Number = 016990020115
Gi = 2
MasterRS ACCOUNT = 016990020115
Unique Account Number = 021940010180
Gi = 3
MasterRS ACCOUNT = 016990020115
Unique Account Number = 057370400042

What I am trying to get it to output is:
Gi = 1
MasterRS ACCOUNT = 016990020115
Unique Account Number = 016990020115
Gi = 2
MasterRS ACCOUNT = 021940010180
Unique Account Number = 021940010180
Gi = 3
MasterRS ACCOUNT = 057370400042
Unique Account Number = 057370400042

 

[arnelgp]

test if there is a Match:

    'Run through each unique account number and assign values to each field
    MasterRS.Index = "ACCOUNT"
    For Gi = 1 To UniqueAccountNumbersCOL.Count
        Debug.Print "Gi = " & Gi
        MasterRS.MoveFirst
        MasterRS.Seek "=", UniqueAccountNumbersCOL.Item(Gi)
        If Not .NoMatch Then
            Debug.Print "MasterRS ACCOUNT = " & MasterRS.Fields("ACCOUNT")
            Debug.Print "Unique Account Number = " & UniqueAccountNumbersCOL.Item(Gi)
            Do While MasterRS.Fields("ACCOUNT") = UniqueAccountNumbersCOL.Item(Gi)
                'Do a bunch of stuff
                If MasterRS.EOF Then
                    Exit Do
                End If
                rsMaster.MoveNext
            Loop
        End If
        'Do a bunch more stuff
    Next Gi

 

[Mike_10]

I edited the code in my post above because some of the indention did not paste correctly and because I inadvertently left out MasterRS.MoveNext inside the inner loop, it is there in the actual code, otherwise it looks like I had a perpetual loop. But @arnelgp, I noticed that you moved that after the If MasterRS.EOF statement, mine is before and I don't understand why it would go after. Should I change mine?

Other than that, I did everything else the way you have it by moving the .Index before the 1st loop and added the If Not .NoMatch which is now giving me a compile error "Invalid or unqualified reference". Seems like we're missing something in front of the .NoMatch portion right? I'm not sure what is supposed to go here though. I tried If Not MasterRS.NoMatch but I get the exact same results still.

 

[Pat Hartman]

If your loop processes all records, you might find that basing the recordset on a query that SORTS the records in the order you want to process them will be simpler and more efficient. If your loop does not process all records, use a query with criteria that is variable and modify the criteria each time you want to go through a new set of data.

 

[Mike_10]

If your loop processes all records, you might find that basing the recordset on a query that SORTS the records in the order you want to process them will be simpler and more efficient. If your loop does not process all records, use a query with criteria that is variable and modify the criteria each time you want to go through a new set of data.

MasterRS contains all records, and was opened with ascending ordering applied by account number, which is the first field (second after the Access defined ID). Often times an account number is used multiple times in that recordset. So UniqueAccountNumbersCOL is a collection of each unique account number, all of which are processed in the outer loop, and as you can see from my debug output, is already ordered ascending. For each unique account number I'm seeking only the MasterRS account numbers that match, and then pulling info from each of those records to do stuff with it. The problem is when I get to my second unique account number and try to seek only the MasterRS account numbers that match, it's not resetting from the first, so it still only has the records that match the first unique account number.

To your second point about using a query with criteria that is variable, I thought that was what I was doing by setting up the seek method, just didn't realize it would get stuck on the first set of results. Maybe I'm not understanding your suggestion here?

Please don't hesitate to point out something if you think I'm not understanding. I am open to changing my strategy, just really want this thing to work.

 

[Pat Hartman]

I don't use this method. It is more efficient to process a recordset sequentially than to use methods such as seek. If you need additional data from a different table, create a query that joins the two tables, sort it so that the sets of records fall together. Then your logic can compare a value to the previous record to see if you are still withing the same group.

If you want to upload the database (compact and zip first), I'll take a look at your loop and see if I can see the problem.