<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Progress Bars: Keeping users informed during extensive processing periods

Published on
25,888 Points
6,288 Views
11 Endorsements
Last Modified:
Awarded
Community Pick
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

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”

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

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”)

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))

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]

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]

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

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

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

Open in new window

or

Docmd.close acform, "frm_Status"

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
11
Comment
Author:Dale Fye
3 Comments

Expert Comment

by:Evert Jor
I'm getting an error clikcing "Generate" on your database-form:

3346
Number of query values and destination fields are not the same.
0
 

Administrative Comment

by:mbizup
EvertJor,

That is not an issue in the sample database as posted -- which works fine as is.

If you are having trouble with your own implementation of this, please post it as a question in the MS Access Topic area, preferably including your database as an attachment to illustrate your issue.

mbizup
EE Page Editor/Access Topic Advisor
0

Expert Comment

by:Adam Borkowski
Got the same problem,

Change the line in sub: cmd_Generate_Click()

            strSQL = "INSERT INTO tblRandomNumbers (ID, OuterLoop, InnerLoop, RandomNumber) " _
                   & "Values (" & (intOLoop - 1) * Val(Me.txt_InnerLoop) + intILoop & ", " _
                                & intOLoop & ", " & intILoop & ", " & Rnd() & ")"

into
            strSQL = "INSERT INTO tblRandomNumbers (ID, OuterLoop, InnerLoop, RandomNumber) " _
                   & "Values (" & (intOLoop - 1) * Val(Me.txt_InnerLoop) + intILoop & ", " _
                                & intOLoop & ", " & intILoop & ", " & Replace(Rnd(), ",", ".", 1, -1, vbBinaryCompare) & ")"

For testing purposed it will be enough
0

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month