Can Access Query be restricted to Records at start of query and not newly added records.
I have VBA code that loops through the records in a query and writes an exact duplicate of the record with the dollar amount reversed. This is a local temporary table for an accounting application for doing inter-company eliminations. The problem is that the query is not confined to the original set of records that fit the criteria at the beginning of the Loop but also includes the newly added "reversal" records. It will never stop finding records!
What is the best method to restrict the query to the records that satisfy the criteria initially and not continually getting the newly added records? Incidentally, this caused my code to write records until Access was out of room and Access helpfully solved the problem of too much data by deleting all of my code modules!!! I lost all of the code that I wrote that day and had to go to prior day's backup!
One method I used is to get the maximum "AutoNumber" in the table and restrict the query to records with an AutoNumber less than or equal to this maximum. Is there any other way to solve this problem?
I wouldn't use a query to create the records. I would use the AfterUpdate event of the form where the records are created. That way, you create an offset for each record you add.
If you want to do this after the fact with a query, you'll need to add a batch id to the records so you can select just records from the batch.