About validating data programmatically

You can verify that data is entered correctly into a control on a form or into a field in a table by setting a property for the control, or by setting record or field validation rules in the underlying table. Macros and event procedures provide additional power and flexibility for more complex validation.

Use a macro or an event procedure for data validation if:

• Your validation rule involves conditions for more than one value on the form. For example, you might want to verify that any two of three fields are filled in before saving a record.
• You want to display different error messages for different types of errors in the field. For example, if the value entered is greater than the acceptable range, you can display one message, and if it's less than the acceptable range, you can display a different message.
• You want the user to be able to override your validation rule. In this case, you can display a warning message asking the user to confirm the data.
• The validation involves references to controls on other forms or contains a function.
• You have a validation rule that you can use for more than one form, and you want the convenience of defining the rule once and then referring to it in each form.

Using a macro

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 for many countries can't rely on a simple validation rule to ensure that a postal code is entered correctly.

You can, however, create a macro that checks which country/region is entered in the Country control and then checks that the value entered in the PostalCode control has the right characteristics for that country/region.

First, create a macro that displays different messages depending on the value of the Country control and the length of the entry in the PostalCode control.

If the country is France and the postal code is not five characters long ...

... the macro displays this message.

Then, open the form that contains the controls in Design view, and set the BeforeUpdate event property of the form to the name of the macro. You use the form property rather than the control property 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 macro after you finish entering the new record, but before it saves the record in the Suppliers table. If either condition in the macro is met, Access displays the appropriate message and doesn't save the record in the Suppliers table.

Using an event procedure

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

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.

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.

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

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.