Progress Bar


I am wanting to add a progress bar to a form that mimics the progress meter in the status bar - to show progress while runnning queries or generating reports. The only thing I can think of it to read the status bar progress meter from the form timer event.

Is there a better way?

Who is Participating?
thenelsonConnect With a Mentor Commented:
LittleWoofer,  (sorry I can't help myself)

For queries:
The SQL parser does not pass information to VBA so there is no way to get the current record.  Also while the SQL parser is running, code, including timers, are suspended so you can't do an elapsed time computation.  To try that, create a query that takes a long time -- I used a make table query with 5 unjoined tables resulting in 6.8 million records created.  Set the timer interval to 100.  Then try this code:

Private Sub Form_Load()
DoCmd.SetWarnings False
Debug.Print "starting query", Timer
DoCmd.OpenQuery "tempquery"
Debug.Print "query complete", Timer
Me.TimerInterval = 0
End Sub

Private Sub Form_Timer()
Static timercount As Integer
Debug.Print "Form_Timer: "; Timer, "count: "; timercount
timercount = timercount + 1
End Sub

The above code resulted in
starting query               34906.11
query complete               34990.82
So my query took 84.71 seconds to complete but the timer event, set to fire every 0.1 seconds never fired.  This is because the VBA suspended while the query ran.  With VBA suspended, there is no way to display the progress of the query execution.

For a report:
You could put some code in the detail format section and/or any other section that is taking time to progress.  Since I wouldn't know how much work it takes to complete the job, I wouldn't use a progress bar.  Instead I would use a color bar that sweeps back and forth, a clock with revolving hands, the old DOS slash, vertical bar, back slash, dash routine, or my favorite: a little guy tapping his fingers, looking at his watch, yawning, tapping his fingers....
NatchiketConnect With a Mentor Commented:
You can use the microsoft progressbar activex control, look in the toolbox in from design under more controls
however the process that is being 'progressed' must be under programatic control, and not automomous e.g. a self contained query
WilloWombleAuthor Commented:
Yeah, I know how to add/use the progress bar control.
I was hoping to be able to somehow hook in to the process and determine it's progress.
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

NatchiketConnect With a Mentor Commented:
I regret to announce that there is no way (as far as I know) of hooking into the system progress bar.
thenelsonConnect With a Mentor Commented:
No you cannot create a progress meter for runnning queries or generating reports.  To create a progress meter, you would need to be able to keep track of the progress in VBA but runnning queries and generating reports are Access internal processes and Access does not give us information about the progress of the processes.

You would be able to create a progress meter of sorts for a part of the generating of a section of a report but it would not be accurate sine you would not know how much work is required to complete the task.  Even Access does not know that. Notice that frequently the runnning of a query or generating of a report completes before the Access progress meter reaches the end.  Sometimes the Access progress meter reaches the end before the task is done.  The only way for Access to know how much work is required is to complete the runnning query or generating report progress on the specific data.  So Access world be able to create an accurate progress meter the second time it ran the query or generated the report, but it would not have an accurate meter the first run through.

You also would not be able to create a progress meter by reading the status bar progress meter from a form timer event.  Form timer events suspend while a query runs (I am not sure if that is the case during the generation of a report).  Further, you would not be able to read the status of the progress bar.

So the answer to your question is no. If you search EE, you will see that many others have asked this question and unfortunately the answer has always been no.
Question for Nelson,
Why couldn't you estimate, through  several trial runs, how long a particular process takes and use the elapsed time as compared to total estimated time as your progress meter, or better yet, why couldn't the record count (for queries, etc) for the current record vs total records be used ?
Thanks for that excellent, detailed explanation. You must have the second fastest typing fingers on EE ...second only to Leigh Purvis..............
WilloWombleAuthor Commented:
That's a shame, especially that VBA is suspended (I thought nothing stopped a modal form timer..obviously I was wrong)

Thanks for all your help!

Cheers :o)
Your Welcome!
>I thought nothing stopped a modal form timer
I have found dropping a laptop will do this pretty well ;-(
All Courses

From novice to tech pro — start learning today.