ACC1x: Validating Data with Values from Another Table

ID: 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.

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

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


   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

In the following example, the user can enter into "Table1" only those values that do not 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 Null

Replace "Field" with the appropriate field name in your database (be sure to preserve the quotation marks and square brackets). Replace "Table2" with the name of the validation table in your database and replace "Table1" 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.

REFERENCES

Microsoft Access "User's Guide," version 1.0, pages 45 and 652

Keywords : kbusage GnlValid
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbinfo