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

toalexsandr
toalexsandr used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Test your restores, not your backups...
Top Expert 2016
Commented:
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()
   Do
      DoEvents
   Loop While (Timer() < t + s)
 End Sub

Open in new window

~bp

Author

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 PrewTest your restores, not your backups...
Top Expert 2016

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

~bp

Author

Commented:
I want to use it when executing VBA for Backing-up DB or any activated button.
Bill PrewTest your restores, not your backups...
Top Expert 2016

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

~bp
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
I've got an article on progress bars located here with a sample form and code.

Author

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.

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