Knowledge Base |

# 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 '<Field Name>' in a validation expression, or the default value in the table '<Table Name>'.
In Microsoft Access 2.0 and 7.0:
Unknown or invalid reference '<Field Name>' in validation expression or default value in table '<Table Name>'.
If you use a function in your validation rule you will receive this error message:
Unknown function '<Function Name>' in validation expression or default value on '<[Table Name]>.[<Field 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.

## REFERENCES

For more information about validation rules, search the Help Index for "validating data."

Keywords: kberrmsg kbinfo kbusage KB113874