Form Data Validation VBA – Data Validation Examples

Data Validation: The following Microsoft Access Visual Basic code demonstrates several different forms of field validation.1) Checking if the date entered is after today’s date:

Private Sub Date_Entered_AfterUpdate()
If Me.Date_Entered>date() then ‘ Date_Entered is form field name
msgbox “Please enter a date less than or equal to today’s date.”
Me.Date_Entered.setfocus ‘ set cursor back in the date field
end if
End Sub

2) Perform data validation on the difference between two date fields is greater than 30 days.

Private Sub Date_Entered_AfterUpdate()
‘ perform data validation on date fields
If datediff(‘d’, Me.Date_Received,Me.Date_Entered) > 30 then
msgbox “Warning: The date received is more than 30 days ” & _
“past Date Entered, please verify.”
end if
End Sub

3) Validate data exists in a table. This example demonstrates a highly compressed method of programming, which I hope you would never use (we don’t).

Private Sub Emp_Name_AfterUpdate()

If Nz(DLookup(“Emp_Name”, “Employees”, “Emp_Name='” & _
Me.Emp_Name & “‘”), “zzzz”) <> “zzzz” Then MsgBox & _
“That name already exists in the employee table.”
‘ note that the two VB lines above actually need to be on one line to run the data validation

End Sub

There is a lot to data field validation forcredit card numbers. A programmer’s analysis of the numbers for each type of credit card generates a series of visual basic data validation checks shown below:

Private Sub CC_Exp_Enter()
Select Case Me.CC_Type ‘ combo box user selection field
Case 1 ‘amex
If IsNull(Me.CC_Number) = True Then
MsgBox “You must enter an AMEX number.”

            Me.CC_Number.SetFocus
Exit Sub
End If
If Len(Me.CC_Number) <> 15 Then
MsgBox “The credit card number should have 15 digits.”
Me.CC_Number.SetFocus
Exit Sub
End If
If Left(Me.CC_Number, 1) <> “3” Then
MsgBox “AmEx numbers must start with a 3.”
Me.CC_Number.SetFocus
Exit Sub
End If
Case 3        ‘ visa
If IsNull(Me.CC_Number) = True Then
MsgBox “You must enter an Visa number.”
Me.CC_Number.SetFocus
Exit Sub
End If
If Left(Me.CC_Number, 1) <> “4” Then
MsgBox “Visa card numbers must start with a 4.”
Me.CC_Number.SetFocus
Exit Sub
End If
If Len(Me.CC_Number) <> 16 Then
MsgBox “This card type should have a 16 digit number.”
Me.CC_Number.SetFocus
Exit Sub
End If
Case 2        ‘ M/C
If IsNull(Me.CC_Number) = True Then
MsgBox “You must enter an MC number.”
Me.CC_Number.SetFocus
Exit Sub
End If
If Left(Me.CC_Number, 1) <> “5” Then
MsgBox “MasterCard numbers must start with a 5.”
Me.CC_Number.SetFocus

Exit Sub
End If
If Len(Me.CC_Number) <> 16 Then
MsgBox “This card type should have a 16 digit number.”
Me.CC_Number.SetFocus
Exit Sub
End If
End Select
End Sub

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & SharePoint

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016

Form Field Validation

Form Data Validation VBA – Data Validation Examples