ACC1x: Validating Data with Values from Another Table

• Microsoft Access versions 1.0, 1.1

SUMMARY

The text below presents two examples that demonstrate setting the ValidationRule property to use values in a different table. You must use either the DCount() or DLookup() function to do this.

You can use a validation expression to compare the value entered by the user with a value in one or more other tables. You can create a validation rule to accept only values that exist in a second table or to reject values that exist in a second table.

MORE INFORMATION

Example 1: Using the DCount() Function

In the following example, the user can enter into "Table1" only those values that exist in the "Field" field of "Table2."

NOTE: In the following examples, an underscore (_) is used as a line- continuation character. Remove the underscores when re-creating these examples.

   Table: Table1
   ---------------------------------------------------------
   Field Name: Field Name
   Data Type:  Text
   Validation Rule: DCount("[Field]","Table2","[Field]='" &_
                          [Field in Table1] & "'") > 0

   Table: Table1
   ---------------------------------------------------------
   Field Name: Field Name
   Data Type:  Text
   Validation Rule: DCount("[Field]","Table2","[Field]='" &_
                          [Field in Table1] & "'") = 0

Example 2: Using the DLookup() Function

In the following example, the user can enter into "Table1" only those values that exist in the "Field" field of "Table2."

   Table: Table1
   ----------------------------------------------------------
   Field Name: Field Name
   Data Type:  Text
   Validation Rule: DLookup("[Field]","Table2","[Field]='" &_
                         [Field in Table1] & "'")Is Not Null

   Table: Table1
   ----------------------------------------------------------
   Field Name: Field Name
   Data Type:  Text
   Validation Rule: DLookup("[Field]","Table2","[Field]='" &_
                         [Field in Table1] & "'")Is Null

DCount() searches through the specified field in the validation table and counts all records where the field name is equal to the value entered in the table. If the function returns a value greater than zero, the function found one or more matching records.

REFERENCES