How to check for duplicate values in a subform

[Aaron5714]

I am working on a database to manage contract documents. Here is a sample version: http://dl.dropbox.com/u/65832352/Exa...Database.accdb

If you click the "New Contract" button on the main form, the contract detail form will load. This form has two subforms for adding locations and accounts to the contract. I am trying to figure out the best way to prevent users from adding the same location or account more than once on each subform. Ideally I would like to have a proceedure run when the "Save and Close" button is clicked that would check for this condition and alert the user if it found duplicate accounts or locations on the subforms.

I am not quite sure how to do this. I was thinking it might be possible with DLookup or DCount statements, or maybe by reading the tables that the subforms are bound to, for example "tblContractAccounts", and trying to identify if there are records with the same combination of "ContractID" and "AccountID" values.

If anyone could point me in the right direction it would be much appreciated.

[orange]

Research " compound unique index" or "multi field unique index".

You can do what you want by setting a unique index on the fields involved.

See Bob Larson's sample at http://www.btabdevelopment.com/ts/mfi

[Aaron5714]

Thanks orange! I didn't that you could create an index with multiple fields. That solved my problem.

[orange]

Glad to help.