This documentation is archived and is not being maintained.

Highlighting the Current Field in Data Entry Forms in Access 2007

Office 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary:  Use indentation and color in the current control on a form to highlight the current control. (2 printed pages)

Office Visual How To

Applies to:  2007 Microsoft Office System, Microsoft Office Access 2007

Adapted from Access Cookbook, 2nd Edition by Ken Getz, Paul Litwin and Andy Baron Copyright (c) 2004, O'Reilly Media, Inc. All rights reserved. Used with permission.

Ken Getz, MCW Technologies, LLC

April 2009


Access has a text cursor that is sometimes too small to tell which text box on a form has the focus. By using a combination of indentation and color, you can make it clear to the user which control currently has the focus.

Code It

Download the sample database

There are many visual cues that you can use to signify which text box contains the cursor. For example, you can change the color of the text or the background, or change the appearance of the text box and its label.

A simple and effective solution is to change the BackColor and SpecialEffect properties of the active control. To implement the solution, you'll need some simple VBA code attached to the Enter and Exit events of each control.

Figure 1 shows the sample form, frmEffects, with the City field currently selected.

Figure 1. frmEffects in use, showing the active field

Sample form with a selected field

If you download the sample database, HighlightCurrentField.accdb, to interact with this sample form, open the database, and then open frmEffects. As you move from field to field on the form, note that the special effect and background color of each control changes when you enter the control and when you leave it.

To create a form with this same sort of functionality, follow these steps:

  1. Create a new module and name it basSpecialEffects. In the declaration section at the top of the module, create the following constants to represent the SpecialEffect and BackColor property settings for the controls

    Private Const conActiveColor = vbWhite
    Private Const conNonActiveColor = vbButtonFace
    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 SpecialEffect properties for the text boxes, and the common sub MakeActive, which does the work. Here are the code listings for the procedures.

    Private Sub MakeActive(ctl As Control, active As Boolean)
        On Error GoTo HandleErr
        If active Then
            ' Set the current control to be indented
            ' Set the current control's background color to be white
            ctl.SpecialEffect = conIndent
            ctl.BackColor = conActiveColor
            ' Set the current control to be flat
            ' Set the current control's background color to be gray
            ctl.SpecialEffect = conFlat
            ctl.BackColor = conNonActiveColor
        End If
        Exit Sub
        ' Uncomment to debug:
        ' MsgBox Err & ": " & Err.Description
        Resume ExitHere
    End Sub
    Public Function SpecialEffectEnter()
        MakeActive Screen.ActiveControl, True
    End Function
    Public Function SpecialEffectExit()
        MakeActive Screen.ActiveControl, False
    End Function
  3. Create your input form, if you haven't already. In design mode, select all of the text boxes that you want to modify. (To select multiple controls, hold down SHIFT and click the right mouse button.) When you select a group of controls, you can set properties for all of them at once. Set the properties for this group of controls as shown in Figure 2. (Note that once you select multiple controls, the title of the Properties sheet can no longer display the name of the selected control, and it shows only "Multiple Selection", as shown in Figure 2.)

    Figure 2. The Properties sheet, showing the properties that you must set for the text box controls on the form

    Setting the properties for a group of controls
  4. Add the following code to the form's Open event procedure.

    Private Sub Form_Open(Cancel As Integer)
    End Sub
Read It

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. For example, when you attach a function to a text box, Access executes that function every time you enter that text box. In this case, when you enter the text box, Access makes the text box appear sunken, with a white background. When you leave the text box, Access reverts to the flat text box, with a gray background.

These functions do their work for any control by using the built-in Screen.ActiveControl object, which always provides a reference to the active control. Thus, when you enter a control, the function attached to that control 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, the form does not have a current control. If you attempt to refer to Screen.ActiveControl before the form is fully loaded, it generates 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 that you attached to the OnGotFocus event property of that first text box will fail.

To work around the problem, use the code attached to the form's 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 might find this technique useful in other applications you create that use Screen.ActiveControl.

The MakeActive procedure that you used in this solution could be extended to include 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 procedure in reaction to the same events, it is simple 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, especially when you need to have multiple events of multiple controls call the same procedure.

See It

Highlighting the current data entry field

Watch the Video

Video Length: 00:08:35

File Size: 7.94 MB WMV

Explore It