Access Cookbook By Ken Getz, Paul Litwin, Andy Baron February 2002 0-596-00084-7, Order Number: 0847 718 pages, $49.95 US $74.95 CA

Sample Excerpts

Excerpted here:

• Chapter 1 Excerpt: Use a Query to Show the Relationship Between Employees and Supervisors
• Chapter 8 Excerpt: Make Slow Forms Run Faster
• Chapter 9 Excerpt: Create a Generic, Reusable Status Meter

Chapter 1 Excerpt: Use a Query to Show the Relationship Between Employees and Supervisors

Problem

You have a table that includes information on every employee in the company, including management. You'd like to be able to store information on who supervises each employee in this same table and then be able to create a query to show this hierarchical relationship.

Solution

You can display an employee-supervisor hierarchical relationship, also known as a recursive relationship, in Access with a select query that uses a self-join to join another copy of a table to itself. This solution shows how to create the table that will store the necessary recursive information and then how to create the self-join query to list each employee and his or her supervisor.

To create the employee table and a query that displays the recursive employee-supervisor relationship, follow these steps:

1. Create the employee table. This table should contain both an EmployeeID field and a SupervisorID field. These fields must have the same field size. In the sample database, tblEmployees contains the EmployeeID and SupervisorID fields. Because EmployeeID is an AutoNumber field with the FieldSize property set to Long Integer, SupervisorID must be a Number field with a FieldSize of Long Integer.
2. Enter data into the employee table, making sure that the SupervisorID field is equal to the EmployeeID field of that employee's immediate supervisor.
3. Create a new select query. Add two copies of the employee table. The second copy of the table will automatically have a "_1" appended to the end of the table name to differentiate it from the first one. Now join the two tables together by dragging the SupervisorID field from the first copy of the table (the one without the _1 suffix) to the EmployeeID field in the second copy of the table (the one with the _1 suffix).
4. Drag any fields you wish to include in the query to the query grid. The fields from the first copy of the table describe the employee; the fields from the second copy of the table describe the supervisor. Because the fields of the two tables have the same names--remember they're really two copies of the same table--you need to alias (rename) any fields from the second table to avoid confusion. For example, in the qryEmployeeSupervisors1 query, we included the following calculated field, named Supervisor, which displays the name of the employee's immediate supervisor:

Supervisor: [tblEmployees_1].[FirstName] & " " & 
[tblEmployees_1].[LastName]

5. Notice that the fields that make up the supervisor name both come from the second copy of the employee table.

6. If you run the query at this point, you will get only employees with supervisors (see Figure 1-34). That's because this version of the query--named qryEmployeeSupervisors in the sample database--uses an inner join. To see all employees, even those without a supervisor (in our example this would include Shannon Dodd, the company's president), you must change the type of join between the two tables to a left outer join. Double-click on the join line you created in Step 3. At the Join Properties dialog, select choice #2 (see Figure 1-35).

Figure 1-34. This self-join query uses an inner join

Figure 1-35. The Join Properties dialog allows you to create left or right outer joins

7. Run the query, and the datasheet will display the employee-supervisor relationship (see Figure 1-37).

Now, open tblEmployees in 01-12.MDB. This table, which is shown in Figure 1-36, contains a primary key, EmployeeID, and the usual name and address fields. In addition, it contains a field, SupervisorID, which stores the EmployeeID of the employee's immediate supervisor. Now run the query qryEmployeeSupervisors1. This query uses a self-join to display a recursive relationship between employee and supervisor; its datasheet lists each employee and his or her immediate supervisor (see Figure 1-37).

Figure 1-36. The SupervisorID field stores information on each employee's supervisor

Figure 1-37. Output of qryEmployeeSupervisors1

Discussion

You can always model an employee-supervisor relationship as two tables in the database. Put all supervised employees in one table and supervisors in a second table. Then create a regular select query to list out all employees and their supervisors. This design, however, forces you to duplicate the structure of the employee table. It also means that you must pull data from two tables to create a list of all employees in the company. Finally, this design makes it difficult to model a situation in which employee A supervises employee B, who supervises employee C.

A better solution is to store both the descriptive employee information and the information that defines the employee-supervisor hierarchy in one table. You can view the employee-supervisor relationship using a self-join query. You can create a self-join query by adding a table to the query twice and joining a field in the first copy of the table to a different field in the second copy of the table. The key to a self-join query lies in first having a table that is designed to store the information for the recursive relationship.

The sample query qryEmployeeSupervisors1 displays the employee-supervisor relationship to one level. That is, it shows each employee and his or her immediate supervisor. But you aren't limited to displaying one level of the hierarchy--the sample query qryEmployeeSupervisors3 displays three levels of the employee-supervisor relationship using four copies of tblEmployees and three left outer joins. The design of qryEmployeeSupervisors3 is shown in Figure 1-38; the output is shown in Figure 1-39.

Figure 1-38. qryEmployeeSupervisors3 shows three levels of the employee-supervisor relationship

Figure 1-39. Output of qryEmployeeSupervisors3

You can use the Access Relationships dialog to enforce referential integrity for recursive relationships. Select Tools Relationships to display the Relationships dialog and add two copies of the table with the recursive relationship. Join the two copies of the table together as if you were creating a self-join query. Choose to establish referential integrity, optionally checking the cascading updates and deletes checkboxes. Click on Create to create the new relationship. Now when you enter a value for SupervisorID, Access will prevent you from entering any reference to an EmployeeID that doesn't already exist.

Although the sample database uses an employee-supervisor relationship example, you can use the techniques discussed in this solution to model other types of hierarchical relationships. This will work, however, only if each "child" record has only one "parent." In this example, each employee has only one supervisor. For hierarchies in which one child can have many parentssuch as parts and assemblies in a bill of materials databasea separate table is needed to contain the multiple records needed for each child, each one specifying a different parent.

Chapter 8 Excerpt: Make Slow Forms Run Faster

Problem

You are not happy with the speed at which your forms load and display. How can you change your forms so they will load and display faster?

Solution

Access gives you a lot of flexibility to develop dynamite-looking forms. Unfortunately, Access also makes it easy to create forms that run painfully slowly. Solution 8.1 explained how you can speed up the loading time of all forms by preloading them. This solution discusses how to track down and fix various performance bottlenecks, thus improving form execution performance. We also discuss the use and misuse of graphic elements and combo and list box controls.

You should consider several potential issues when analyzing your forms for performance. We discuss here two common performance bottlenecks: controls involving graphic or memo field data, and combo and list box controls.

Graphic and memo controls

Load the 08-02a.MDB database. Open the frmCategoriesOriginal form (see Figure 8-5). This form, although attractive, loads slowly and has a noticeable delay on slower machines when moving from record to record. Now open frmCategoriesStep3, which is the final version of the form after various optimizations have been applied to it (see Figure 8-6). Its load and execution times should be noticeably faster.

Figure 8-5. The original form, frmCategoriesOriginal, is slow

Figure 8-6. The final form, frmCategoriesStep3, is faster

Follow these steps to improve the performance of forms that include unbound graphic controls or bound controls that hold OLE or memo fields:

1. Open the problem form in design view. If you have any unbound object frame controls (also know as unbound OLE controls) that are used to store fixed graphic images, change them to image controls by right-clicking on the object and selecting Change To Image (see Figure 8-7). The frmCategoriesStep1 form in the 08-02a.MDB sample database is identical to frmCategoriesOriginal except that ctlLogo has been converted from an unbound object frame control to an image control.

Figure 8-7. Changing an unbound object frame control to an image control

2. If you created a watermark for the form, consider removing it. To do this, select the word "bitmap" in the form's Picture property, press the Del key, and answer Yes to the confirming dialog. The frmCategoriesStep2 form in 08-02a.MDB is identical to frmCategoriesStep1, except that we deleted the watermark.
3. If your form contains any bound controls that hold either OLE or memo fields, consider moving the controls to a second page of the form. In the final version of the Categories form, named frmCategoriesStep3 (Figure 8-6), we moved the ctlDescription and ctlPicture controls to a second page.

Combo and list box controls

Load the 08-02b.MDB database. Open the frmSurveySlow form. This form contains a combo box control, cboPersonId, that has as its row source a SQL Select statement that pulls in 15,000 rows from the tblPeople table. Load time for the form is slow because Access has to run the query that supplies the 15,000 rows to cboPersonId. Tab to the cboPersonId control and type "th" to search for the name "Thompson, Adrian" (see Figure 8-8). Note the long delay before the "th" list of records appears. Now open the frmSurveyFast form (see Figure 8-9); its load time is significantly faster. Press the ">" command button to open the frmPersonPopup form. Type "th" in the first field and press Tab. After a short delay, you'll be able to select "Thompson, Adrian" from the drop-down list as shown in Figure 8-10. Press the OK button, which will drop the chosen name back into the txtPersonName text box on frmSurveyFast.

Figure 8-8. The cboPersonId combo box in frmSurveySlow is very slow

Figure 8-9. In frmSurveyFast, the combo box is replaced with a text box and command button

Figure 8-10. Selecting a name from the drop-down list is much faster

Follow these steps to improve the speed of forms containing combo or list boxes that must display a lot of information:

1. Make a copy of the problem form and open the copy in design view. Select the slow combo or list box control. Right-click on the control and select Change To Text Box.
2. Create a new unbound pop-up form with the property settings shown in Table 8-2. Leave the remaining property settings at their defaults. In the sample database, this form is named frmPersonPopup.

Table 8-2: Property settings for the pop-up form

Property	Setting
ScrollBars	Neither
RecordSelectors	No
NavigationButtons	No
AutoResize	Yes
AutoCenter	Yes
PopUp	Yes
Modal	Yes
MinMaxButtons	None

3. Create four unbound controls on this form: a text box, a combo box, and two command buttons. In the sample database, we created the controls shown in Table 8-3. The text box will be used to limit the number of items in the combo box, using the parameter query created in Step 4.

Table 8-3: The controls on frmPersonPopup

Control type	Control name	Notes
Text box	txtChar	Limits the values in the row source of the combo box
Combo box	cboPersonId	Uses the parameter query created in Step 4 as its row source
Command button	cmdOK	Hides form
Command button	cmdCancel	Closes form

4. Create a new query that will serve as the row source for the combo box of the pop-up form. If you used a query as the source for the combo or list box on the original form, you should be able to modify its design. Add the necessary fields to the query. Add a parameter to the form that limits the rows based on a value typed into the text box on the pop-up form. Choose any sort fields. In the sample database, we created the qryPersonComboBox query with the fields shown in Table 8-4. Save and close the query.

Table 8-4: The fields in qryPersonComboBox

Query field	Sort	Criteria
PersonId	(None)	(None)
FullName: [LastName] & ", " & [FirstName]	(None)	(None)
LastName	Ascending	Like [Forms]![frmPersonPopup2]![txtChar] & "*"
FirstName	Ascending	(None)

5. Reopen the pop-up form created in Steps 2 and 3. Set the Enabled property of the combo box to No. Set the RowSource property to point to the query created in Step 4. In the sample database, we set the properties of the cboPersonId combo box to the values in Table 8-5.

Table 8-5: Property settings for cboPersonId

Property	Setting
Enabled	No
RowSourceType	Table/Query
RowSource	(Blank)
ColumnCount	2
ColumnHeads	No
ColumnWidths	0";2.5"
BoundColumn	1
ListRows	8
ListWidth	2.5"

6. Create a new event procedure for the text box's Change event. (If you're unsure of how to do this, see "How Do I Create an Event Procedure?" in the Preface of this book.) Add the following code to the event procedure:

Private Sub txtChar_Change(  )
 
    If Not IsNull(Me!txtChar.Text) Then
        Me!cboPersonID.Enabled = True
    Else
        Me!cboPersonID.Enabled = False
    End If
 
End Sub

7. Change txtChar to the name of your text box and cboPersonId to the name of your combo box.

8. Create a new event procedure for the text box's AfterUpdate event and add the following code to it:

Private Sub txtChar_AfterUpdate(  )
 
    Dim ctlPersonId As ComboBox
    Dim ctlChar As TextBox
    
    Set ctlPersonId = Me!cboPersonID
    Set ctlChar = Me!txtChar
    
    If Not IsNull(ctlChar) Then
        ctlPersonId.RowSource = "qryPersonComboBox"
        ctlPersonId.SetFocus
        ctlPersonId.Dropdown
    End If
 
End Sub

9. Change txtChar to the name of your text box, and cboPersonId to the name of your combo box. Change qryPersonComboBox to the name of the query you created in Step 4.

10. Create the following new event procedure for the OK command button's Click event:

Private Sub cmdOK_Click(  )
    Me.Visible = False
End Sub

11. Create the following new event procedure for the Cancel command button's Click event:

Private Sub cmdCancel_Click(  )
    DoCmd.Close acForm, Me.Name
End Sub

12. Save the pop-up form and close it.
13. Reopen the form from Step 1 in design view. Add a button called cmdPopup to the right of the text box. Add the following event procedure to cmdPopup's Click event:

Private Sub cmdPopup_Click(  )
 
    Const acbcPopup = "frmPersonPopup"
 
    ' Open up pop-up form in dialog mode.
    DoCmd.OpenForm acbcPopup, WindowMode:=acDialog
    
    ' Check if form is still loaded.
    ' If yes, then OK button was used to close pop-up.
    If SysCmd(acSysCmdGetObjectState, acForm, acbcPopup) <> 0 Then
        Me!PersonID = Forms(acbcPopup)!cboPersonID
        DoCmd.Close acForm, acbcPopup
    End If
        
End Sub

14. Change frmPersonPopup to match the name of the pop-up form. Change PersonId and cboPersonId to the names of the appropriate controls.

Discussion

When you have a form that loads and executes slowly, you need to analyze the form and weigh the advantages and disadvantages of using graphic features. After a careful analysis of the frmCategoriesOriginal form in the 08-02a.MDB database, we made several changes.

First, we changed the unbound object frame control to an image control. The OLE-based object frame control can be used to hold graphic images, sound, and other OLE-based data such as Excel spreadsheets or Word documents. But if you need to display only an unbound bitmap, you're better off using the more resource-conservative image control.

Second, we removed the form watermark, as this feature slows down form execution slightly. The improvement in performance depends on the color-depth of the removed image and the speed of your machine.

Finally, we created a second page and moved the text box bound to the memo field and the bound object frame bound to the OLE field to this second page. These field types (memo and OLE) are stored separately from the rest of the fields in a record and thus require additional disk reads to display. Fortunately, Access does not fetch these potentially large fields from the database unless they are visible on the screen. By placing them on the second page, you can quickly navigate from row to row without having to fetch the memo or OLE data. When you need to view the data in the fields, you can easily flip to the second page of the form.

The frmSurveySlow form in 08-02b.MDB contains a combo box, cboPersonId, bound to a 15,000-row table. This makes form load and combo box list navigation slow. Combo and list box controls are excellent for allowing users to choose from a list of values and work well with a small number of list rows. However, they perform poorly when the size of the list exceeds a few thousand rows, even with very fast hardware.

We were able to improve the load time of the survey form significantly by limiting the rows in the person combo box. This was done using a pop-up form containing the same combo box control, but linked to a text box control that filtered the combo box's rows via a parameter query. Using a little VBA code, we disabled the combo box control until at least one character was entered into the text box. In this way, we reduced a 15,000-row combo box to, on average, 577 rows (15000 / 26), and that's when only the minimum number of characters (one) is typed into the text box. You could increase performance by waiting for at least two or even three characters, rather than filling the list after the user has typed only one letter.

Besides reducing the number of rows in the row source for cboPersonId, two other improvements were made to boost combo box performance. On the original frmSurveySlow form, a SQL statement was used as the row source for the combo box; the cboPersonId combo box on the pop-up form uses a saved query instead. Saved queries are always faster than SQL statements because the query optimizer optimizes the query when it is saved instead of when it is run.

In addition, the SQL statement for frmSurveySlow's combo box includes the following ORDER BY clause:

ORDER BY [LastName] & ", " & [FirstName]

In contrast, the SQL statement for the qryPersonComboBox query used as the row source for frmPersonPopup uses the following ORDER BY clause:

ORDER BY tblPeople.LastName, tblPeople.FirstName

Although these two ORDER BY clauses look similar, the first one sorts on an expression, whereas the second sorts on two indexed fields. It's always faster to sort on individual fields rather than expressions.

There are several other things to consider when looking for ways to speed up your forms. You may wish to try some or all of the following suggestions:

• Preload and keep loaded forms (see Solution 8.1).
• Ensure that fields used to sort or filter rows are indexed in the underlying tables (see Solution 8.4 for more on indexing and Rushmore).
• Use referential integrity throughout your database. Besides the obvious improvements to the quality of your data when you create enforced relationships, Access creates hidden foreign key indexes that improve the performance of queries, forms, and reports based on the joined tables.
• Create simpler forms with less color, fewer graphics, and fewer fonts.
• Limit the number of records in the form's recordset (see Solution 8.6).
• Watch out for Tab controls with many pages and subforms on each page. Loading all those subforms will slow the opening of your form. One alternative is to load the subforms on a Tab control page only when that page is selected. You can do this by using the Change event of the Tab control to check the Value of the controlthis tells you the PageIndex of the selected page. You can set the SourceControl property of your subforms only when the page they appear on is selected; you can't set it in design view.

Chapter 9 Excerpt: Create a Generic, Reusable Status Meter

Problem

Access allows you to control the built-in status meter using the SysCmd function, but you have no control over the location or appearance of this status meter. How do you create a status meter that you can control?

Solution

You can create a status meter based on an Access form and control it using VBA routines. The status meter is composed of a Rectangle control and a Label control. By updating the Width property of the rectangle, you can control the meter's progress. Additionally, by updating the Caption property of the label, you can insert messages such as "50% complete". All the internal workings of the control can be encapsulated (hidden) inside the form using Let and Get property procedures and a global wrapper function.

For an example of a programmatically controlled status bar, open and run frmTestStatusMeter from 09-10.MDB (see Figure 9-32). To start the status meter, click the Start button and frmStatusMeter will pop up. If you want the status meter to include a Cancel button, check the Include Cancel button checkbox before clicking the Start button. The status meter will slowly advance to 100% and then close. If you've included a Cancel button, you can click on it at any time to immediately close the status meter and notify the calling form (frmTestStatusMeter) that the cancel has been requested.

Figure 9-32. The frmStatusMeter form

Create a generic status meter

To create a generic status meter for your own application, follow these steps (or skip these steps entirely and import frmStatusMeter and basStatusMeter from 09-10.MDB into your database):

1. Create a form and set its properties as shown in Table 9-10.

Table 9-10: Property settings for the status bar form

Property	Value
DefaultView	Single Form
RecordSelectors	No
ScrollBars	Neither
NavigationButtons	No
BorderStyle	Thin
PopUp	Yes
MinMaxButtons	None

2. Place a rectangle on the form, name it recStatus, and set its Width property to 0. Set its background color to the color of your choice.
3. Place a label on the form, name it lblStatus, and set its Width property to the total width you want the status bar to be. Set its Background to Clear. In the Label property, type in "0% Completed".
4. Add a command button control named cmdCancel with a caption of "Cancel". Create an event procedure attached to the button's Click event. (If you're unsure of how to do this, see the "How Do I Create an Event Procedure?" section in the Preface of this book.) Add the following code to the event procedure:

Private Sub cmdCancel_Click(  )
    mfCancel = True
End Sub

5. Add the following global declaration to the global declarations section of the form's module:

Dim mfCancel As Boolean

6. Add the following three property procedures to the form's module:

Public Property Let InitMeter(fIncludeCancel As Boolean, strTitle As String)
   
   Me!recStatus.Width = 0
   Me!lblStatus.Caption = "0% complete"
   Me.Caption = strTitle
   Me!cmdCancel.Visible = fIncludeCancel
   
   DoCmd.RepaintObject
   
   mfCancel = False
 
End Property
 
Public Property Let UpdateMeter(intValue As Integer)
   
   Me!recStatus.Width = CInt(Me!lblStatus.Width * (intValue / 100))
   Me!lblStatus.Caption = Format$(intValue, "##") & "% complete"
 
   DoCmd.RepaintObject
 
End Property
 
Public Property Get Cancelled(  ) As Boolean
   Cancelled = mfCancel
End Property

7. Save the form as frmStatusMeter and close it.
8. Create a new global module and add the following code (or import the module basStatusMeter from 09-10.MDB):

Private Const mconMeterForm = "frmStatusMeter"
 
Private Function IsOpen(strForm As String)
    IsOpen = (SysCmd(acSysCmdGetObjectState, acForm, strForm) > 0)
End Function
 
Public Sub acbCloseMeter(  )
   
    On Error GoTo HandleErr
   
    DoCmd.Close acForm, mconMeterForm
 
ExitHere:
    Exit Sub
HandleErr:
    Case Else
        MsgBox "Error#" & Err.Number & ": " & Err.Description, _
         , "acbCloseMeter"
    End Select
    Resume ExitHere
End Sub
 
Public Sub acbInitMeter(strTitle As String, fIncludeCancel As Boolean)
   
    ' Initializes the status meter to 0.
    '
    ' In:
    '     strTitle - Title of status meter form
   
    On Error GoTo HandleErr
    
    DoCmd.OpenForm mconMeterForm
    Forms(mconMeterForm).InitMeter(fIncludeCancel) = strTitle
   
ExitHere:
    Exit Sub
HandleErr:
    Select Case Err.Number
    Case Else
        MsgBox "Error#" & Err.Number & ": " & Err.Description, _
         , "acbInitMeter"
    End Select
    If IsOpen(mconMeterForm) Then Call acbCloseMeter
    Resume ExitHere
    Resume
End Sub
 
Public Function acbUpdateMeter(intValue As Integer) As Boolean
   
    ' Updates the status meter and returns whether
    ' the Cancel button was pressed.
    '
    ' In:
    '     intValue - percentage value 0-100
    
    On Error GoTo HandleErr
    
    Forms(mconMeterForm).UpdateMeter = intValue
    
    ' Return value is False if cancelled.
    If Forms(mconMeterForm).Cancelled Then
        Call acbCloseMeter
        acbUpdateMeter = False
    Else
        acbUpdateMeter = True
    End If
   
ExitHere:
    Exit Function
HandleErr:
    Select Case Err.Number
    Case Else
        MsgBox "Error#" & Err.Number & ": " & Err.Description, _
         , "acbUpdateMeter"
    End Select
    If IsOpen(mconMeterForm) Then Call acbCloseMeter
    Resume ExitHere
End Function

9. Save and close the global module.

Use the generic status meter in your application

To use the generic status meter in your own applications, follow these steps:

1. When you wish to initialize the meter, use the following syntax:

Call acbInitMeter(title, flag)

2. where title is the title you want the status meter to assume, and flag is True (or -1) to display a Cancel button or False (or 0) to not display one. For example, this statement creates a status meter with the title Progress and a Cancel button:

Call acbInitMeter("Progress", True)

3. To update the meter with a new progress value, use the following syntax:

variable = acbUpdateMeter(value)

4. where value is an integer between 0 and 100. acbUpdateMeter will place True or False in the return value. If the return value is False, the user has pressed the Cancel button. (The return value will never be False if you choose not to include the Cancel button when initializing the status meter.) For example, to update the meter with a progress setting of 50%, you might call acbUpdateMeter like this:

fOK = acbUpdateMeter(50)

5. To close the status meter form, use this syntax:

Call acbCloseMeter

Discussion

You can change the size of the rectangle by manipulating its Width property. The Rectangle control is placed behind a transparent Label control that defines the boundaries of the status meter and contains the status text. The status meter form is manipulated by three public wrapper functions contained in basStatusMeter: acbInitMeter, acbUpdateMeter, and acbCloseMeter. These functions, in turn, interact with frmStatusMeter through its exposed properties. The wrapper functions know the names of the properties and how to call them, but they know nothing of the inner workings of the form.

acbInitMeter initializes the status meter by opening the status meter form and setting the InitMeter property to the appropriate string. At the same time, a parameter is passed that determines if the Cancel button is included on the status meter form:

DoCmd.OpenForm acbcMeterForm
Forms(acbcMeterForm).InitMeter(fIncludeCancel) = strTitle

acbUpdateMeter sets the value of the status meter form's UpdateMeter property. It then checks the Cancelled property of the form to determine whether the user has clicked on the Cancel button. If so, it closes the status meter form and returns False to the calling procedure; otherwise it returns True:

Forms(acbcMeterForm).UpdateMeter = intValue
 
' Return value is False if cancelled.
If Forms(acbcMeterForm).Cancelled Then
    Call acbCloseMeter
    acbUpdateMeter = False
Else
    acbUpdateMeter = True
End If

acbCloseMeter closes the status meter form using the DoCmd.Close method:

DoCmd.Close acForm, acbcMeterForm

Internally, the Let and Get property procedures do all the work. When the InitMeter property is set by some external procedure, the InitMeter Let property procedure runs the following code:

Me!recStatus.Width = 0
Me!lblStatus.Caption = "0% complete"
Me.Caption = strTitle
Me!cmdCancel.Visible = fIncludeCancel
 
DoCmd.RepaintObject
 
mfCancel = False

This code sets the Width property of the recStatus control to 0 and the Caption property of lblStatus to "0% complete", updates the form's Caption property with the strTitle parameter, and sets the cmdCancel button's Visible property to match the fIncludeCancel parameter. The code then uses the RepaintObject method to force an update of the screen and resets the mfCancel module-level global variable to False.

When the UpdateMeter property of the form is set to a value, the following code is executed by the UpdateMeter Let property procedure:

Me!recStatus.Width = CInt(Me!lblStatus.Width * (intValue / 100))
Me!lblStatus.Caption = Format$(intValue, "##") & "% complete"
 
DoCmd.RepaintObject

This code updates the status meter by changing the width of the recStatus control relative to the width of the lblStatus control. This relative change ensures that the status meter rectangle never exceeds the limits as defined by the width of the lblStatus control. The routine then updates the Caption property of the lblStatus control to a formatted percentage value concatenated to the string "% complete". Once again, the code uses the RepaintObject method to force an update of the screen.

The Cancelled property of the status meter form is handled by the Cancelled Get property procedure. When called by an external procedure, this procedure returns the value of the module-level global mfCancel variable. This variable, which was initialized to 0 by the IntitMeter Let property procedure, is set to False if the user clicks on the cmdCancel button in the cmdCancel_Click event procedure.

It's a good idea to encapsulate the inner workings of a generic utility form such as frmStatusMeter by keeping all the event procedures private and using Let and Get property procedures to expose a controlled user interface to calling procedures. Getting in the habit of thinking and coding in this object-oriented way will allow you to create generic components that you can reuse over and over again.

The pop-up status meter form's AutoCenter property has been set to Yes, so it will always appear in the center of the screen. You may wish to extend acbInitMeter with optional left and top parameters so you can precisely position the form on the screen when it is first opened.

As an alternative to the form presented in this solution, you may wish to employ one of the ActiveX controls that offers similar functionality. The Developer version of Office and Visual Basic both include the Progress Bar control, and Visual Basic also includes the Gauge control. Both controls can be used to indicate the progress of long operations.

Back to: Access Cookbook