Example of validating data by using an event procedure

In some circumstances, you might want to use validation rules that depend on complex conditions, and then take different actions depending on those conditions. For example, because different countries have different rules for their postal codes, a database that stores postal codes can't rely on a simple validation rule to ensure that a postal code is entered correctly.

You can, however, create an event procedure that checks which country is entered in the Country control and then checks that the value entered in the PostalCode control has the right characteristics for that country.

First, open the form that contains the controls in Design view. Then, add the following Visual Basic code to the form's BeforeUpdate event procedure to display different messages, depending on the value of the Country control and the length of the entry in the PostalCode control.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case Me![Country]
        Case "France", "Italy", "Spain"
            If Len(Me![PostalCode]) <> 5 Then
                MsgBox "PostalCode must be 5 characters."
                Cancel = True
            End If
        Case "Australia", "Singapore"
            If Len(Me![PostalCode]) <> 4 Then
                MsgBox "PostalCode must be 4 characters."
                Cancel = True
            End If
    End Select
End Sub

Note   You add Visual Basic code to the BeforeUpdate event procedure for the form, not the control, because the validation involves two different controls on the form.

Now when you use the form to add a new supplier, Microsoft Access runs the validation procedure after you finish entering the new record, but before it saves the record in the Suppliers table. If either condition in the procedure is met, Access displays the appropriate message and doesn't save the record in the Suppliers table.