Highlight required form fields

This procedure allows you to highlight a bound form's required fields so the user knows what data they must enter before attempting to save the record.

Each bound form has a recordset, and each recordset has a Fields collection. Each field has a Required property, and it's this property the procedure checks for every bound control on the form.

Add the following code to a standard module:

Public Sub ShowRequiredFields(frm As Form, HighlightColor As Long)
    Dim ctl As control
    Dim rs As DAO.Recordset
    Dim blnIsRequired As Boolean
    

    On Error Resume Next
    

    Set rs = frm.RecordsetClone
    

    For Each ctl In frm.Controls
        blnIsRequired = rs.Fields(ctl.ControlSource).Required
        If (Err = 0) And blnIsRequired Then ctl.BackColor = HighlightColor
        Err.Clear
    Next ctl
    

    Set ctl = Nothing
    Set rs = Nothing
End Sub

Then add the following line of code to the form's Current event, specifying whatever colour you want to use to highlight the field.

ShowRequiredFields Me, vbYellow