Version 1.2 Approx 0.9 MB (zipped) First Published 10 Nov 2023
This is the third in a series of articles about highlighting objects on Access forms.
If this series doesn't get my articles listed as one of this week's highlights in Mike Wolfe's Week in Review blog, then nothing will! We'll find out if it works!
When one or more fields are set as REQUIRED in the table design, Access will prevent records being saved unless each of those felds has been populated.
However, the error messages provided by Access may not be particularly helpful.
In the form below, 3 fields are REQUIRED: Title, FirstName & LastName but there is no indication to end users that this is the case.
If a record is partly completed leaving one or more of those fields blank and you then try to move to a new record, Access will show a message similar to this:
The message only mentions the first blank required field (Title).
If the user completes that field and again tries to leave the record, another message appears with the name of the next blank required field (LastName) and so on until each required field is finally completed.
In my opinion, this leads to a very poor user experience. However, with a small amount of work, the process can be significantly improved for end users.
The example app shows three different ways of informing users where required fields have missing data, using both messages and highlighting the controls.
In each form, the current record is highlighted in yellow using the same approach as in my previous article: Highlight Required Fields.
In addition, the required fields are clearly marked on each form.
Form 1 - message only
The required fields are identified with a red * together with an explanatory note.
If a user attempts to move to a new record before all fields are completed, this message will be shown:
Trying to move to a previous record will trigger a message similar to that in the first screenshot above.
Form 2 - message and highlighted controls
In this example, the required fields are highlighted in cyan. Each required field has a tag property = "REQ"
If a user attempts to move to a new record before all fields are completed, the message displayed will state which required fields still need to be completed:
The code to do this is contained in the following form procedures:
Dim strTitle As String, strFN As String, strLN As String
Dim ctrl As Access.Control, strText As String
Private Sub CheckControls()
On Error Resume Next
strText = "" 'clear existing value of strText
strTitle = Nz(Me.Title, "")
strFN = Nz(Me.FirstName, "")
strLN = Nz(Me.LastName, "")
For Each ctrl In Screen.ActiveForm.Controls
'If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox And ctrl.Tag = "REQ" Then
If ctrl.Tag = "REQ" Then
If Nz(ctrl.Value, "") = "" Then
strText = strText & vbCrLf & vbTab & "- " & ctrl.Name
ctrl.BackStyle = 1 'Normal
ctrl.BackColor = vbCyan 'optional - highlight all empty controls
ctrl.BackStyle = 0 'Transparent
Private Sub CompleteFieldsMessage()
FormattedMsgBox "You must complete all required fields before the record can be saved. " & _
"@The following required fields have not been completed" & _
strText & " @", vbExclamation, "Required fields MISSING"
The CheckControls procedure is run from the Form_BeforeUpdate event.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If strText <> "" Then CompleteFieldsMessage
The CheckControls procedure is also run from several other events :
a) Form_Load & Form_Current
b) AfterUpdate events for each of the required field controls
c) cmdNew_Click & cmdReport_Click
This ensures the highlighting is shown for all blank required fields and removed once each field has been completed
Form 3 - highlighted controls only
This form is mainly based on code by Allen Browne: Highlight the required fields, or the control that has focus
I made some minor additions to the code to also format the control labels
In this example, when a user attempts to move to a new record before all fields are completed, the message displayed will similar to that in Form 1 but in this case, the required fields are also highlighted:
The code to do this is in a standard module modHilightAJB and explained in the above article
Although the code is more complex, using it is very easy
Only one line of code is needed: SetupForm Me in the Form_Load event
All of the above methods work. Choose the method that best suits your needs
Click to download: Highlight Required Fields v1.2.accdb Approx 0.9 MB (zipped)
Download and unblock the zip file.
For more details, see my article: Unblock downloaded files by removing the Mark of the Web
Unzip and save the ACCDB file to a trusted location.
Highlight Current Record in Continuous Form
Highlight Current Control
Highlight Search Results
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 10 Nov 2023
Return to Example Databases Page
Return to Top