I use Access 2010.
I have two tables and one form. The main record source for the form points to table1_Main, however the drop down menu I have in the form is looking at another table called table2_Contacts. I want to make sure then when a user adds a new record and selects a ContactID from the drop down menu that it can only be added to the database once. If someone tries adding a new record and selects the same ContactID it will inform them that this ContactID has already been used already and can't be used again.
Hope this makes sense.