Recipe 2.2 Highlight the Current Field in Data-Entry Forms

2.2.1 Problem

The text cursor is too small in Access, and you can't always tell which text box on a form has the focus. You need some way to really highlight the current field.

2.2.2 Solution

There are many visual cues you can use to tell the user which text box contains the cursor. You can change the color of the text or the background, change the appearance of the text box, or change the appearance of the text box's label.

The simplest solution, which works quite well, is to change the BackColor and SpecialEffect properties of the active control. This solution uses some simple VBA code, which is attached to each control's Enter and Exit events, to do the work. Figure 2-3 shows the sample form, frmEffects, in use (with the City field currently selected).

Figure 2-3. frmEffects in use, showing the active field

Open 02-02.MDB and load frmEffects. As you move from field to field on the form, note that the special effect and the background color of each control change when you enter and again when you leave the control.

Follow these steps to create a form with this same sort of functionality:

  1. Create a new module and name it basSpecialEffects. In the declaration section, create the following constants, which will represent the controls' SpecialEffect and BackColor property settings:

    Option Compare Database
    Option Explicit
    Private Const conWhite = 16777215
    Private Const conGray = -12632256
    Private Const conIndent = 2
    Private Const conFlat = 0
  2. Create two functions named SpecialEffectEnter and SpecialEffectExit that will toggle the values of the BackColor and SpecialEffects properties for the text boxes. The completed module is shown in Figure 2-4.

    Here are the code listings for the two functions:

    Public Function SpecialEffectEnter( )
    On Error GoTo HandleErr
        ' Set the current control to be indented.
        Screen.ActiveControl.SpecialEffect = conIndent
        ' Set the current control's background color to be white.
        Screen.ActiveControl.BackColor = conWhite
        Exit Function
        MsgBox Err & ": " & Err.Description
        Resume ExitHere
    End Function
    Public Function SpecialEffectExit( )
    On Error GoTo HandleErr
        ' Set the current control to be flat.
        Screen.ActiveControl.SpecialEffect = conFlat
        ' Set the current control's background color to be gray.
        Screen.ActiveControl.BackColor = conGray
        Exit Function
        MsgBox Err & ": " & Err.Description
        Resume ExitHere
    End Function
Figure 2-4. The completed basSpecialEffects module
  1. Create your input form, if you haven't already. In design mode, select all of the text boxes to which you'd like to attach this effect. (Shift-clicking with the mouse allows you to select multiple controls.) When you select a group of controls, you can set properties for all of them at once. Set the properties of this group of controls as shown in Table 2-1. Figure 2-5 shows the design surface with all the text boxes selected. (Note that once you select multiple controls, the properties sheet's title can no longer display the name of the selected control and it will only show "Multiple selection," as shown in Figure 2-5.)

Figure 2-5. frmEffects in design mode, with all the text boxes selected

Table 2-1. Property settings for selected controls on frmEffects






=SpecialEffectEnter( )


=SpecialEffectExit( )

  1. Add the following code to the form's Load event procedure (see the Preface for information on creating event procedures):

    Sub Form_Open (Cancel As Integer)
    End Sub

2.2.3 Discussion

The SpecialEffectEnter and SpecialEffectExit functions do their work by reacting to the events that occur when you enter or leave a control on the form. Every time you enter one of the text boxes to which you've attached a function, Access executes that function. Therefore, whenever you enter one of these special text boxes, Access will cause the text box to appear sunken and will change its background color to white. When you leave the control (by tab or mouseclick), Access will set it back to being flat and will reset its background color to gray.

The pair of functions do their work for any control by using the built-in Screen.ActiveControl object. This object always provides a reference to the currently active control. Therefore, when you enter a control, the function acts on that particular control, setting the SpecialEffects and BackColor properties.

The only problem with this mechanism is that, when Access first opens a form, there isn't a current control. Attempting to refer to Screen.ActiveControl before the form is fully loaded results in an Access error. Because Access attempts to enter the first control on your form when it first opens the form and there isn't yet a current control, the code you've attached to that first text box's OnGotFocus event property will fail. To work around this problem, you need to use the code attached to the Open event, as shown in Step 4. This tiny bit of code forces Access to load the form completely before it attempts to enter the first text box on the form. You may find this technique useful in other applications you create that use Screen.ActiveControl.

The functions used in this solution could be extended to include many other changes to the controls as you enter and leave them. For example, you can change the font or its size, or the foreground color. You might wonder why this example calls functions directly from the Properties window, instead of using the standard mechanism for setting up event handlers. In this case, because multiple controls call the same procedures in reaction to the same events, it's simpler to set up the function calls directly from the Properties window. This isn't the only solution, but it's a quick and easy one, when you need to have multiple events of multiple controls call the same procedure.

2.2.4 See Also

See How Do I Create a Module in the Preface for information on creating a new module.