How do I validate one field in Access to another field in another table?
Posted on 2011-02-21
I am using Access 2010 and am creating an database that has two tables. As an example of what I'm trying to do, the following is a similar scenario:
Table1 is an inventory of parts. One of the fields is labeled Part No.
Table2 is an list of orders. One of the fields is also labeled Part No.
When the sales rep fills out Table2 to create a new order, he is required to fill in the Part No. field. As soon as he tabs to the next field, Access is supposed to validate what he put in the Part No. field. It compares that entry with the list of Part No. in Table 1. If it is valid, it lets the sales rep move on to the next field. If not, it displays a custom error message.
What I have entered in the Validation Rule for the Part No. field of Table 2 is the following:
[Part No]=("[Part No]","[Table1]")
However, it is not working. Does anyone know how to make this happen correctly? Thank you.