Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: Field and Record Validation Rule Enforced at Table Level

ACC2000: Field and Record Validation Rule Enforced at Table Level

View products that this article applies to.

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).

↑ Back to the top

Summary

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

↑ Back to the top

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 '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 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.

↑ Back to the top

References

For more information about validation rules, click Microsoft Access Help on the Help menu, type validating data in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

↑ Back to the top

Keywords: KB209089, kbusage, kbinfo, kbhowto, kberrmsg

↑ Back to the top

Article Info
Article ID : 209089
Revision : 2
Created on : 6/28/2004
Published on : 6/28/2004
Exists online : False
Views : 275