Field-Level Validation

A field-level validation rule is enforced directly by Microsoft Jet. It doesn’t matter whether you attempt to update the field through Microsoft Access, a custom Visual Basic program, or an ODBC call from a third-party program such as PowerBuilder.

The validation rule must be an expression that evaluates either to True or False. If the validation rule doesn’t evaluate to True for the data entered in a field, an error occurs and the contents of the ValidationText property of the field are made available to the calling program.

How this message is conveyed to the user depends on the context of the calling program. For example, if the user attempts to enter an invalid value in the Microsoft Access Datasheet view of a table, Microsoft Access beeps, displays the validation text in a message box, and waits for the user to respond. If a Visual Basic or Visual Basic for Applications code routine violates the validation rule, a trappable run-time error occurs.

Because Microsoft Jet is independent of its host applications, the validation rule may not refer to features that are available from a specific application. You can’t use a custom function in the validation rule expression. Nor can you make reference to objects in the user environment, such as to Microsoft Access forms or controls.

It may seem reasonable to have a validation rule expression such as the following:

Between 10 And MyUpperLimit()

However, even if you defined a function called MyUpperLimit within a Microsoft Access module or within your Visual Basic code, the validation rule expression is still not valid.

By default, field validation rules aren’t evaluated until the entire record is updated. However, in many situations you can improve your application’s performance by using DAO code that sets the ValidateOnSet property of a field to True, so that the field’s validation rule is evaluated when the field’s Value property (its data) is set. For more information about using the ValidateOnSet property, search the DAO Help index for “ValidateOnSet property.”