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


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q304581
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

IN THIS TASK

SUMMARY

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.

back to the top

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.
back to the top

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:
    back to the top

    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
    back to the top

    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.


back to the top
Modification Type:MinorLast Reviewed:8/30/2004
Keywords:kbProgramming KbVBA kbHOWTOmaster KB304581 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.