Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

Data Validation Access
Data Validation Visual Basic






Visual Basic Tutorials:
Access-Google Earth
Age Calculation
Change To Proper Case
Email via Gmail #1
Send Gmail Email #2
Inactivity Logout Code
Outlook Email
Read Email Access
Email Attachment
Send Outlook Email
Running Sum
Concatenate Records
Stock Quotes
CreateQueryDef
Find Database Path
Detail-Master Update
Data Field 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



Are you just starting out on your quest for a database? Need some advice or someone to bounce ideas off of? We are here and will be happy to chat with you on the phone and discuss options to your situation. Visit our contact page and give us a call.

Did you know that most template databases often do not do exactly what you need done? We can create a custom template that fits your needs exactly for a small to medium Access database at a reduced cost. See our contact page and call Joseph Dean for more info.


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










Blue Claw Database Design Downloadable Tutorial:
How To Open Email Using SendObject Access Download  








A Blue Claw Software Design Template:

Student Grant Program Administration Software
 










A Blue Claw Database Design Article:

ODBC Links To Other DB Systems
 

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


Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, 2010, 2013 & 2016