Preventing Duplicates in a Table

[tcsikany]

I have a table that associates Employees (from the Employee table) with Protocols (from the Protocol table)

I would like to make it so you can't associate the employee to a protocol that has already been made. The table is currently Many to Many

My table columns are:

strDocNum
intEmpID
dtmDateCompleted

Can anyone help?

Tina

[meratigoerr]

How do you fill data into this table? When a protocol is created? One simple method is use a combobox who's record source is query that pulls only the protocols you want to make availble to a specific employee. It's easier to limit the users choices than it will be to try and limit the combination of foreign keys in your table records.

[tcsikany]

Hi. The two fields are look up fields. On the form used for entry, the information in the Employee table is the Parent and a subform for the protocols is the Child form. It filters the list of documents and displays only the ones completed by the employee in a table grid where new records can be added. This is where I'd like to prevent a duplicate entry.

Does this help?

[meratigoerr]

Is there a limited number of protocols? or can the list be infinite? It would be difficult to limit what the user can enter into the datasheet view. You may want to prevent entries directly into the grid view, but instead provide a seperate form for entry where you can control the available choices.

[tcsikany]

The protocols list is infinite. New ones are being added as they are developed.

[meratigoerr]

The protocols list is infinite. New ones are being added as they are developed.

Are the protocols added to the database seperately, before the current operation. What i'm getting at is an unmatched query. that would list all the available protocols that do not match the current list associated with the currently selected employee. That query could be used as a record source for a combo box to be used to match the protocol with the employee. This would essentially elimate duplicate protocols going to the same employee.
Again, this couldn't be done in the datasheet view you're working with, you'd have to create a seperate form for this.