ValidateData()...

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 every control on your Form you want to be filled before saving or going to a new record place "require" (no quotation marks) on Tag line in the Properties Window of said controls.  To use...
If frmValidateData 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 ALL the fields tagged require are not filled in.  Instead you will get a message telling which fields are not filled in.
The difficult I do immediately, the impossible takes a little bit longer.
Function frmValidateData() As Boolean
On Error GoTo ErrHandler
'From  http://www.access-diva.com/
 'For use with forms that have no subforms
 
     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
              MsgBox (ctl.Name & " cannot be empty.")
              ctl.SetFocus
              GoTo Complete
            End If
          End If
        End If
      End If
   Next ctl
 
Complete:
  Set ctl = Nothing
  frmValidateData = 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()
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.