On a subject form, how can I highlight a required control IF AND ONLY IF the user misses it

Jmaxton2160 

You can do this with the following setup and VBA-code. I assume you have some basic VBA knowledge. Otherwise, let me know and I will elaborate on this.

Furthermore, we focus on text and combo boxes while these field have a background color that we can set (highlight).

First, we address the required form fields by using the Tag-property. In the example below I mark the Description-field as required by setting the Tag-property in the Other-tab to the value “required”. The Tag-property can hold any text we want, so we use it here to our convenience.

I also marked the Unit-combo box as required.

Next, we build a VBA-function that checks the required fields of a form. You can place this function in the code section of the concerning form, but it’s more suitable to place it in a module because this makes it possible to call the function from any form you want.

If necessary, add a module (Insert > Module from the VBA-editor) and place this function in it:

Function ValidateRequired(frm As Form, sFormName As String) As Boolean
  'Check if the required fields of a form are entered
  
  'Declare
  Dim ctl As Control, ctlFirst As Control, iCounter As Integer
  
  'Init
  ValidateRequired = True
  iCounter = 0

  'Loop through the form controls
  For Each ctl In frm.Controls
    'Check for required tag
    If ctl.Tag = "required" Then
      'Is the control empty?
      If IsNull(ctl) Then
        'Only set the background for text and combo boxes
        Select Case ctl.ControlType
          Case acTextBox, acComboBox
          ctl.BackColor = 10092543
          iCounter = iCounter + 1 'count the number of required fields that are empty
        End Select
            
        'Set the outcome of this function to false, so the form update can be cancelled
        ValidateRequired = False
            
        'Keep the first control that's highlighted for setting focus on this control later on
        If ctlFirst Is Nothing Then Set ctlFirst = ctl
            
      Else
        '(Re)set the background of a required field that's filled
        Select Case ctl.ControlType
          Case acTextBox, acComboBox
            ctl.BackColor = vbWhite
          End Select
      End If
    End If
  Next

  'Set focus to the first highlighted field, if any, and signal an info message
  If ValidateRequired = False Then
    On Error Resume Next
    ctlFirst.SetFocus
    On Error GoTo 0
    
    Select Case iCounter
    Case 1
      MsgBox "The highlighted field is required.", vbInformation, sFormName
    Case Is > 1
      MsgBox "The highlighted fields are required.", vbInformation, sFormName
    End Select
  End If
End Function

This function takes the form(object) as input and loops over all the form controls, including the text and combo boxes. If a control has a Tag-property with the value “required” and a type of text or combo box, the function check if it is filled.

If not, the background of the concerning field is set to a yellow color. If a required field is filled, the background is set to white. Furthermore, the function signals a message when one or more required fields are empty.

You can change the highlight-color and the message as you like. Or even comment out the message if you don’t want it displayed.

Last step is calling the validation-function from the form. In my example I call it from the Click-event of the Save-command button:

Private Sub cmdSave_Click()
  'Validate required fields
  If Not ValidateRequired(Me, "Product") Then Exit Sub

  'Save the record
  If Me.Dirty Then Me.Dirty = False 'Save

  'Close form
  DoCmd.Close acForm, Me.Name
End Sub

The ValidateRequired-function takes two parameters: the form object and the name of the form as you want it displayed in the error-message. Here, we refer with the “Me” keyword to the form object. We call the function from an If-statement, so in case of an error, we exit the subroutine without saving and the form stays on the screen.

Now when you open the form in View mode and click Save without entering any data, you will get this screen:

The required fields are highlighted and the focus is set on the first missing field. After filling the required fields and clicking Save again, the record is entered in Access and the form is closed.

Hope this works for you.

Best wishes,

Tieme