This article will offer a simple solution of how to let the person know what is needed from them on Microsoft Access data entry forms and where it is needed.

Last week I was asked for a way to give the person an on screen message while on Microsoft Access data entry forms without it requiring them to dismiss it by clicking the OK button on a message box. My reply was to have a label change it’s caption property from nothing to “The desired message…” then back to nothing using the form’s built in timer and some OnTimer event code. This article using some code similar to the last posted article.  It addresses two areas of Microsoft Access data entry forms assistance. The first is a visual clue as to where that skinny little cursor is blinking and second, what the user needs to know about the current form control that has cursor focus. The first area was addressed by changing the background color to a light shade of blue as the visual clue. The second area was addressed by having a custom message appear in a label at the top of the form. The messages are stored in the Tag property of each control that requires a message to be displayed by Microsoft Access. Data entry forms might have a message that simply lets the data entry person know that this is a required field or it might supply more about the type of information that should be entered in this field. This message is turned off when the user move to the next control on the form.

Below is a short video demonstrating how this works.

This green colored code below is all you need at the form level. It is placed in the form’s On Open Event.

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

Each form control has a tag property where you can type in the message you want to appear in the ‘lblControlMsg’ label that you will need to place on your form. If you need help finding the tag property see this video.

Place code below in a code module:

Public Sub SetEventHandlers(frm As Form)

‘Runing this routine from the OnOpen event of any form using
‘Call SetValidatorEventHandlers(Me)
‘will replace any OnGotFocus and OnLostFocus event code
‘with a call to the Highlighter function.
On Error GoTo errline
Dim ctl As Control
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox
If ctl.Visible = True Then
ctl.OnGotFocus = “=Highlighter(true)”
ctl.OnLostFocus = “=Highlighter(false)”
End If
Case Else
End Select
Next
exitline:
Exit Sub
errline:
Select Case Err.Number
Case Else
MsgBox “Error ” & Err.Number & Chr(10) & Err.Description
GoTo exitline
End Select

End Sub

Public Function Highlighter(TurnOn As Boolean)

On Error GoTo errline
Select Case Screen.ActiveControl.ControlType
Case acTextBox, acComboBox, acListBox
If TurnOn Then
Screen.ActiveControl.BackColor = 16773857
‘requires that the name of the message control be labeled ‘lblControlMsg’
Screen.ActiveForm![lblControlMsg].Caption = Screen.ActiveControl.Tag
Else
Screen.ActiveControl.BackColor = -2147483643
Screen.ActiveForm![lblControlMsg].Caption = “”
End If
Case Else
End Select
exitline:
Exit Function
errline:
Select Case Err.Number
‘Form window not visible yet to see Screen.ActiveControl
‘(Only needed when setting event handlers by code)
Case 2475
Resume exitline
Case Else
MsgBox “Error ” & Err.Number & Chr(10) & Err.Description
GoTo exitline
Resume Next
End Select
End Function

Let me know how this worked for you by entering your comments and suggestions.