By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,758 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

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

P: 223

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.

Expand|Select|Wrap|Line Numbers
  1. 'Update Routine Feeders
  2. 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;"
  4. Set HATTrecords = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)
  6. HATTrecords.MoveFirst
  7. CAMPrecords.MoveFirst
  9. 'Update the records in HATT with CAMP records
  10. While Not HATTrecords.EOF
  11.     If Not CAMPrecords.EOF Then
  12.         If CAMPrecords("LOCATION_CODE") = HATTrecords("FeederNumber") Then
  13.             HATTrecords.Edit
  14.             HATTrecords("MilesBilled") = CAMPrecords("MilesBilled")
  15.             HATTrecords.Update
  16.             HATTrecords.MoveNext
  17.         End If
  18.         CAMPrecords.MoveNext
  19.     Else
  20.         CAMPrecords.MoveFirst
  21.     End If
  22. Wend
  24. 'Close the recordsets
  25. HATTrecords.Close
  26. CAMPrecords.Close
  28. DoCmd.Close acForm, "frmRunningQuery"
  29. MsgBox "Done"
Feb 27 '12 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 15k+
P: 31,271
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.
Feb 27 '12 #2

P: 223
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.
Feb 27 '12 #3

Expert Mod 15k+
P: 31,271
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?
Feb 27 '12 #4

P: 223
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.
Feb 27 '12 #5

Expert Mod 15k+
P: 31,271
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).
Feb 27 '12 #6

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

Expand|Select|Wrap|Line Numbers
  1.     'Update the records in HATT with CAMP records
  2.     HATTrecords.MoveFirst
  3.     Do While Not HATTrecords.EOF
  4.         CAMPrecords.MoveFirst
  5.         Do While Not CAMPrecords.EOF
  6.             If CAMPrecords("LOCATION_CODE") = HATTrecords("FeederNumber") Then
  7.                 'Update HATT
  8.                 HATTrecords.Edit
  9.                 HATTrecords("MilesBilled") = CAMPrecords("MilesBilled")
  10.                 HATTrecords.Update
  11.         Exit Do 'The match is find and can't be others
  12.             End If
  13.             CAMPrecords.MoveNext
  14.         Loop
  15.         HATTrecords.MoveNext
  16.     Loop
  18.     'Close the recordsets
  19.     HATTrecords.Close
  20.     CAMPrecords.Close
  22.     DoCmd.Close acForm, "frmRunningQuery"
  23.     MsgBox "Done"
I update your code in order to show you how can be done but using an update query is much much faster.
Feb 28 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.