Code Samples for Businesses, Schools & Developers

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

StartForm
1.   using a solid bar - in this case coloured green. Progress is based on a series of events (update queries).

ProgressBar1

2.   using a solid bar - in this case coloured magenta. Progress is shown using a timer event

ProgressBar2

3.   using an image - in this case using colours from a flag.

ProgressBar3

4.   using an image - in this case using a gradient fill.

ProgressBar4

4.   Same design as form 4 but starts automatically when the form loads

ProgressBar5



Create Your Own Progress Bars

In each case, the progress bar itself consists of 3 equal sized controls placed on top of each other

ProgBarComponents
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