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?
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?
Microsoft AccessSQL
Last Comment
RCUllrich
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
Copy records
/gustav
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.
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.
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.