Data Validation Examples - Form Data Validation VBA

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 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