How to simulate a progress bar in a form without using an ActiveX control in Access 2002

This article shows you how to simulate a progress bar on a form by using the rectangle controls in the Microsoft Access toolbox. In this example, you create a form that uses either Data Access Objects (DAO) or Microsoft ActiveX Data Objects (ADO) to read data in the Customers table. As Access reads each record, the form code fills another piece of the progress bar.

NOTE: If you decide to use ADO to read the records, you must have either the Microsoft Data Engine (MSDE) or Microsoft SQL Server installed. Additionally, you must have either Northwind or NorthwindCS installed on your MSDE or SQL Server.

Create a New Access Database and Import the Customers Table

1. Start Microsoft Access.
2. Create a new database, and name it Progressbar.
3. On the File menu, point to Get External Data, and then click Import.
4. In the Import dialog box, locate Northwind.mdb, and then click Import.
5. In the Import Objects dialog box, click the Customers table, and then click OK.

Create the Form

1. Open the Progressbar database that you created earlier.
2. On the View menu, point to Database Objects, and then click Forms.
3. Click New. In the New Form dialog box, make sure that Design View is selected, and then click OK.
4. Add the following objects to the form and set the following properties:

   
      Form: frmProgressBar
      -------------------------
      Caption: Progress Bar Form
      Width: 6.333"
      General Declarations:  

   Dim lCounter As Long

      OnOpen: 

    
   ' Make sure, when the form is initially opened, that the status caption reads READY.
   Me.Status.Caption = "Ready"

   
      Detail
      ------------
      Height: 1.5"
   
      Label: Status
      ----------------
      Caption: Reading
      Left: 0.0833"
      Top: 0.4167"
      Width: 0.4583"
      Height: 0.1667"
   
      Text Box: CurrentRecordID
      -------------------------
      Left: 0.5833"
      Top: 0.4167"
      Width: 0.9167"
      Height: 0.1667"
   
      Rectangle
      ----------------------
      Name: ProgressBarA
      Left: 0.0833"
      Top: 0.7083"
      Width: 4.7917"
      Height: 0.1667"
      Special Effect: Sunken
   
      Rectangle
      -------------------------
      Name: ProgressBarB
      Left: 0.0833"
      Top: 0.7083"
      Width: 0"
      Height: 0.1458"
      Back Style: Normal
      Back Color: 10040115
      Special Effect: Flat
      Border Style: Transparent
   
      Command Button
      --------------
      Name: Read
      Caption: Read
      Left: 2.9167"
      Top: 0.3333"
      Width: 1.0"
      Height: 0.25"
      OnClick: 
    

   ADO Method

   This code sample uses ADO. Type or paste the following procedure:
   
   

   ' As soon as you click the command button, start reading records.
   ' Because you've started reading records, update the status caption to
   ' READING.
   
   Me.Status.Caption = "Reading"
   
   ' For the currently open form, display a busy/hourglass mouse icon.
   
   Screen.MousePointer = 11
   
   ' Dimension the connection and recordset objects for using ADO.
   
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   
   ' Set the connection properties. 
   ' ==================================================================
   ' BEGIN - Using ADO connection to local table.
   ' ==================================================================
   ' Use Set cn = CurrentProject.Connection to connect to a local table 
   ' using ADO. 
      
   Set cn = CurrentProject.Connection
    
   ' ==================================================================
   ' END - Using ADO connection to local table.
   ' ==================================================================
   
   ' ==================================================================
   ' BEGIN - Using new ADO connection to SQL Server.
   ' ==================================================================
   ' If you want to connect to a SQL Server using ADO, then uncomment these 
   ' lines of code and comment out the "Using ADO connection to local 
   ' table" code above. 
   
   ' With cn
   ' .Provider = "SQLOLEDB"
   
   ' Change the Data Source name to your SQL Server.
   ' The current connection string specifies integrated 
   ' security. You may have to change this.
   '.ConnectionString = "Data Source=TestSQL; Integrated Security=SSPI;Initial Catalog=Northwind"
   
   ' Open the connection.
   '.Open
   'End With
   
   ' ==================================================================
   ' END - Using new ADO connection to SQL Server.
   ' ==================================================================
   
   ' Set the recordset object to the Customers table.     
          
   Set rs = New ADODB.Recordset
   With rs
   Set .ActiveConnection = cn
   .CursorLocation = adUseClient
   .Source = "Customers"
   .Open
   
   ' While there are still records to be read, display the current customer 
   ' ID number and fill the progress bar to the current record's location, 
   ' based on the total number of records in the Customers recordset. 
   
   While Not .EOF
   
   ' Set the form's record ID number text box equal to the current record 
   ' being read.
   
   CurrentRecordID = .Fields("CustomerID")
   
   ' Set the width of the visible (or top) progress bar rectangle. 
           
   ProgressBarB.Width = (ProgressBarA.Width / .RecordCount) * .AbsolutePosition
   
   ' Repaint the current form.
   Me.Repaint
   ' Move to the next record.
   .MoveNext
   For lCounter = 1 To 750000: Next
       Wend
   End With
   
   ' If you're at the end of the Customers recordset, then fill the 
   ' progress bar completely and repaint the form.
   
   If rs.EOF Then
   ProgressBarB.Width = rs.RecordCount
   Me.Repaint
   
   ' Clear any customer ID information from the form because you're finished.
   ' Set the caption for the Status label to DONE.   
   
   CurrentRecordID = ""
   Me.Status.Caption = "Done"
      
   ' Set the progress bar's width to zero. Repaint the form.
   ProgressBarB.Width = 0
   Me.Repaint
       
   End If
       
   ' Close the recordset.
   rs.Close
   
   ' Close the connection.
   cn.Close
   
   ' Clear the recordset and database objects.
   Set rs = Nothing
   Set cn = Nothing
   
   ' Set the form's mouse pointer back to the default mouse pointer.
   Screen.MousePointer = 0

   DAO Method

   This code sample uses DAO. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, follow these steps:
   
   
   • On the View menu, point to Database Objects, and then click Modules. Click New.
   • On the Tools menu, click References. Locate the Microsoft DAO Object Library. Select the Microsoft DAO 3.6 Object Library check box, and then click OK.
   • Close the new module without saving it.
   Type or paste the following procedure:
   
   

   ' As soon as you click the command button, start reading records. Because
   ' you've started reading records, update the status caption to READING.
   Me.Status.Caption = "Reading"
   
   ' For the currently open form, display a busy/hourglass mouse icon.
   Screen.MousePointer = 11
   
   ' Dimension the database and recordset objects for using DAO.  
     
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   
   ' Set the database object to the currently opened database.
   ' Set the recordset object to the Customers table.        
   Set db = CurrentDb
   Set rs = db.OpenRecordset("Customers", dbOpenSnapshot)
   
   ' Go to the first record in the Customers recordset/table.
   rs.MoveFirst
   
   ' While there are still records to be read, display the current customer 
   ' ID number and fill the progress bar to the current record's location, 
   ' based on the total number of records in the Customers recordset/table. 
   
   While Not rs.EOF
       
   ' Set the form's record ID number text box equal to the current record 
   ' being read.
   
   CurrentRecordID = rs!CustomerID
   ' Set the width of the visible (or top) progress bar rectangle.                   
   ProgressBarB.Width = (ProgressBarA.Width / rs.RecordCount) * rs.AbsolutePosition
   
   ' Repaint the current form.
   Me.Repaint
   
   ' Go to the next record in the Customers recordset/form.
   rs.MoveNext
   For lCounter = 1 To 750000: Next
   Wend
   
   ' If you're at the end of the Customers recordset/form, then fill the 
   ' progress bar completely and repaint the form.
   
   If rs.EOF Then
   ProgressBarB.Width = rs.RecordCount
   Me.Repaint
   
   ' Clear any customer ID information from the form because you're finished.
   CurrentRecordID = ""
   
   ' Set the caption for the Status label to DONE.
   Me.Status.Caption = "Done"
   
   ' Set the progress bar's width to zero. Repaint the form.
   ProgressBarB.Width = 0
   Me.Repaint
       
   End If
       
   ' Close the recordset.
   rs.Close
   
   ' Clear the recordset and database objects.
   Set rs = Nothing
   Set db = Nothing
   
   ' Set the form's mouse pointer back to the default mouse pointer.
   Screen.MousePointer = 0

5. Remove the label for the CurrentRecordID text box.
6. Make sure that ProgressBarA appears directly over ProgressBarB in the form. To make sure that this happens, click ProgressBarA, and then click Send to Back on the Format menu. Then, click ProgressBarB, and click Bring to Front on the Format menu.
7. Compile the code, and then save the form.
8. Open the form in Form view. Note that "Ready" appears in the Status label.
9. Click the READ button and note that the Status label changes to "Reading." After all the records have been processed, the progress bar is completely filled from left to right, and "Done" appears in the Status label.