Form Data Validation VB Tutorial
Form 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 validation for
credit card numbers. A
programmer's look at 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
