Progress Bars: Keeping users informed during extensive processing periods

Have you ever written a loop or a multi-step procedure during which you would have liked to keep your user appraised of your process?  Are you dissatisified with just changing the cursor to an hourglass to inform your users that a time consuming process is executing?  If so, then this article will provide you with a couple of alternatives.

Access provides us with several built-in methods for informing our users about ongoing processes.
1.  You can change the cursor from a pointer to an hourglass.  Although this method provides your users with a visual indication that something is happening, it does not tell them much.  Even so, this is a useful way to give an indication that something is happening.  There are two ways to accomplish this:

    a.  Docmd.hourglass  - This method allows you to change the cursors image from its default to an hourglass, or change it back.
    b.  Screen.Mousepointer – Mousepointer is a property of the Screen object.  You can determine the type of pointer currently in use, or change the type of pointer to one of 6 different options.  The parameter values for the default cursor and hourglass are 0 and 11.  I find it helpful when I have turned on the hourglass, and then open a dialog box, to check for the value of the mousepointer, store that value, then set the pointer to the default.  Then, when I close the dialog form, I set the mousepointer property back to what it was when I opened the form.  Whenever you use either of these methods, you need to ensure that you change the pointer back to its default value when you leave a procedure.  Although failure to properly do this will not hinder your ability to continue working in your application, it is extremely annoying to your users.  I usually do this by implementing error handling and normal exit processing via code that looks similar to:

Private Sub DoSomething
                          On Error Goto ProcError
                      
                          Docmd.hourglass true
                          ‘do some processing here
                      
                      ProcExit:
                          Docmd.hourglass false
                          'or
                          Screen.MousePointer = 0
                          Exit Sub
                      ProcError:
                          ‘error handling code
                          Resume ProcExit
                      End Sub

Select all Open in new window

2.  The second technique for keeping  your users informed is the Echo method.  Most of you probably know that the Echo method allows you to turn screen updating on and off, but did you know that there is a second parameter (StatusBarText) that is also available for use with this method?  This method will place a text message in the lower left hand corner of the Access Application window.  It doesn’t flash, move, or anything else which means that it is unlikely to catch your users attention, so I rarely use this option.  However, if you choose to do so, you can activate the Echo method with the following syntax:

Application.Echo False, “Display status bar text”

Select all Open in new window

As with the hourglass method, you need to ensure that you provide a reliable way to turn screen updating back on.  If you fail to do this, the screen will not repaint, and you will not be able to see what is happening in your application.  To avoid this, I use code similar to that shown in the previous code segment and include a line to reenable the screen painting in the ProcExit event.  Additionally, I set the forms KeyPreview property to Yes and add the following code segment to the forms KeyDown event:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
                          If KeyCode = vbKeyF12 Then
                              DoCmd.Echo True
                              KeyCode = 0
                          End If
                      End Sub

Select all Open in new window

3.  A third technique for providing your users with meaningful feedback is to use the SysCmd method.   You can either display a text message in the lower left hand corner of the Access application window:

Call SysCmd(acSysCmdSetStatus, "This is a test message”)

Select all Open in new window

or a status bar in the lower right corner with optional text displayed in the center of the status bar.

Call SysCmd(acSysCmdInitMeter, "some text ", 100)
                      Call SysCmd(acSysCmdUpdateMeter, Int(Val(Me.Tag) / 40 * 100))

Select all Open in new window

This technique avoids the screen repaint problems associated with the Echo method but has the same visual drawback; nothing draws your users eyes to the status bar.  For this reason, I find both the Echo and SysCmd techniques to be of little value.

4.  Finally, because none of the previous methods are very visually stimulating, I created a form which I use whenever I am executing a multi-step process or a loop during which I want to provide feedback to my users.  This is not a new idea, Microsoft and other software companies have been using a similar technique for years for their installation processes, I just modified it for use in my Access applications.  The attached mdb file contains frm_Status, which contains all the code necessary to implement it within your applications.  It also contains another form frm_Example which provides an example of how to use the status form.

Frm_Status provides you with the ability to provide process status for a single process, or for imbedded processes.  In frm_Example, I have given you the ability to see how the code works with both processes.  I also provide an example of how to set the form up to display a Cancel option, and how to implement that within your application.  

Explaination:
To open frm_Status from your code, all you need to do is call the the forms function StatusUpdate function.  The syntax for this function call is:

Form_frm_Status.StatusUpdate [Message], [PctComplete]

Select all Open in new window

Message: the text message you would like to display
PctComplete: a number between 0 and 1 which provides the necessary information for determining the width of the progress bar.  The progress bar will not display if the value of PctComplete is < .005.   When the PctComplete value exceeds .999 the message and progress bar will disappear, leaving a “Done” message and a Continue command button.  If the Continue button is not pressed within five seconds of being displayed, it will be automatically be activated and the form will close.

To activate the second message and progress bar, call the Status2Update function, with similar parameters:

Form_frm_Status.Status2Update [message], [PctComplete]

Select all Open in new window

This second status bar will not display if [PctComplete] is < .005 or > 1.005.  You can tweak these values as you see fit.  

To make the cancel button visible, call the EnableCancel function:

Form_frm_Status.EnableCancel  true/false

Select all Open in new window

This will make the cancel button visible on your form.  If the user clicks the cancel button, it sets the Tag value of the form to -1.  This will not do you any good unless you actually test for this value in your code.  The code segment below (from cmd_Generate_Click) shows  how I test for this value in my loop.

If Form_frm_Status.Tag = True Then
                          Form_frm_Status.StatusUpdate "", 1
                          If Me.chk_RollbackOnCancel = True Then
                              wrk.Rollback
                          Else
                              wrk.CommitTrans
                          End If
                          GoTo ProcExit
                      End If

Select all Open in new window

This form is modal and popup, so when you activate it, it gets the focus and you will not be able to leave it until your code passes the StatusUpdate function a PctComplete value greater than .999.  You should probably include one of the following as the last line in any procedure that calls this form.

Form_frm_Status.StatusUpdate "", 1

Select all Open in new window

or

Docmd.close acform, "frm_Status"

Select all Open in new window

I have also included a bypass function to allow you to close the form manually, in the event that you forget to include one of these steps or that your code somehow bypasses them.  If you need to manually close the form, click on the form and press the F12 button.  This will immediately close the form, but if your other code continues to call it, it will pop back up.

StatusBar.mdb
StatusBar.jpg