ACC: Field and Record Validation Rule Enforced at Engine Level (113874)

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:

In Microsoft Access 2.0 and 7.0:

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

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.