ACC2000: How to Use the Status Bar Progress Meter

This article shows you how to use the SysCmd() function in Microsoft Access to create a progress meter on the status bar that gives a visual representation of the progress of an operation that has a known duration or number of steps.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The syntax of the SysCmd() function is where

• action is a numeric expression identifying the required action. The expression is one of the following:

     Intrinsic Constant     Value   Action
     --------------------------------------------------------
     acSysCmdInitMeter        1     Initialize progress meter
     acSysCmdUpdateMeter      2     Update progress meter
     acSysCmdRemoveMeter      3     Remove progress meter
  					

• text is a string expression providing the message that appears to the left of the progress meter on the status bar.
• value is a numeric expression that controls the display of the meter. This is required when the action is acSysCmdInitMeter or acSysCmdUpdateMeter and invalid otherwise.
  
  When the action is acSysCmdInitMeter, value establishes the maximum value the process should attain, that is, when the meter indicates 100 percent.
  
  When the action is acSysCmdUpdateMeter, value is used to calculate and update the progress toward completion on the meter.

NOTE: The SysCmd() function returns NULL when it is displaying a progress meter.

How to Use SysCmd() in a Function

To use SysCmd() to display a progress meter, follow these steps:

1. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
   
   
2. Start Microsoft Access and open the sample database Northwind.mdb.
3. In the Database window, click Modules, and then click New.
4. Type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

5. Save the module as Meter Test.
6. Type or paste the following procedure:

   Function Meter()
      Dim MyDB As DAO.Database, MyTable As DAO.Recordset
      Dim Count As Long
      Dim Progress_Amount As Integer, RetVal As Variant
      
      Set MyDB = CurrentDb()
      Set MyTable = MyDB.OpenRecordset("Customers")
   
      ' Move to last record of the table to get the total number of records.
      MyTable.MoveLast
      Count = MyTable.RecordCount
   
      ' Move back to first record.
      MyTable.MoveFirst
   
      ' Initialize the progress meter.
      RetVal = SysCmd(acSysCmdInitMeter, "Reading Data...", Count)
   
      ' Enumerate through all the records.
      For Progress_Amount = 1 To Count
        ' Update the progress meter.
         RetVal = SysCmd(acSysCmdUpdateMeter, Progress_Amount)
         
        'Print the contact name and number of orders in the Immediate window
         Debug.Print MyTable![ContactName]; _ 
                     DCount("[OrderID]", "Orders", "[CustomerID]='" & MyTable![CustomerID] & "'")
   
        ' Goto the next record.
         MyTable.MoveNext
      Next Progress_Amount
   
      ' Remove the progress meter.
      RetVal = SysCmd(acSysCmdRemoveMeter)
           
   End Function
   					

7. Save and close the module.
8. In the Database window, click Forms, and then click New.
9. In the New Form dialog box, click OK to create a new form in Design view without selecting any table or query.
10. If the Properties sheet is not visible, on the View menu, click Properties.
11. On the Properties sheet, click Event, click On Open, and then select Event Procedure.
12. Click the Build button and, in the Visual Basic Editor, type the following procedure:

    Private Sub Form_Open(Cancel As Integer)
    
        Meter
    
    End Sub
    					

13. Quit the Visual Basic Editor, and then save and close the Test Meter form.
14. In the Database window, click Forms, select Test Meter, and then click Open.
    
    Notice the progress meter on the status bar.

For more information about SysCmd(), in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type syscmd method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.