How to pause the code (VBA) so the process can complete?

BD used Ask the Experts™
I have a subroutine that generates a table of contents.  The subroutine works perfectly.  I execute the code, it opens a report used to develop an index of pages saved in a table of contents table, and then pages down through the report to enter each record's page into the table of contents table.  Once executed, it takes about five seconds for the report to be opened and paged all the way through.

Here is where my troubles start.  I would like for the code to close the report after it has generated the table – so I have added a close report snippet to the code.  The problem I have now is that the code somehow runs all the way through the page down loop and then closes the report before (I am guessing) the processor can complete the actual process.  Thus the table only reflects a page or two of contents before the report is close.

So, is there a way to pause the code while the processor is catching up?
Private Sub B_TOC_Click()
On Error GoTo Err_B_TOC_Click

    Dim PG As Integer
    Dim stDocName As String
    stDocName = "R_TOC_CBL_SHT"
    DoCmd.OpenReport stDocName, acPreview

    Forms!MAIN!C_Ttl = Reports!R_TOC_CBL_SHT.Pages
    ' GO TO PAGE #2
    SendKeys "{PGDN}"

    PG = Reports!R_TOC_CBL_SHT.Pages - 1
    ' DO
    Do While PG > 0

    SendKeys "{PGDN}"
    PG = PG - 1
    DoCmd.Close acReport, stDocName
    Exit Sub

    MsgBox Err.Description
    Resume Exit_B_TOC_Click
End Sub

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can add the statement:

Application.Wait Time + TimeSerial(x, y, z) where x=hours, y=minutes, z=seconds.

to add a 10 second pause, use:    Application.Wait Time + TimeSerial(0, 0, 10)
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

I'm not sure I understand what you are really trying to do here.  It sounds like you have a table for your "table of Contents" and another one for the data that should go in each of those sections.

Why do you think you need to do this by opening a report?  Why don't you just create a query that links the "Table of Contents" records with the appropriate other table, and use that as the source for your report.

If the problem is that you want to display the page that each section begins on in the "Table of Contents", then it might be best to use this query as the source for the report.  Then Group the report the Section# and use the Group header to identify the current page and insert that value back into the table which contains your table of contents.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

I'm confused...

First you say it works "Perfectly"
then you say...
"the table only reflects a page or two of contents before the report is close."

So is it working or not?

Like fyed, am confused are to what you are trying to accomplish with this system.
(or even what it produces...)

If you need a Table of contents in MS Access Reports see here:

Ensure you’re charging the right price for your IT

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



Ragnarok89,  I am getting a MS VB “Compile Error: Method or data member not found."
And it is highlighting the “.Wait” part of your “Application.Wait Time + TimeSerial(0, 0, 10)” code.  Any further recommendations?

Jeff, indeed I did use MS Article Q131588 to accomplish the building of the Table of Contents.  If you refer to step #6 you will find a note: “If you are previewing the report, page through all the pages of the report to ensure that the Print event is triggered for all records.”  I am accomplishing this with the page down loop.  As the report scrolls to each new “CategoryName” (when referring to the article), it enters a new record in the “Table of Contents showing the “CategoryName” and “Page Number”.  That is all taken care of, I am simply trying to automate the process and close this “table of contents” building report.

The code you are viewing is supposed to accomplish starting the table of contents build (the article you stated), then page through the report to the end (there by building the table of contents), and then I want the code to close the table of contents building report as it is no longer needed – I now have my table of contents.  If I do not have the “close report” part in my code, it works fine, but I then have to manually close the report.  If I add the “close report” part of my code – the report is closed before the process is completed to make the report.  If I can delay the closing of the report for ten seconds, everything will be working just fine.
MIS Liason
Most Valuable Expert 2012

Then try inserting DoEvents at strategic points in your code.

For example, right before the "Close Report" code as a start.
What is this code supposed to do? It appears that you are simply opening the report in preview mode, and then paging though the report, and counting down from X to 1 using the PG variable. But what does that achieve? Nothing else is done in the code, and there is no table of contents being created anywhere as far as I can tell.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial