Enforcing no duplicates in multiple textbox controls

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Thelma Lubkin - 30 Nov 2005 15:03 GMT

I have a form w/ 11 identical text boxes in each of which user can
enter a number between 1 and 11. Each defaults to 0, which I use to
flag empty. Each has the 1 to 11 restriction as its validation rule,
but I also need to enforce no duplicates among them. I haven't been
able to figure out how to do this in the validation rule, so I have a
function that gets as a parameter a number to identify which textbox
is calling it that handles that for me. But that means that I have 11
lostfocus events in 11 separate subroutines that do nothing but call
the same function with their own identifying numbers. Is there a less
clumsy way to do this?
thanks, --thelma

Reply to this Message

Allen Browne - 30 Nov 2005 15:25 GMT

Yes, the less clumsy way would be to create a related table.
You can then select any number of rows in that table, related to the one in
your original table, and easily ensure no duplicate.

For example, if these 11 are colors, and you don't allow Blue to be picked
twice for the same client, the related table would have fields:
ClientID which client
ColorID which color
You select both fields together, and make them primary key in this related
table. Problem solved.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a form w/ 11 identical text boxes in each of which user can
> enter a number between 1 and 11. Each defaults to 0, which I use to
[quoted text clipped - 7 lines]
> clumsy way to do this?
> thanks, --thelma

Reply to this Message