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:
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
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
' Uncomment to debug:
' MsgBox Err & ": " & Err.Description
Public Function SpecialEffectEnter()
MakeActive Screen.ActiveControl, True
Public Function SpecialEffectExit()
MakeActive Screen.ActiveControl, False
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
Add the following code to the form's Open event procedure.
Private Sub Form_Open(Cancel As Integer)
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