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?
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 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.
I regret to announce that there is no way (as far as I know) of hooking into the system progress bar.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

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