Automating Microsoft Access with VBA

Text boxes are the second most common control on most forms (the most common, labels, are pretty uninteresting from the point of view of automation). You saw in Chapter 11's case study how you can use the events of text boxes to prevent users from entering invalid data, but there's much more that you can do with text boxes and VBA. This section of the chapter reviews some of the key properties of text box controls, and then shows you some useful techniques.

Key Properties of Text Boxes

You probably already know most of the properties of text boxes they're visible in the Properties window when you select a text box in Design mode in the Access user interface. Although you can work with most of these properties in code, some are more useful in code than others. Table 13.1 lists some of the more useful properties.

TIP

Remember, when you refer to things in VBA, there are no spaces in the name. So the TextBox object has a ValidationText property, which corresponds exactly to the Validation Text property of a text box control on the Properties window.

You can use these properties to adjust the appearance and behavior of a text box at runtime (while the user is working with the form). You might note that there are four properties dealing with the text in a text box. Of these, the SelText, OldValue, and Text properties are valid only for the text box that currently has the focus. The Text property is available for any text box on the form. If you try to retrieve, for example, the Text property of a text box that doesn't have the focus, VBA reports an error.

The other property that deserves some extra comment is the Tag property. This is a property that Access doesn't use. It exists simply to give you a place to store data that's associated with the text box. You see how to use the Tag property later in the chapter, in the "Working with the Tag Property" section.

NOTE

Forms, reports, and all types of controls have a Tag property, so you can store arbitrary data with just about everything.

Tracking the Focus

Users of your database might have many different levels of experience. Inexperienced users sometimes have trouble with concepts that you might take for granted. For example, the notion that there is a focus, and that only the control with the focus accepts data, can be challenging when you're first starting with Access. Here's a technique that can help new users understand exactly which part of a form is active.

To start, you need a pair of procedures in a standalone module. One of these procedures sets the BackColor property of a control to the code for yellow, and the other sets it to white:

Sub HighlightControl(ctl As Control) ' Set the background color of the ' specified control to yellow, if possible On Error Resume Next ctl.BackColor = 65535 End Sub Sub UnhighlightControl(ctl As Control) ' Set the background color of the ' specified control to white, if possible On Error Resume Next ctl.BackColor = 16777215 End Sub

As you can see, these procedures accept a single argument whose type is Control. Control is a generic object type that you can use to represent any type of Access control: text boxes, combo boxes, labels, and so on. Access enables you to pass any control to these procedures without raising an error.

However, there's no guarantee that every control you pass in supports a BackColor property. That's why the On Error Resume Next lines are in these procedures. If the procedure can set the property, great; if not, it exits without any fuss.

To use these procedures, call the HighlightControl procedure from the GotFocus event of each control on your form, and the UnhighlightControl procedure from the LostFocus event. For example, here's code to use these procedures with the Timeslips form:

Private Sub DateWorked_GotFocus() HighlightControl DateWorked End Sub Private Sub DateWorked_LostFocus() UnhighlightControl DateWorked End Sub Private Sub EmployeeID_GotFocus() HighlightControl EmployeeID End Sub Private Sub EmployeeID_LostFocus() UnhighlightControl EmployeeID End Sub Private Sub Hours_GotFocus() HighlightControl Hours End Sub Private Sub Hours_LostFocus() UnhighlightControl Hours End Sub Private Sub TaskID_GotFocus() HighlightControl TaskID End Sub Private Sub TaskID_LostFocus() UnhighlightControl TaskID End Sub

Save everything and open the form. You'll find that a yellow highlight follows the focus as you tab around the form. Figure 13.1 shows the Timeslips form after clicking in the Date Worked field.

CAUTION

If you use this technique with one form, be sure to use it consistently across your entire application. Inconsistent applications are extremely difficult to use.

Working with Unbound Text Boxes

An unbound text box is one that is not connected to a particular field in a database table. Unbound text boxes are useful when you want to allow the users to enter data that's used transiently. For instance, the Billing Report Setup form in the TimeTrack database allows the users to select a client, start date, and end date, and open a report in preview mode. But it doesn't let users filter the results any more specifically than by date. Here's how you might handle that requirement:

1. Open the BillingReportSetup form in Design mode.

2. Place the mouse at the bottom of the design area of the form (indicated by the grid of dots), and then click and drag to make the form taller.

3. Add a new text box to the form. Because the form itself is unbound, the text box is automatically unbound. Name the new text box txtWhere. Set the attached label to WHERE clause.

4. Add a new command button to the form. Name the new button cmdAdvancedReport and set its caption to Advanced Report.

5. Open the form's module and add this code to handle the Click event of the new button:

   Private Sub cmdAdvancedReport_Click() On Error GoTo HandleErr DoCmd.OpenReport "BillingReport", acViewPreview, _ WhereCondition:=txtWhere.Value ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description & " in cmdAdvancedReport_Click" Resume ExitHere End Sub

6. Save the form.

To test the new controls, open the form in Form view. Select Bill's Auto Glass as the client, 5/1/2004 as the start date, and 6/1/2004 as the end date. Then enter Hours=7 as the WHERE clause, and click the Advanced Report button. As you can see in Figure 13.2, the resulting report displays only the timeslips on which exactly seven hours was reported.

Note that the code uses the Value property to retrieve information from a text box that does not have the focus.

For more information on WHERE clauses in reports, see "Populating the Report," p. 213.