Access VBA, adding Progress Bar in code to allow execution.

I have tried several online tutorials on creating a Progress Bar when executing code, but I haven't been successful in getting it to work. Does anyone have a working solution for either using built-in Progress Bar or maybe a popup Progress Bar?
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.

Bill PrewIT / Software Engineering ConsultantCommented:
Have you tried the built in status bar based one.  It's pretty small and less obvious but can be adequate in some cases.  Here is a small sampole procedure to test, and then notice the right side of the status bar in the main window when running, it runs for 10 seconds.

Sub ProgressMeter()
   Dim i As Integer
   DoCmd.Hourglass (True)
   SysCmd acSysCmdInitMeter, "Working...", 10
   For i = 1 To 10
      SysCmd acSysCmdUpdateMeter, i
      Wait 1
   Next i
   SysCmd acSysCmdRemoveMeter
   DoCmd.Hourglass (False)
End Sub

Sub Wait(s As Integer)
   Dim t As Long
   t = Timer()
   Loop While (Timer() < t + s)
 End Sub

Open in new window


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
toalexsandrAuthor Commented:
Works actually very well. Will it be always for 10 seconds? Can it be adjusted and allow the system to use its processing timing?
Bill PrewIT / Software Engineering ConsultantCommented:
It is driven off a counter that the code controls.  Notice that I set the range to end at 10, and then called the "update" of the meter with number 1, 2, ..., 10 to let it show updates.

What are you trying to have going on while the progress bar is active, are you waiting for the result of a long query, or do you have application logic in VBA that is processing? If it's just a long query that is running then that isn't going to work well for this, since you have no ability to execute VBA code while it is running.

Need to know a little bit more about how and when you want to use this...

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

toalexsandrAuthor Commented:
I want to use it when executing VBA for Backing-up DB or any activated button.
Bill PrewIT / Software Engineering ConsultantCommented:
If it's something where you have VBA code executing during the time you want to display and update it then that can work.

If it's during the time a single statement is executing, like backup up the database I suspect, then you cannot use it effectively there, since you have no VBA code executing while that single statement is executing.

I have some techniques using a form that responds to timer events, but not sure if they would fire while the backup is running.  But even with that approach the problem is you don't know how long the backup will take, so you can't estimate what percentage has been done at any point in time, making updates to the bar not possible for any useful estimate of % complete.

Dale FyeOwner, Dev-Soln LLCCommented:
I've got an article on progress bars located here with a sample form and code.
toalexsandrAuthor Commented:
Thank you for all your help. I will review the article once more because it's very informative but for now, Bills code works perfectly for me. Thanks again.
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

From novice to tech pro — start learning today.