Prevent duplicate values in two unrelated tables

[scousethemoose]

Hi i have 2 tables, tblReqPAT and tblAppPAT they both contain the same fields

tblReqPAT:
PATno and AssetID

tblAppPAT
PATno and ApplainaceDescription

The two tables need to be unrelated.

Is it possible when adding a record to either table to check if the value of the field PATno already exists in the other table.. If it does exist prevent saving of the record.

I was thinking of using DLOOKUP to check the tables and if it returns an empty string then saving can go ahead but if a matching record is found then saving is cancelled.

Any suggestion would be great

Thanks in advance

 

[wemeier]

DLookup is the best way to do this. Put the lookup on the BeforeUpdate event and, if you find a match in the other table do a CancelEvent.