Microsoft KB Archive/96973

{|
 * width="100%"|

ACC1x: Validating Data with Values from Another Table

 * }

Q96973

-

The information in this article applies to:


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

Example 1: Using the DCount Function
In the following example, the user can enter into &quot;Table1&quot; only those values that exist in the &quot;Field&quot; field of &quot;Table2.&quot;

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(&quot;[Field]&quot;,&quot;Table2&quot;,&quot;[Field]='&quot; &_                         [Field in Table1] & &quot;'&quot;) > 0

In the following example, the user can enter into &quot;Table1&quot; only those values that do not exist in the &quot;Field&quot; field of &quot;Table2.&quot;

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

Example 2: Using the DLookup Function
In the following example, the user can enter into &quot;Table1&quot; only those values that exist in the &quot;Field&quot; field of &quot;Table2.&quot;

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

In the following example, the user can enter into &quot;Table1&quot; only those values that do not exist in the &quot;Field&quot; field of &quot;Table2.&quot;

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

Replace &quot;Field&quot; with the appropriate field name in your database (be sure to preserve the quotation marks and square brackets). Replace &quot;Table2&quot; with the name of the validation table in your database and replace &quot;Table1&quot; with the name of the table in which you are entering data.

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.