Looping through records (A97/NT4.0 )

Topic

New Reply

THis is an easy one for all you wizards out there but I am a newbie and need a little basic help. I am trying to loop through a recordset using vba code in a module. The purpose of this is to check for an archieve flag. If it does not exist, append that record to another table. I it does exist I need to leave it alone. Then at the same time count all the records that are being appended to the table. I have the basic loop process down but am falling way short in trying to count the number of records. Can anyone help?

Thanks
Kevin

Reply | Quote

Replies

Why not use an append query instead, using the flag as a selection criteria? This will give you the total at the end.

But in your loop, you just need to have defined a counter in your code:
Dim lngC as Long

Then in your loop, every time you write to your archive table, just do:
lngC = lngC + 1

After you exit the loop, just put this to see the number:

MsgBox (“No. of records archived = ” & lngC, vbexclamation)

Reply | Quote

Thanks Mark,
I took your reply into consideration and developed queries that first append the information and then flag the archieve tag. How do I get the total from the query to display in the message box you described in the loop process. Would I add a count function in the query (if thats possible) and then just display that result or should I capure that some other way. As you can see, I’m kinda confused about that part. Thanks
Kevin

Reply | Quote

>>I took your reply into consideration and developed queries that first append the information and then flag the archieve tag. How do I get the total from the query to display in the message box you described in the loop process. <<

Most times, an append query will tell you how many lines were appended (and how many weren't for various reasons). Other than that, you would have to do a DCount using the same criteria as the query, then display that value in the message box.

Reply | Quote