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

Article translations Article translations
Article ID: 304581 - View products that this article applies to.
This article was previously published under Q304581
Expand all | Collapse all

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.

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.


Properties

Article ID: 304581 - Last Review: August 30, 2004 - Revision: 4.1
APPLIES TO
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbhowtomaster kbprogramming kbvba KB304581

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com