Microsoft KB Archive/304581

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

Article ID: 304581

Article Last Modified on 8/30/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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
 * Create a New Access Database and Import the Customers Table
 * Create the Form
 * ADO Method
 * DAO Method



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
 Open the Progressbar database that you created earlier. On the View menu, point to Database Objects, and then click Forms. Click New. In the New Form dialog box, make sure that Design View is selected, and then click OK.  Add the following objects to the form and set the following properties:   Form: frmProgressBar -  Caption: Progress Bar Form Width: 6.333&quot; General Declarations: Dim lCounter As Long   OnOpen: ' Make sure, when the form is initially opened, that the status caption reads READY. Me.Status.Caption = &quot;Ready&quot;   Detail Height: 1.5&quot;

Label: Status Caption: Reading Left: 0.0833&quot; Top: 0.4167&quot; Width: 0.4583&quot; Height: 0.1667&quot;

Text Box: CurrentRecordID -  Left: 0.5833&quot; Top: 0.4167&quot; Width: 0.9167&quot; Height: 0.1667&quot;

Rectangle --  Name: ProgressBarA Left: 0.0833&quot; Top: 0.7083&quot; Width: 4.7917&quot; Height: 0.1667&quot; Special Effect: Sunken

Rectangle -  Name: ProgressBarB Left: 0.0833&quot; Top: 0.7083&quot; Width: 0&quot; Height: 0.1458&quot; Back Style: Normal Back Color: 10040115 Special Effect: Flat Border Style: Transparent

Command Button --  Name: Read Caption: Read Left: 2.9167&quot; Top: 0.3333&quot; Width: 1.0&quot; Height: 0.25&quot; 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 = &quot;Reading&quot;

' 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 &quot;Using ADO connection to local ' table&quot; code above.

' With cn ' .Provider = &quot;SQLOLEDB&quot;

' Change the Data Source name to your SQL Server. ' The current connection string specifies integrated ' security. You may have to change this. '.ConnectionString = &quot;Data Source=TestSQL; Integrated Security=SSPI;Initial Catalog=Northwind&quot;

' 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 = &quot;Customers&quot; .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(&quot;CustomerID&quot;)

' 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 = &quot;&quot; Me.Status.Caption = &quot;Done&quot; ' 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.</li> 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.</li> Close the new module without saving it.</li></ul>

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 = &quot;Reading&quot;

' 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(&quot;Customers&quot;, 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 = &quot;&quot;

' Set the caption for the Status label to DONE. Me.Status.Caption = &quot;Done&quot;

' 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 </li> Remove the label for the CurrentRecordID text box.</li> 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.</li> Compile the code, and then save the form.</li> Open the form in Form view. Note that &quot;Ready&quot; appears in the Status label.</li> Click the READ button and note that the Status label changes to &quot;Reading.&quot; After all the records have been processed, the progress bar is completely filled from left to right, and &quot;Done&quot; appears in the Status label.</li></ol>

back to the top

Additional query words: inf openrecordset dao ado progress bar read connection status meter

Keywords: kbhowtomaster kbprogramming kbvba KB304581

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.