Test for duplicate records

[smalty]

I have a table that contains as well as other fields, two fields which are populated from the same drop down list ie I have a list of surnames and in field 1 the user selects a surname then in field 2 the user selects another surname from the same list ending up with perhaps
SMITH JONES
At sometime in the future the user might be requested to enter
SMITH JONES or JONES SMITH and in either case I require a message to tell the user that this combination is no longer possible. I know it is possible to not allow the combination if they are entered in the same order by using 'key fields' but I don't know how to automatically check for the reverse combination.
Any ideas/suggestions would be much appreciated

 

[lespaul]

Have you created a unique index on the fields?

Unique index on Surname1 & Surname2
Unique index on Surname2 & Surname1

I'm not sure if you'll need two, but that should do what you want, prevent users from entering the same combination twice...

(just a wild guess! hope it works!!)

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[PHV]

tell the user that this combination is no longer possible
Have a look at the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[smalty]

Thank you for the quick reply. I have never used indexes and an not sure if what I have now done is correct or not but what I have done doesn't work but that is not to say that what you are suggesting is incorrect just probably my inexperience with indexes.
This is what I have in the indexes box

IndexName FieldName Sort
PK PrimaryKey BowlerName1 Asc
PK BowlerName2 Asc
ResultsBowler BowlerName2 Asc
BowlerName1 Asc

and for both indexes the Unique value is set to Yes

Please advise if this is totally wrong or is it correct and isn't the solution I was looking for

Thanks again for your assistance

Smalty