Microsoft KB Archive/209089

= ACC2000: Field and Record Validation Rule Enforced at Table Level =

Article ID: 209089

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209089



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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
Microsoft Access 2000 enforces field and record validation rules defined at the table level, regardless of the data input or editing method.



MORE INFORMATION
In Microsoft Access, the validation rules 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.

The expression for a field validation rule cannot refer to other fields or controls, 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 create a field validation rule that includes a reference to another field, you receive the following error message:

Invalid SQL syntax - cannot use multiple columns in a column level CHECK constraint.

If you use a function in your validation rule, you receive the following error message:

Unknown function ' ' in validation expression or default value on '. '.

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 is 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. Yes               Yes               When editing data in a form, form validation rules are enforced first. If the value passes form validation, the field validation rule is enforced. For data to                                       be accepted, it must pass both rules.

