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

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

Who is Participating?
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.

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, Developing Solutions LLCCommented:
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 LiasonCommented:
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:

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

BDAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:

Then try inserting DoEvents at strategic points in your code.

For example, right before the "Close Report" code as a start.

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
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.
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.