Report Does Not Pick Up All Data on 2nd Running

[jjlogan]

A very strange happening, perhaps since I changed to Office 2007 - but have Access Office 2010, 32-bit, using VBA and ADO in my module. I don't remember this issue being a problem with earlier versions of Office.
In summary, my unbound report captures all the data expected when report is first run after opening my database. But, if I run the same report again before closing / opening the database, it drops all the data from one of the two sources of data. My data is presented to the report (RecordSource) as an SQL that selects its data from a Union query, where the first query in the Union pulls from a table that the module first deletes all records from and then appends records back, as the ADO recordset is being analyzed record-by-record and then a code is used for each record depending on conditions found. The 2nd query within the Union pulls data from an existing data table that did not have its records deleted and then appended back; the 1st set of data is business travel costs, and the 2nd set is non-travel. Complicated a bit, huh. As for the module, it has two main Sub's: the first asks the user for a organization code (and validates the input), then it transfers control to a second Sub which does the ADO analysis of the first category of data (business travel transactions). In this 2nd Sub, two ADO recordsets are opened: one pulls data from the main table for Travel-only; the 2nd recordset opens a temporary table (that exists) but has just had its records deleted by a vba DoCmd.RunSQL (delete sql string here). With this 2nd recordset, new records are added back to the temp table using .AddNew, !Field Names = xxx, and .Update. After analyzing all the travel transactions and appending the travel data to the temp table, control passes back to the 1st Sub where the Union query is built and assigned to the report's RecordSource. Now this is what is strange: the report opens with all the data from both sources of data (Business Travel from temp table, and non-business data from main table) when the report is run the first time after opening the database OR if I put a pause in the module (the red dot pause) and then hit run within the vba edit window. But, if I don't have a pause in the code and run the report again, or someone else runs it before closing / opening the database, they don't see results from business travel, even though the temp table with the business travel is full at this time (119 records).
If anyone has any input, it will be appreciated. Got to get this fixed because have several users who would be opening this report soon.
Thanks, Jeff

[jjlogan]

A workaround solved this problem. I created a form instead of having all automation behind the report. The form has two command buttons. The first button triggers vba behind form to pull the travel records which had been reviewed with exceptions and did some transforming of the data and appended the temporary travel table with the transformed data. When this step was done a message tells user to click the 2nd command button which calls for the running of the final report (DoCmd.OpenReport). The automation behind the report performs the final data preparation by defining an SQL to pull from the travel table and a 2nd SQL to pull non-travel from the main transaction table. The report is then presented using the Union query. The report now (at last) consistently displays results from both travel and non-travel - whereas before without the form, the results were fine the first time the db was opened, but on the 2nd running of the report it would not include the travel data. Thanks to all who took a look at my first post. I know that it was difficult to get one's hands around it cuz not a common problem (in my experience) and not just a code issue. Jeff

[danny500]

The automation behind the report performs the final data preparation by defining an SQL to pull from the travel table and a 2nd SQL to pull non-travel from the main transaction table. The report is then presented using the Union query.




__________________
danny