Link to home
Start Free TrialLog in
Avatar of RCUllrich
RCUllrichFlag for United States of America

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of omgang
omgang
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"Original" doesn't have any meaning.  You can't query on it.  You would need some data field that defined the set of records you wanted.

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.
You should use DAO for this. If you have a form where you display the records to be "reversed", here is a direct - and very fast - method:

Copy records

/gustav
Avatar of RCUllrich

ASKER

This is a reporting issue not a form issue. These are not permanent records in our SQL Server database but records accumulated in a local access table for reporting.

It may have helped you if I explained the process for creating this report.
To create a Consolidated Balance Sheet for a Parent company with multiple "child" companies owned in various percentages I first retrieve all the Year-to-date accounting records for all the companies and place them in a temporary table, then I apply the GAAP (Generally Accepted Accounting Principles) elimination rules to reverse records. We have 18 account pairs that eliminate "Due to/Due From" records within the consolidation group of companies. I apply each rule to the records in the temporary table before I Print the Balance Sheet report.

I think the solution would be OM Gang's suggestion to add a field that flags these original accounting records and then use that in the WHERE clause of my query. As a result, the reversed records would never be included in the query.
Thanks for all your suggestions.