Microsoft Access– Adding a Progress Bar

I recently wrote a couple articles about integrating progress bars into application:

Excel (using Conditional Formatting)

Excel - Adding a Progress Bar

I was recently doing some work for a client in which I build an Excel WorkBook that processed a series of large CSV files to clean them up, format the content, … because of their size the process could take several minutes and they contacted me thinking it wasn’t working and that they thought the…

Continue reading →

MS Office (using a Userform)

Microsoft Office – Adding a Progress Bar

So a few days ago I published an article about using conditional formatting to create a progress bar in an Excel Worksheet to display, in real-time, the progress of some process to your users. You can read all the details at: That said, I then pushed a little further to create a reusable progress bar…

Continue reading →

Since Microsoft Access is unique in that is has it’s own native forms, today I would like to demonstrate how we can create a standard form and with a little ingenuity and VBA code, we can create a simple, reusable progress bar to display the progress of iterative and/or multi-step processes.

Per the usual, I started off with a very basic progress bar and it has evolved over time as I’ve added new features/capabilities to make it more adaptive.

At it’s core, it is a simple form with a background rectangle and a superimposed textbox that we make the width of grow as the process evolves.

Then I added so labels to be able to communicate to the user what exactly was going on, which step in the process was happening.

Then I decided I wanted to be able to change the fonts: size, weight, color, family, alignment, …, resize the progress bar at runtime.

Anyways, with a little VBA code we end up being able to generate things like:

The Code

I’m not going to post the code here, for that simply download and review the demo file provided below.

However, at it’s simplest we could use it by doing:

Sub GenericTest1()
    'Generic Testing
    Dim i                     As Long
    Const lNoIteration As Long = 73

    cls_ProgressBar.ProgressBar_Show

    For i = 1 To lNoIteration
        cls_ProgressBar.ProgressBar_Message2 "Iteration " & Chr(10) & i
        cls_ProgressBar.ProgressBar_Progress (i / lNoIteration)
        Sleep 25 'Just for demonstration purposes!
    Next i
    
    cls_ProgressBar.ProgressBar_Message2 "Operation Completed Successfully!"
End Sub

or a more complex example with configurations applied would look like:

Sub GenericTest1()
    'Generic Testing
    Dim i                     As Long
    Const lNoIteration As Long = 73

    cls_ProgressBar.ProgressBar_Show
    cls_ProgressBar.ProgressBar_ProgressOverlay True    'Optional
    cls_ProgressBar.ProgressBar_Resize 150, 325         'Optional Resize the form
    cls_ProgressBar.ProgressBar_Color RGB(253, 109, 13) 'Change progress bar color - Orange
    cls_ProgressBar.ProgressBar_Caption "Export Progress"
    cls_ProgressBar.ProgressBar_Message1 "Exporting data to Excel." & vbCrLf & vbCrLf & "Enjoy!"
    cls_ProgressBar.ProgressBar_Message1_FontParam Align_Left, "Calibri", Weight_Normal, 8, RGB(0, 102, 204)
    cls_ProgressBar.ProgressBar_Message2_Align Align_Center
    cls_ProgressBar.ProgressBar_ProgressValue_Align Align_Center

    For i = 1 To lNoIteration
        cls_ProgressBar.ProgressBar_Message2 "Iteration " & Chr(10) & i
        cls_ProgressBar.ProgressBar_Progress (i / lNoIteration)
        Sleep 25 'Just for demonstration purposes!
    Next i
    
    cls_ProgressBar.ProgressBar_Message2 "Operation Completed Successfully!"
    cls_ProgressBar.ProgressBar_Hide 'Automatically close the ProgressBar
End Sub

This only uses basic VBA functions, uses no references, nor any ActiveX controls and is bitness independent (so it will run equally on 32 or 64-bit installations).

Defaults

One quick comment, instead of always using function to change the look and feel of the progress bar, don’t forget that you can also simply edit the form, change the colors, alignment, fonts, … to make the default what you want. This will simplify your code. That said, the functions are there for those others time when you wish to override the default values.

Customizations

Feel free to make it your own! You now have a solid base illustrating how it is done, but you can add more features as you see fit. Just inspire yourself with what is there and make it suit your specific needs.

Download The Demo File

Feel free to download a 100% unlocked copy by using the link provided below:

Download “Access - Progress Bar” ProgressBar.zip – Downloaded 8020 times – 46.49 KB

Disclaimer/Notes:

If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

All code samples, download samples, links, ... on this site are provided 'AS IS'.

In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.

Page History

Date	Summary of Changes
2022-12-10	Initial Release
2022-12-11	Update Enums to avoid potential conflicts with built-in VBA functions & updated the demo file.

9 responses on “Microsoft Access– Adding a Progress Bar”

Johan Welman December 11, 2022 at 1:39 am

Thank you for this awesome tool! I have an interesting issue though. After importing the Class module into my application, I receive and “Expected Array” error when I compile my application. The error occurs at these 3 string functions:
Function makeNumber(vType As String, vName As String) As String
vType = Left(vType, 1)
vPrefix1 = Left(vName, 2)
vPrefix2 = Right(vName, 1)
It appears to be caused by the Enum declaration in the Class Module:
Enum pTextAlign
General = 0 ‘(Default) The text aligns to the left; numbers and dates align to the right.
Left = 1 ‘The text, numbers, and dates align to the left.
Center = 2 ‘The text, numbers, and dates are centered.
Right = 3 ‘The text, numbers, and dates align to the right.
Distribute = 4 ‘The text, numbers, and dates are evenly distributed.
End Enum
When I comment out “Left” and “Right”, my string functions work.
Is there another way that I should use to extract single characters from a string?
Regards
Johan

Daniel Pineault Post authorDecember 11, 2022 at 6:03 am

The demo file does not contain ‘makeNumber’ so I’m not sure where that is coming from.

Although it compiles and works fine for me in multiple VMs, I have updated the demo file to not use Enums that could conflict with VBA functions (Left, Right, …). So try the new version and hopefully it will resolve this issue for you.
1. Johan Welman December 11, 2022 at 7:32 am
  
  Sorry, Daniel, I should have explained better. makeNumber is in my application, it is a function that builds a customer number or supplier number. It uses the 3 string functions I posted above, to extract certain characters from the strings passed to the function by the calling procedure. This used to work find before I imported the class module for the progress bar. After I imported the class module, the words Left and Right (which I use as part of my string functions) generates the error “Expected Array”. In the meantime, I commented out the enum-declaration and all the bits in the code referring to “pTextAlign” – now my own code works again, but I lost some of the configuration capabilities of the progress bar. Here is a slightly longer extract from my own code:
  
  Function makeNumber(vType As String, vName As String) As String
  
  On Error GoTo Error_Handler
  
  Dim db As Database
  Dim rst As Recordset
  Dim strSQL As String
  Dim vPrefix1 As String
  Dim vPrefix2 As String
  Dim vSeqNo As String
  Dim vCustNo As String
  Dim vCustID As Long
  Dim vSupNo As String
  Dim vSupID As Long
  
  vType = Left(vType, 1)
  vPrefix1 = Left(vName, 2)
  vPrefix2 = Right(vName, 1)
  ***** Lots more here *****
  End Function
  1. Johan Welman December 11, 2022 at 7:35 am
    
    Also, I’ll try the new version tomorrow.
    
    Thanks for this awesome information and assistance.

Binns Patrick May 19, 2023 at 1:59 pm

Hello,
I’m sorry, for me the form is starting, but the bar does not start.
I loaded the demo
Friendly
Patrick
Access 2019

Daniel Pineault Post authorMay 19, 2023 at 2:04 pm

Did you enable the content, place it in a Trusted Location, so the VBA code can run?
Did you Unblock the macro for downloaded files from the internet?
1. Binns May 19, 2023 at 2:43 pm
  
  Yes, I do that

John August 14, 2023 at 4:53 pm

Love the progress bar and I watched the video.
Downloaded the example and it compiles just fine.

When I take the Class Module and import it into my app it compiled just fine. When I add parts of the generic test into one of my modules it does not compile. Says “Compile Error: Variable not defined.” The variable is: cls_ProgressBar. Which is the name of the class module. Kind of at a loss as to why it compiles in the example and not when added to an existing app.

Any thoughts?
-John

John August 15, 2023 at 10:45 am

I was able to fix the variable issue by doing the following: Dim clsPBAr as cls_ProgressBar
But now I run into error 91. Object variable or With Block variable not set.
The line in questions is: clsPBar.ProgressBar_Show

Thoughts?

Johan Welman December 11, 2022 at 1:39 am

Thank you for this awesome tool! I have an interesting issue though. After importing the Class module into my application, I receive and “Expected Array” error when I compile my application. The error occurs at these 3 string functions:
Function makeNumber(vType As String, vName As String) As String
vType = Left(vType, 1)
vPrefix1 = Left(vName, 2)
vPrefix2 = Right(vName, 1)
It appears to be caused by the Enum declaration in the Class Module:
Enum pTextAlign
General = 0 ‘(Default) The text aligns to the left; numbers and dates align to the right.
Left = 1 ‘The text, numbers, and dates align to the left.
Center = 2 ‘The text, numbers, and dates are centered.
Right = 3 ‘The text, numbers, and dates align to the right.
Distribute = 4 ‘The text, numbers, and dates are evenly distributed.
End Enum
When I comment out “Left” and “Right”, my string functions work.
Is there another way that I should use to extract single characters from a string?
Regards
Johan
1. Daniel Pineault Post authorDecember 11, 2022 at 6:03 am
  
  The demo file does not contain ‘makeNumber’ so I’m not sure where that is coming from.
  
  Although it compiles and works fine for me in multiple VMs, I have updated the demo file to not use Enums that could conflict with VBA functions (Left, Right, …). So try the new version and hopefully it will resolve this issue for you.
  1. Johan Welman December 11, 2022 at 7:32 am
    
    Sorry, Daniel, I should have explained better. makeNumber is in my application, it is a function that builds a customer number or supplier number. It uses the 3 string functions I posted above, to extract certain characters from the strings passed to the function by the calling procedure. This used to work find before I imported the class module for the progress bar. After I imported the class module, the words Left and Right (which I use as part of my string functions) generates the error “Expected Array”. In the meantime, I commented out the enum-declaration and all the bits in the code referring to “pTextAlign” – now my own code works again, but I lost some of the configuration capabilities of the progress bar. Here is a slightly longer extract from my own code:
    
    Function makeNumber(vType As String, vName As String) As String
    
    On Error GoTo Error_Handler
    
    Dim db As Database
    Dim rst As Recordset
    Dim strSQL As String
    Dim vPrefix1 As String
    Dim vPrefix2 As String
    Dim vSeqNo As String
    Dim vCustNo As String
    Dim vCustID As Long
    Dim vSupNo As String
    Dim vSupID As Long
    
    vType = Left(vType, 1)
    vPrefix1 = Left(vName, 2)
    vPrefix2 = Right(vName, 1)
    ***** Lots more here *****
    End Function
    1. Johan Welman December 11, 2022 at 7:35 am
      
      Also, I’ll try the new version tomorrow.
      
      Thanks for this awesome information and assistance.
Binns Patrick May 19, 2023 at 1:59 pm

Hello,
I’m sorry, for me the form is starting, but the bar does not start.
I loaded the demo
Friendly
Patrick
Access 2019
1. Daniel Pineault Post authorMay 19, 2023 at 2:04 pm
  
  Did you enable the content, place it in a Trusted Location, so the VBA code can run?
  Did you Unblock the macro for downloaded files from the internet?
  1. Binns May 19, 2023 at 2:43 pm
    
    Yes, I do that
John August 14, 2023 at 4:53 pm

Love the progress bar and I watched the video.
Downloaded the example and it compiles just fine.

When I take the Class Module and import it into my app it compiled just fine. When I add parts of the generic test into one of my modules it does not compile. Says “Compile Error: Variable not defined.” The variable is: cls_ProgressBar. Which is the name of the class module. Kind of at a loss as to why it compiles in the example and not when added to an existing app.

Any thoughts?
-John
1. John August 15, 2023 at 10:45 am
  
  I was able to fix the variable issue by doing the following: Dim clsPBAr as cls_ProgressBar
  But now I run into error 91. Object variable or With Block variable not set.
  The line in questions is: clsPBar.ProgressBar_Show
  
  Thoughts?