Highlighting the Current Field in Data Entry Forms in Access 2007

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.

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

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
       Else
           ' 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
       
   ExitHere:
       Exit Sub
   
   HandleErr:
       ' 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

   

4. Add the following code to the form's Open event procedure.

   Private Sub Form_Open(Cancel As Integer)
       Me.SetFocus
   End Sub

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.

Watch the Video

Video Length: 00:08:35

File Size: 7.94 MB WMV

• Access 2007 Sample: Highlighting the Current Field in Data Entry Forms

• Access Developer Portal

• Access 2007 Resource Center

• Visual Basic Programmer's Guide

• SpecialEffect property

• BackColor property

• Screen.ActiveControl property

• SetFocus method