Version 2.2 First Published 15 June 2019 Last Updated 23 May 2024
Progress bars are often used to indicate the progress of a lengthy procedure containing many separate steps e.g. SQL statements or queries or a repeated code loop
However, in certain situations, a form timer event can be used to show progress
Both of these approaches are demonstrated in the attached example app.
Adding a progress bar helps users know the approximate time a task will take to complete
However, using a progress bar will also slightly increase the time needed to complete the task!
The example app attached shows different methods of using a progress bar on a form
NOTE: The code works in both 32-bit and 64-bit Access
Example App
The example app opens to a start form with links to five different variations of progress bars
1. using a solid bar - in this case coloured green. Progress is based on a series of events (update queries).
2. using a solid bar - in this case coloured magenta. Progress is shown using a timer event
3. using an image - in this case using colours from a flag.
4. using an image - in this case using a gradient fill.
4. Same design as form 4 but starts automatically when the form loads
Create Your Own Progress Bars
In each case, the progress bar itself consists of 3 equal sized controls placed on top of each other
a) Label lblProgressCaption - shows % completed - placed at the top (optional)
b) Rectangle boxProgressTop - this is the bar which grows to indicate progress
c) Rectangle boxProgressBottom - placed at the bottom & hidden
This manages the maximum size of the progress bar
Another optional label lblHelpText can be used to display additional info e.g. Step 8 of 15 ...
Three functions are used with each progress bar
1. SetupProgressBar - used at the start of the procedure to display the bar and start the process
2. UpdateProgressBar - used after each step in the procedure
3. HideProgressBar - used at the end of the procedure to hide the progress bar and reset the counter
CODE:
Option Compare Database
Option Explicit
Dim intMaxLength As Integer
Dim sngIncrement As Single
Global N As Long, iCount As Long, RI As Integer
Global frm As Access.Form
'##################################################
'module to manage progress bars for multiple forms
'##################################################
Public Sub SetupProgressBar(frm As Form)
On Error GoTo ErrHandler
N = 0
If iCount = 0 Then iCount = 50 'default value if not set on host form
intMaxLength = frm.boxProgressBottom.Width
sngIncrement = frm.boxProgressBottom.Width / iCount
'v2.2 - RI (reciprocal increment) used to manage progress for very large numbers of events where sngIncrement < 0.5
RI = Int(1 / sngIncrement)
frm.boxProgressTop.Width = 0
frm.lblProgressCaption.Caption = "0%"
frm.boxProgressBottom.Visible = True
frm.boxProgressTop.Visible = True
frm.lblProgressCaption.Visible = True
frm.lblProgressCaption.ForeColor = vbBlack
frm.Repaint
DoEvents
ExitHandler:
Exit Sub
ErrHandler:
'err 2475 = the form listed isn't active; err 2467 = object closed
If Err = 2475 Or Err=2467 Then
Exit Sub
Else
MsgBox "Error " & Err.Number & " in SetupProgressBar procedure : " & Err.Description
Resume ExitHandler
End If
End Sub
'----------------------------------------------------
Public Sub UpdateProgressBar(frm As Form)
'----------------------------------------------------
' % caption label fore color changes at 55%
'----------------------------------------------------
On Error GoTo ErrHandler
'update progress bar
N = N + 1
If frm.boxProgressTop.Width < intMaxLength Then
DoEvents 'needed to let computer continue with other tasks
'v2.2 - modified code to handle very large numbers of steps where sngIncrement < 0.5
Select Case RI
Case Is < 2
frm.boxProgressTop.Width = (frm.boxProgressTop.Width + sngIncrement)
Case Else
If N Mod RI = 0 Then frm.boxProgressTop.Width = (frm.boxProgressTop.Width + (RI * sngIncrement))
End Select
frm.lblProgressCaption.Caption = Int(100 * (frm.boxProgressTop.Width / intMaxLength)) & "%"
'change colour of progress bar caption text at 55%
If frm.boxProgressTop.Width / intMaxLength > 0.55 Then frm.lblProgressCaption.ForeColor = vbYellow
End If
frm.Repaint
DoEvents
ExitHandler:
Exit Sub
ErrHandler:
'err 2475 = the form listed isn't active; err 2467 = object closed
If Err = 2475 Or Err=2467 Then
Exit Sub
Else
MsgBox "Error " & Err.Number & " in UpdateProgressBar procedure : " & Err.Description
Resume ExitHandler
End If
End Sub
'----------------------------------------------------
Public Sub HideProgressBar(frm As Form)
On Error GoTo ErrHandler
'Hide progress bar
frm.boxProgressBottom.Visible = False
frm.boxProgressTop.Visible = False
frm.lblProgressCaption.Visible = False
iCount = 0
N = 0
ExitHandler:
Exit Sub
ErrHandler:
'err 2475 = the form listed isn't active; err 2467 = object closed
If Err = 2475 Or Err=2467 Then
Exit Sub
Else
MsgBox "Error " & Err.Number & " in HideProgressBar procedure : " & Err.Description
Resume ExitHandler
End If
End Sub
Each function uses a form reference and are called from your form(s) like this:
SetupProgressBar Me ; UpdateProgressBar Me ; HideProgressBar Me
For example, click a cmdStart button to run some code with a progress bar
CODE:
Private Sub cmdStart_Click()
If Me.cmdStart.Caption = "Start" Then
Me.cmdStart.Caption = "Stop"
'enter number of steps to be run e.g. 5
iCount = 5
SetupProgressBar Me
'step 1 - run some code here e.g. update query & update progress bar
CurrentDb.Execute "UPDATE . . . ", dbFailOnError
UpdateProgressBar Me
DoEvents 'pause to allow screen to update
'step 2 - run some more code here e.g. append query & update progress bar
CurrentDb.Execute "INSERT . . . ", dbFailOnError
UpdateProgressBar Me
DoEvents
'step 3 - run some more code here e.g. update query & update progress bar
CurrentDb.Execute "UPDATE . . . ", dbFailOnError
UpdateProgressBar Me
DoEvents
'step 4 - run some more code here e.g. append query & update progress bar
CurrentDb.Execute "INSERT . . . ", dbFailOnError
UpdateProgressBar Me
DoEvents
'step 5 - run some more code here e.g. delete query & update progress bar
CurrentDb.Execute "DELETE . . . ", dbFailOnError
UpdateProgressBar Me
DoEvents
'pause briefly to show completed
Me.LblHelpText.Caption = "Updates completed . . . "
DoEvents
'reset form
Me.cmdStart.Caption = "Start"
'hide progress bar and help text
HideProgressBar Me
Me.LblHelpText.Visible = False
Else
'reset form if process stopped by user
Me.cmdStart.Caption = "Start"
HideProgressBar Me
Me.LblHelpText.Visible = False
End If
End Sub
To use a progress bar in your own applications:
a) Copy the module modProgress to your application
b) Add the progress bar controls and (optionally) the textbox used to indicate progress
c) Determine the total number of steps to be monitored by the progress bar
Enter this in the form just before the SetUpProgressBar Me code line
d) Add the line UpdateProgressBar Me after each step
e) Add the line HideProgressBar Me at the end of the series of steps
Download
Click to download: Example Progress Bars Version 2.2 Approx 0.7 MB (zipped)
Download the zip file and unblock it.
For more details, see my article:
Unblock downloaded files by removing the Mark of the Web
Unzip and save the ACCDB file to a trusted location.
Version History
v1.0 24/03/2018 Initial release
v2.0 15/06/2019 Added 3 more example forms
v2.1 16/08/2023 Added Autostart example
v2.2 23/05/2024 Fixed issue where progress bar failed to update for very large numbers of steps (approx 16800+)
YouTube Video
I have created a short video explaining how to create a progress bar to indicate the progress of a lengthy series of events or by using a timer.
You can watch the Build A Progress Bar In Access video on my YouTube channel or click below:
If you liked the video, please subscribe to my Isladogs on Access
channel on YouTube. Thanks.
Feedback
Please use the contact form to let me know whether you found this article / example app useful or if you have any questions.
Do let me know if there are any errors or omissions.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 23 May 2024
|
Return to Example Databases Page |
Return to Top |