Microsoft KB Archive/113874

= ACC: Field and Record Validation Rule Enforced at Engine Level =

Article ID: 113874

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q113874



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
All versions of Microsoft Access (except Microsoft Access 1.x) enforce field and record validation rules defined at the table level, regardless of the data input or editing method.



MORE INFORMATION
In Microsoft Access version 1.x, field validation defined at the table level is not enforced by the database engine. The validation rule and validation text defined in table design are used as the defaults for the ValidationRule and ValidationText properties in forms. There is no record validation in Microsoft Access 1.x.

In all other versions of Microsoft Access, the validation rule for both the field and table record are enforced at the engine level. The validation rules are enforced whenever you add or edit data, whether through a table's datasheet, a form based on the table, an append query, an update query, Visual Basic or Access Basic code, or by importing data from another table.

If you have converted a database from Microsoft Access 1.x to a later version of Microsoft Access, you can test to see if the data in the table meets the validation rules with the Test Validation Rules command, available by clicking the right mouse button on the title bar of the Table Design window.

The expression for a field validation rule cannot refer to fields or controls in a field validation rule, user-defined functions, Microsoft Access domain functions, aggregate functions, or the CurrentUser or Eval functions. The expression for a record validation rule cannot contain user-defined functions, Microsoft Access domain functions, aggregate functions, or references to forms, queries, or tables. Expressions can include only references to fields in the table. If you try to do field level validation on a table including any of the above options you will encounter the following error message:

In Microsoft Access 97:

The database engine does not recognize either the field '' in a validation expression, or the default value in the table ''.

In Microsoft Access 2.0 and 7.0:

Unknown or invalid reference '' in validation expression or default value in table ''.

If you use a function in your validation rule you will receive this error message:

Unknown function '' in validation expression or default value on '<[Table Name]>.[]'

The table below identifies the interactions between the field and form validation rules:

  Field Validation Rule Defined      Form Validation (Table Level)     Rule Defined      Behavior --  No                 No                No validation is performed at any level.

Yes               No                Validation will be enforced regardless of method used to insert data.

No                Yes               Validation enforced at form level only. User can bypass the form validation rule when editing data outside the form. This is the same as Microsoft Access 1.x behavior.

Yes               Yes               When editing data in a form, form validation rules will be enforced first. If the value passes form validation, then the field validation rule is enforced. In                                       order for data to be accepted, it                                        must pass both rules.

