Access 2010 Suspend Form Event Until Report Is Closed

Hi Folks,
The attached code extracts data to a Table (STEP_100) , displays a Report in preview based on the data in the Table (STEP_200) and then deletes the data in the Table (STEP_800).

When the Report displays in preview and the code continues and deleties the data in the Table before all of the data can be displayed correctly in the Report.

How can I suspend the code running after the Report is displayed in preview (end of STEP_200) until the Report is closed and then resume the processing to delete the data in the Table (STEP_800)?

Bob C.
Bob CollisonSystem ArchitectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
There are methods to let the application "sleep until .." but the simplest method would be to move the call of the clean-up function to the OnClose event of the report.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
/gustav's idea is fine, but may stop you from reusing report in another context.
Gustav BrockCIOCommented:
Here is full code for a "suspension" function for a form: OpenFormDialog. Browse to paragraph Simulating dialogue mode

Replace the CurrentProject.AllForms with CurrentProject.AllReports and you have a OpenReportDialog function.

You may even use the time-out function to wait for, say, five seconds if that is enough for your report to settle.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hamed NasrRetired IT ProfessionalCommented:
One way: Try modifying one line in your code, DoCmd.OpenReport stDocName_R49060, acPreview

DoCmd.OpenReport stDocName_R49060, acViewPreview, , , acDialog

Open in new window

Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,

Thanks for all of your suggestions.
- The Timer Solutions are not desirable for this situation.
- The acDialog Solution works except that the previewed report cannot be resized which is not desirable.

The best solution for this situation is to move the emptying of the table to the Form Close Event because:
- The Called Load procedure empties the Table before loading it.
- Each record that is loaded contains three fields that ensure that the record relates to only one Unique User (logon User), Object (specific Report) and Process (Form running the report).

Bob C.
Dale FyeOwner, Developing Solutions LLCCommented:
I use the method described by hnasr.

The acDialog value used in the WindowMode argument will prevent code that follows that line from running until the report is closed.  Works the same way.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.