Validate Required Fields on a Form

[zephyr223]

Hi All,
I have a form and need to make sure that 5 of those fields have data before the record is saved.
On the form the fields are in the following Tab order:
DATE NOTIFIED, DATE OF INCIDENT, FNAME, LNAME, AGENCY

I have some code on the BeforeUpdate event of the Form which is shown below:

When i test for a value in those columns by hitting the save button(with no data entered), the first field that it detects needs data is Date of Incident. I want it to validate each field based on it's tab position, so it should say DATE OF NOTIFICATION before DATE OF INCIDENT. Maybe I am missing something in my code, or maybe there is a better way to validate these fields. Any Help is appreciated. Thanks in advance.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, ctl As Control
 
   DL = vbNewLine & vbNewLine
 
   For Each ctl In Me.Controls
 
     If ctl.Tag = "?" Then
       If Trim(ctl.Value & "") = "" Then
 
        Msg = "'" & ctl.Name & "' is Required!" & DL & _
               "Please enter a value . . ."
         Style = vbInformation + vbOKOnly
         Title = "Required Data Missing! . . ."
         MsgBox Msg, Style, Title
         ctl.SetFocus
         Cancel = True
         Exit For
         End If
      End If
   Next
 
End Sub

[Seth Schrock]

Please read Before Posting (VBA or SQL) Code

I recently created a function for one of my databases to make sure that the fields that I needed to populated were in fact populated. It doesn't give the control name in the error message, but if you are following a naming convention, you probably wouldn't want the control name anyway. The control's label's caption could work, but I just make the label red and then tell the user to enter data in the highlighted fields.

Private Function VerifyFields() As Boolean
On Error GoTo Error_Handler
 
VerifyFields = True
 
If Me.txtBorrower & "" = "" Then
    Me.txtBorrower_Label.ForeColor = vbRed
    VerifyFields = False
Else
    Me.txtBorrower_Label.ForeColor = 8355711
End If
 
If IsNull(Me.cboLoanType) Then
    Me.cboLoanType_Label.ForeColor = vbRed
    VerifyFields = False
Else
    Me.cboLoanType_Label.ForeColor = 8355711
End If
 
If Me.cboCounty.Visible = True Then
    If IsNull(Me.cboCounty) Then
        Me.cboCounty_Label.ForeColor = vbRed
        VerifyFields = False
    Else
        Me.cboCounty_Label.ForeColor = 8355711
    End If
End If
 
 
Exit_Procedure:
 
    Exit Function
 
 
Error_Handler:
    Call ErrorMessage(Err.Number, Err.Description, "Form_frmRequestProcessor: VerifyFields")
    Resume Exit_Procedure
    Resume
 
 
End Function

You can ignore the error trapping at the end. I called this from a button's OnClick event like you are planning on doing.

If VerifyFields Then
    'Save your Record here
Else
    MsgBox "Please fill in the highlighted fields."
End If

With this code, the highlighted field would remain highlighted until the save button was clicked again, but you could call the VerifyFields function in each of the required controls' AfterUpdate events and it would keep the highlights accurate.

[zmbd]

AB may have a non-vba solution:
Validation Rules You can use a validation text to relate the information requirements to the user. The user then will have the option to [Esc] out of the record or enter the data.
As to do this at the table or the form level, that depends; however, I tend to do this at the table level and trap for errors if needed.

[zephyr223]

thanks Seth and zmbd for the quick replies. I think now i have a better understanding of what path to take.