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
            'loop through
            '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
                  Else
                        ctrl.BackStyle = 0 'Transparent
                  End If
            End If
      Next ctrl

End Sub

'=========================================

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"

End Sub

The CheckControls procedure is run from the Form_BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel As Integer)

      CheckControls
      If strText <> "" Then CompleteFieldsMessage

End Sub

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

NOTE:
All of the above methods work. Choose the method that best suits your needs



Download

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.



Related Articles:

      Highlight Current Record in Continuous Form

      Highlight Current Control

      Highlight Search Results



Feedback

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