Access Cookbook

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Aa188220.accesscook_xs(en-us,office.10).gif

Chapter 9 Excerpt: Create a Generic, Reusable Status Meter

Ken Getz, Paul Litwin, and Andy Baron
Microsoft Corporation

November 2002

Applies to:
Microsoft® Access

Buy this book

Summary: This article presents an excerpt from the book Access Cookbook by Ken Getz, Paul Litwin, and Andy Baron. Learn how to use Access to create a generic and reusable status meter. (8 printed pages)

Contents

Problem
Solution
Discussion

Problem

Access allows you to control the built-in status meter using the SysCmd function, but you have no control over the location or appearance of this status meter. How do you create a status meter that you can control?

Solution

You can create a status meter based on an Access form and control it using VBA routines. The status meter is composed of a Rectangle control and a Label control. By updating the Width property of the rectangle, you can control the meter's progress. Additionally, by updating the Caption property of the label, you can insert messages such as "50% complete". All the internal workings of the control can be encapsulated (hidden) inside the form using Let and Get property procedures and a global wrapper function.

For an example of a programmatically controlled status bar, open and run frmTestStatusMeter from 09-10.MDB (see Figure 9-32). To start the status meter, click the Start button and frmStatusMeter will pop up. If you want the status meter to include a Cancel button, check the Include Cancel button checkbox before clicking the Start button. The status meter will slowly advance to 100% and then close. If you've included a Cancel button, you can click on it at any time to immediately close the status meter and notify the calling form (frmTestStatusMeter) that the cancel has been requested.

Aa188220.odc_cookbookchapter9_01(en-us,office.10).gif

Figure 9-32. The frmStatusMeter form

Create a generic status meter

To create a generic status meter for your own application, follow these steps (or skip these steps entirely and import frmStatusMeter and basStatusMeter from 09-10.MDB into your database):

  1. Create a form and set its properties as shown in Table 9-10.

    Property Value
    DefaultView Single Form
    RecordSelectors No
    ScrollBars Neither
    NavigationButtons No
    BorderStyle Thin
    PopUp Yes
    MinMaxButtons None

    Table 9-10. Property settings for the status bar form

  2. Place a rectangle on the form, name it recStatus, and set its Width property to 0. Set its background color to the color of your choice.

  3. Place a label on the form, name it lblStatus, and set its Width property to the total width you want the status bar to be. Set its Background to Clear. In the Label property, type in "0% Completed".

  4. Add a command button control named cmdCancel with a caption of "Cancel". Create an event procedure attached to the button's Click event. (If you're unsure of how to do this, see the "How Do I Create an Event Procedure?" section in the Preface of this book.) Add the following code to the event procedure:

    Private Sub cmdCancel_Click(  )
        mfCancel = True
    End Sub
    
  5. Add the following global declaration to the global declarations section of the form's module:

    Dim mfCancel As Boolean
    
  6. Add the following three property procedures to the form's module:

    Public Property Let InitMeter(fIncludeCancel As Boolean, strTitle As String)
    
       Me!recStatus.Width = 0
       Me!lblStatus.Caption = "0% complete"
       Me.Caption = strTitle
       Me!cmdCancel.Visible = fIncludeCancel
    
       DoCmd.RepaintObject
    
       mfCancel = False
    
    End Property
    
    Public Property Let UpdateMeter(intValue As Integer)
    
       Me!recStatus.Width = CInt(Me!lblStatus.Width * (intValue / 100))
       Me!lblStatus.Caption = Format$(intValue, "##") & "% complete"
    
       DoCmd.RepaintObject
    
    End Property
    
    Public Property Get Cancelled(  ) As Boolean
       Cancelled = mfCancel
    End Property
    
  7. Save the form as frmStatusMeter and close it.

  8. Create a new global module and add the following code (or import the module basStatusMeter from 09-10.MDB):

    Private Const mconMeterForm = "frmStatusMeter"
    
    Private Function IsOpen(strForm As String)
        IsOpen = (SysCmd(acSysCmdGetObjectState, acForm, strForm) > 0)
    End Function
    
    Public Sub acbCloseMeter(  )
    
        On Error GoTo HandleErr
    
        DoCmd.Close acForm, mconMeterForm
    
    ExitHere:
        Exit Sub
    HandleErr:
        Case Else
            MsgBox "Error#" & Err.Number & ": " & Err.Description, _
             , "acbCloseMeter"
        End Select
        Resume ExitHere
    End Sub
    
    Public Sub acbInitMeter(strTitle As String, fIncludeCancel As
      Boolean)
    
        ' Initializes the status meter to 0.
        '
        ' In:
        '     strTitle - Title of status meter form
    
        On Error GoTo HandleErr
    
        DoCmd.OpenForm mconMeterForm
        Forms(mconMeterForm).InitMeter(fIncludeCancel) = strTitle
    
    ExitHere:
        Exit Sub
    HandleErr:
        Select Case Err.Number
        Case Else
            MsgBox "Error#" & Err.Number & ": " & Err.Description, _
             , "acbInitMeter"
        End Select
        If IsOpen(mconMeterForm) Then Call acbCloseMeter
        Resume ExitHere
        Resume
    End Sub
    
    Public Function acbUpdateMeter(intValue As Integer) As Boolean
    
        ' Updates the status meter and returns whether
        ' the Cancel button was pressed.
        '
        ' In:
        '     intValue - percentage value 0-100
    
        On Error GoTo HandleErr
    
        Forms(mconMeterForm).UpdateMeter = intValue
    
        ' Return value is False if cancelled.
        If Forms(mconMeterForm).Cancelled Then
            Call acbCloseMeter
            acbUpdateMeter = False
        Else
            acbUpdateMeter = True
        End If
    
    ExitHere:
        Exit Function
    HandleErr:
        Select Case Err.Number
        Case Else
            MsgBox "Error#" & Err.Number & ": " & Err.Description, _
             , "acbUpdateMeter"
        End Select
        If IsOpen(mconMeterForm) Then Call acbCloseMeter
        Resume ExitHere
    End Function
    
  9. Save and close the global module.

Use the generic status meter in your application

To use the generic status meter in your own applications, follow these steps:

  1. When you wish to initialize the meter, use the following syntax:

    Call acbInitMeter(title, flag)
    

    where title is the title you want the status meter to assume, and flag is True (or -1) to display a Cancel button or False (or 0) to not display one. For example, this statement creates a status meter with the title Progress and a Cancel button:

    Call acbInitMeter("Progress", True)
    
  2. To update the meter with a new progress value, use the following syntax:

    variable = acbUpdateMeter(value)
    

    where value is an integer between 0 and 100. acbUpdateMeter will place True or False in the return value. If the return value is False, the user has pressed the Cancel button. (The return value will never be False if you choose not to include the Cancel button when initializing the status meter.) For example, to update the meter with a progress setting of 50%, you might call acbUpdateMeter like this:

    fOK = acbUpdateMeter(50)
    
  3. To close the status meter form, use this syntax:

    Call acbCloseMeter
    

Discussion

You can change the size of the rectangle by manipulating its Width property. The Rectangle control is placed behind a transparent Label control that defines the boundaries of the status meter and contains the status text. The status meter form is manipulated by three public wrapper functions contained in basStatusMeter: acbInitMeter, acbUpdateMeter, and acbCloseMeter. These functions, in turn, interact with frmStatusMeter through its exposed properties. The wrapper functions know the names of the properties and how to call them, but they know nothing of the inner workings of the form.

acbInitMeter initializes the status meter by opening the status meter form and setting the InitMeter property to the appropriate string. At the same time, a parameter is passed that determines if the Cancel button is included on the status meter form:

DoCmd.OpenForm acbcMeterForm
Forms(acbcMeterForm).InitMeter(fIncludeCancel) = strTitle

acbUpdateMeter sets the value of the status meter form's UpdateMeter property. It then checks the Cancelled property of the form to determine whether the user has clicked on the Cancel button. If so, it closes the status meter form and returns False to the calling procedure; otherwise it returns True:

Forms(acbcMeterForm).UpdateMeter = intValue

' Return value is False if cancelled.
If Forms(acbcMeterForm).Cancelled Then
    Call acbCloseMeter
    acbUpdateMeter = False
Else
    acbUpdateMeter = True
End If

acbCloseMeter closes the status meter form using the DoCmd.Close method:

DoCmd.Close acForm, acbcMeterForm

Internally, the Let and Get property procedures do all the work. When the InitMeter property is set by some external procedure, the InitMeter Let property procedure runs the following code:

Me!recStatus.Width = 0
Me!lblStatus.Caption = "0% complete"
Me.Caption = strTitle
Me!cmdCancel.Visible = fIncludeCancel

DoCmd.RepaintObject

mfCancel = False

This code sets the Width property of the recStatus control to 0 and the Caption property of lblStatus to "0% complete", updates the form's Caption property with the strTitle parameter, and sets the cmdCancel button's Visible property to match the fIncludeCancel parameter. The code then uses the RepaintObject method to force an update of the screen and resets the mfCancel module-level global variable to False.

When the UpdateMeter property of the form is set to a value, the following code is executed by the UpdateMeter Let property procedure:

Me!recStatus.Width = CInt(Me!lblStatus.Width * (intValue / 100))
Me!lblStatus.Caption = Format$(intValue, "##") & "% complete"
DoCmd.RepaintObject

This code updates the status meter by changing the width of the recStatus control relative to the width of the lblStatus control. This relative change ensures that the status meter rectangle never exceeds the limits as defined by the width of the lblStatus control. The routine then updates the Caption property of the lblStatus control to a formatted percentage value concatenated to the string "% complete". Once again, the code uses the RepaintObject method to force an update of the screen.

The Cancelled property of the status meter form is handled by the Cancelled Get property procedure. When called by an external procedure, this procedure returns the value of the module-level global mfCancel variable. This variable, which was initialized to 0 by the IntitMeter Let property procedure, is set to False if the user clicks on the cmdCancel button in the cmdCancel_Click event procedure.

It's a good idea to encapsulate the inner workings of a generic utility form such as frmStatusMeter by keeping all the event procedures private and using Let and Get property procedures to expose a controlled user interface to calling procedures. Getting in the habit of thinking and coding in this object-oriented way will allow you to create generic components that you can reuse over and over again.

The pop-up status meter form's AutoCenter property has been set to Yes, so it will always appear in the center of the screen. You may wish to extend acbInitMeter with optional left and top parameters so you can precisely position the form on the screen when it is first opened.

As an alternative to the form presented in this solution, you may wish to employ one of the ActiveX controls that offers similar functionality. The Developer version of Office and Visual Basic both include the Progress Bar control, and Visual Basic also includes the Gauge control. Both controls can be used to indicate the progress of long operations.