fValidateData()...

Place the below in your modUtilities or in a new Module, remembering not to name the Module the same as the Function, and save.  To use...
On the Tag line in the Properties Window of every Control on your Form you want to force to be filled before saving or going to a new record place the word "require" (no quotation marks).  Then on Command Button that is used to Save, go to Next or Previous and\or Commit the record place the *If fValidateData Then* line around your code confirming the update or save, as in...
If fValidateData Then
     intResp = MsgBox("Your Question?", vbYesNo + vbQuestion, "Your Title?")
     If intResp = vbYes Then
        Call LogChange(Me.txtID, "frmYourForm", "Your Note of What Was Done.")
        strSQL = "UPDATE tblYourTable SET ytYourField = 5 WHERE rID = " & Me.txtID
        CurrentDb.Execute strSQL, dbFailOnError
     End If
        DoCmd.RunCommand acCmdSaveRecord
        Call SendeMail
        DoCmd.SelectObject acReport, "YourReport", True
        DoCmd.PrintOut , , , , 2
End If
The code will not execute if the Controls on your Form do not have require in the Tag line.  The ones that do  will give you a message indicating which Control is missing data, as well as, setting the focus to and highlighting  said Control.
The difficult I do immediately, the impossible takes a little bit longer.
Function fValidateData() As Boolean
On Error GoTo ErrHandler
'From  https://www.access-diva.com/vba13.html
'Use with Command Button on Subform or on single Form in Before_Update event
'rtw 8.29.2015 Updated to cut off "ID" and split based on CamelCase
'rtw 7.30.2019 Updated to add highlighting of Control with missing data
 
    Dim ctl As Control
    Dim blnValid As Boolean
    Dim frm As Form
 
    Set frm = Screen.ActiveForm
 
      blnValid = True
 
      For Each ctl In frm.Controls
        If ctl.Tag <> "" Then
          If ctl.Enabled Then
            If InStr(1, ctl.Tag, "require") Then
              If Nz(ctl, "") = "" Then
                blnValid = False
                    'Displays name of each control minus *ID* if at the end and the prefix, i.e. txt, cbo, lbl
                    If Right(ctl.Name, 2) = "ID" Then
                        MsgBox SplitOnUppercase((Right(Left(ctl.Name, Len(ctl.Name) - 2), Len(Left(ctl.Name, Len(ctl.Name) - 2)) - 3)) & " MUST be filled in!")
                    Else
                        MsgBox SplitOnUppercase((Right(ctl.Name, Len(ctl.Name) - 3)) & " MUST be filled in!")
                    End If
                    'Highlights the control
                    Select Case ctl.ControlType
                        Case acTextBox
                            ctl.BackColor = RGB(254, 242, 154)  'Yellow
                        Case acComboBox
                            ctl.BackColor = RGB(254, 242, 154)  'Yellow
                    End Select
                'Sets the fovus to the Control
                ctl.SetFocus
                GoTo Complete
              Else
                'If more than one Control will turn the filled in ones back to white
                Select Case ctl.ControlType
                    Case acTextBox
                        ctl.BackColor = vbWhite
                    Case acComboBox
                        ctl.BackColor = vbWhite
                End Select
              End If
            End If
          End If
        End If
      Next ctl
 
Complete:
  Set ctl = Nothing
  fValidateData = blnValid
  Exit Function
 
ErrHandler:
  blnValid = False
  MsgBox ("Error validating: " & Err.Description)
  Resume Complete
End Function
VBA
Tips (Main)
Home
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
fMouseOverCurrent()
fHighlightRequiredControls()
Check if Table Exists
fAmortization()
Insert (or Remove) Blank Line
Get SplitOnUppercase() Function click here.

Adding this Function allows the message to display the name of the Control on your Form without any prefixes and with the appropriate spaces providing you use the Naming Conventions, described here, for Controls on your Forms, i.e. Company Name instead of txtCompanyName.  If you use a different Naming Convention you will need to adjust the Function.
If fValidateData Then
     Cancel = True
     Exit Sub
End If
To use in the Before_Update event of your Form (no Subform)...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.