Ensuring no duplicates between 2 fields

[johnvog]

Hi all:

I have a table named Contact Links which stores links between contacts.

For example:


John Smith and Mary Smith are Married so the Contact Links table stores this link.

So, the table has the following fields which store the Primary Key from the Contacts Table.
ContactsId
Linked

What I am wanting to do is have an index that will not allow a duplicate link no matter which way the contacts are stored.

Entered as:
ContactId -- John Smith's Id
Linked -- Mary Smith's Id

Do not allow:
ContactID -- Mary Smith's Id
Linked -- John Smith's Id

Any suggestions would be greatly appreciated.

John

[June7]

Would have to do a search of both fields for each ID. Code in BeforeUpdate event of both controls, something like:

If (Not IsNull(DLookup("ContactID", "tablename", "CantactID=" & Me.ContactID)) OR Not IsNull(DLookup("Linked", "tablename", "Linked=" & Me.ContactID))) AND (Not IsNull(DLookup("ConctactID", "tablename", "ContactID=" & Me.Linked)) OR Not IsNull(DLookup("Linked", "tablename", "Linked=" & Me.Linked))) Then
MsgBox "Contact link already exists."
Cancel = True
End If