How do I skip over a record in a query once the loop has gone through once?

[anoble1]

Hi,

My question retains to a function I made that grabs a lot of data and compares and makes updates to that same record IF it is in both databases. If it is not in both databases, then it keeps going in a loop and never stops running. I need some help, I don't even know if it's possible to tell if you have been through a recordset more than once. How do you just skip a record once it scans through the other database and can't find it?

Here is what I have FYI, don't know if this helps.

'Update Routine Feeders
strSQL = "SELECT tblFeeder.FeederNumber, tblFeeder.MilesBilled FROM (tblCrews INNER JOIN tblContractors ON tblCrews.Contractor = tblContractors.ContractorID) INNER JOIN ((tblDivisions INNER JOIN (tblSubs INNER JOIN tblFeeder ON tblSubs.SubID = tblFeeder.Sub) ON tblDivisions.Division = tblFeeder.Division) INNER JOIN tblPlanSplitYearFeeders ON tblFeeder.FdrID = tblPlanSplitYearFeeders.FdrID) ON tblCrews.CrewID = tblPlanSplitYearFeeders.Crew WHERE (((tblFeeder.NegotiatedFinishDate) Is Not Null) AND ((tblFeeder.Active)=True)) ORDER BY tblFeeder.FeederNumber;"
 
Set HATTrecords = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)
 
HATTrecords.MoveFirst
CAMPrecords.MoveFirst
 
'Update the records in HATT with CAMP records
While Not HATTrecords.EOF
    If Not CAMPrecords.EOF Then
        If CAMPrecords("LOCATION_CODE") = HATTrecords("FeederNumber") Then
            HATTrecords.Edit
            HATTrecords("MilesBilled") = CAMPrecords("MilesBilled")
            HATTrecords.Update
            HATTrecords.MoveNext
        End If
        CAMPrecords.MoveNext
    Else
        CAMPrecords.MoveFirst
    End If
Wend
 
'Close the recordsets
HATTrecords.Close
CAMPrecords.Close
 
DoCmd.Close acForm, "frmRunningQuery"
MsgBox "Done"
 

[NeoPa]

It seems like you're dealing with separate tables rather than databases, but your question leaves much of your requirement to guesswork. Are you simply updating HATTrecords.MilesBilled to CAMPrecords.MilesBilled for all records where CAMPrecords.LOCATION_CODE = HATTrecords.FeederNumber? If so, then you should really be looking to do that in a simple UPDATE query.

[anoble1]

Sorry I left out a piece of code. Yes 2 databases (HATTRecords & CAMPRecords). The CAMPRecords has the most recent data, I need it copied to HATTRecords. Did't think of an update query. Wow, seems a lot easier.

[NeoPa]

ANoble1:
Yes 2 databases (HATTRecords & CAMPRecords).

You appear to be trying to agree with me yet contradict me at the same time. I assume you mean "Yes. Two tables." If not, you'll have to make your meaning clearer (or not).

I assume that you're now happy with the idea of an UPDATE query and we can leave this where it is?

[anoble1]

I meant Yes 2 Different Databases. Not the same database. But I will give the update query a try if possible.

Yes, thanks for the help.

[NeoPa]

Go for the update query. I was saying it wasn't separate databases, but just separate tables. Whichever the real situation an UPDATE can be made to work, but it will be harder if the data is referenced via different databases. If it really is so, then it would make sense to link the remote data into the one you're working within and then do it within that one database (Still separate tables of course).

[Mihail]

Assuming that the rest of your code is Ok this should do the job.

    'Update the records in HATT with CAMP records
    HATTrecords.MoveFirst
    Do While Not HATTrecords.EOF
        CAMPrecords.MoveFirst
        Do While Not CAMPrecords.EOF
            If CAMPrecords("LOCATION_CODE") = HATTrecords("FeederNumber") Then
                'Update HATT
                HATTrecords.Edit
                HATTrecords("MilesBilled") = CAMPrecords("MilesBilled")
                HATTrecords.Update
        Exit Do 'The match is find and can't be others
            End If
            CAMPrecords.MoveNext
        Loop
        HATTrecords.MoveNext
    Loop
 
    'Close the recordsets
    HATTrecords.Close
    CAMPrecords.Close
 
    DoCmd.Close acForm, "frmRunningQuery"
    MsgBox "Done"

I update your code in order to show you how can be done but using an update query is much much faster.