Home  Fees/Services  Microsoft Access Templates  Tutorials  Tutorial Download  Articles  Search  Contact  Privacy  Links
Data Validation Access Access Visual Basic - VBA Macros
Data Validation Access
Data Validation Visual Basic
 





Visual Basic Tutorials:
Access-Google Earth
Age Calculation
Change To Proper Case
Email via Gmail
Outlook Email
Read Email Access
Email Attachment
Send Outlook Email
Running Sum
Concatenate Records
Stock Quotes
CreateQueryDef
Find Database Path
Detail-Master Update
Data Validation
Field Value New-Old
FindFirst
Access Version
Global Variable Parameter
Global Variables
Active Labels
Files List Box
Mail Merge
Quick Sort
Recordset Filters
Reference Form Field
Select Case
Access Transactions


Visual Basic Function Examples


 
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







Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013


Blue Claw Database Design Downloadable Tutorial:
Programming MS Access Security Alternative  








A Blue Claw Database Design Template:

Barcode-Device Inventory Database
 










A Blue Claw Database Design Article:

Repair Corrupted Access Table
 


Contact Information

Copyright 2000-2014 Blue Claw Database Design