Microsoft Access: Tips for Serious users

Provided by Allen Browne.  Created: September 2008.  Last updated: May 2009.

Highlight the required fields, or the control that has focus

Would you like your forms to automatically identify the fields where an entry is required?

How about highlighting the control that has the focus, so you don't have to search for the cursor?

This utility automatically does both in any form in Form view (not Continuous), just by setting a property.

In the screenshot (right), Title is highlighted as the current field (yellow), and the name fields are required (red background, and bold label with a star.) Modify the colors to whatever style suits you.

Implementation

To use this in your database:

1. Download the example database (24 kb zipped, for Access 2000 or later.)
2. Copy the module named ajbHighlight into your database.
3. Widen the labels attached to your controls (to handle the star and bolding.)
4. Set the On Load property of your form to:
       =SetupForm([Form])

Do not substitute the name of your form in the expression above, i.e. use the literal [Form] as shown. 

Options

To highlight the required fields only, use:
    =SetupForm([Form], 1)

To highlight the control with focus only, use:
    =SetupForm([Form], 2)

If your form's OnLoad property is set to [Event Procedure] add this line to the code:
    Call SetupForm(Me)

Change the color scheme by assigning different values to the constants at the top of the module. mlngcFocusBackColor defines the color when a control gains focus. mlngcRequiredBackColor defines the color for required fields. Use  RGB values (red, green, blue.) Note that:

• In Datasheet view, only the asterisk shows (over the column heading)
• In Continuous form view (where you typically have not attached labels), only the background color shows.

(You could modify the code with the CaptionFromHeader() function from the FindAsUType utility, so as to bold the labels in the Form Header over the columns.)

Note that the labels will not be bolded or have the star added if they are not attached to the controls. To reattach a label in form design view, cut it to clipboard, select the control to attach it to, and paste.

Limitations

1. The code highlights only text boxes, combo boxes, and list boxes.
2. A control will not highlight if it already has something in its On Got Focus or On Lost Focus properties. Use OnEnter or OnExit for the existing code.

How it works

You can use the code without understanding how it works: this explanation is for those who want to learn how it works, or modify what it does.

The main function — SetupForm() — accepts two arguments: a reference to the form you are setting up, and an integer indicating what parts you want set up. The integer is optional, and defaults to all bits on (except the sign.) We are actually only using the first two bits (for required and focus-color); you can use the remaining bits for other things you want to set up on your form. SetupForm() examines the bits, and calls separate functions to handle the required and focus-color issues.

Highlighting the control with focus

The OnGotFocus event fires when a control get focus, and its OnLostFocus event when focus moves away. We can therefore use these events to highlight (by setting its BackColor) and restore it. But we needs these events to fire for each control that can get focus. SetupFocusColor() assigns these properties for us when the form loads.

So, SetupFocusColor() loops through each control on the form. It looks at the ControlType property, and skip anything other than a text box, combo, or list box, and controls that are already using OnGotFocus or OnLostFocus. It then sets property values this (using Text0 as an example):

Assigning these properties automatically when the form opens makes it easier to design and maintain.

Now, when any of these controls receives focus, it calls Hilight(), passing in a reference to itself, and a True flag. When it loses focus, it calls Hilight() with a False flag.

If the flag is True (i.e. the control is gaining focus), Hilight() simply sets its BackColor to the value specified in the constant mstrcTagBackColor. You can set that value to any number you wish at the top of the module. Just use any valid RGB (red-green-blue) value.

If the flag is False (i.e. the control is losing focus), Hilight() needs to set it back to its old color. Our initialization — SetupFocusColor() — stored the usual background color for the control in its Tag property. Tag could be used for other things as well (typically separated by semicolons), so we call ReadFromTag() to parse the value from the tag. If we get a valid number, we assign that to the BackColor. Otherwise (e.g. if some less polite code overwrote the Tag), we assign the most likely background color (white.) 

Highlighting required fields

SetupRequiredFields() is the function that provides the formatting for fields that are required.

Again, we loop through the controls, ignoring anything other than text box, combo, or list box. We also ignore it if its Control Source is unbound (zero-length string), or bound to an expression (starts with =.) Otherwise the Control Source must be the name of a field, so we look at that field in the Recordset of the form. If the field's Required property is true, we will highlight it. We also check if the field's Validation  Rule includes a statement that it is not null: some developers prefer this to the Required property, as it allows them to use the field's Validation Rule to give a custom message.

If we determined that the field is required, we set the BackColor of the control to the color specified in the constant mlngcRequiredBackColor. Then we call MarkAttachedLabel() to format its label as well. The reason for using a separate function here is that the control may not have an attached label, so an error is likely. It's simplest to handle that error in a separate function.

If there is an attached label, it will be the first member of the Controls collection of our control — Controls(0). If there is no attached label, the error handler jumps out. Otherwise we add the asterisk to its Caption (unless it already has one), and sets it to bold. Using bold looks good on continuous forms but does not show on datasheets. The asterisk does show in the Column Heading in datasheet view. You can use whatever formatting suits you.