preventing duplicate choices

[redsteve11]

I have 5 comboboxes all with the same query as the rowsource.

The user fills in all the comboboxes, but I want to prevent them from choosing the same list option in multiple comboboxes.

I tried setting criteria in the query to something like this...
<>[Forms]![frm_FORMNAME]![Combo1]
then tried messing with the ReQuery event in the form, but couldn't get it to work. (I tried it in the AfterUpdate event in all the comboboxes)

If I can't get that option to work, is it easy if the user hits a command button and a message box pops up indicating that there are at least 2 comboboxes with similar values?

Thanks

 

[randy700]

How about this...

make the control source of combo1...

SELECT SomeField FROM SomeTable WHERE SomeField <> combo2 AND SomeField <> combo3 AND SomeField <> combo4 AND SomeField <> combo5

make similar control sources for the other 4 combo boxes.

In the after_update event for each combo box...
me.combo1.requery
me.combo2.requery
etc

 

[mndrlion]

If the objective is to give the user the ability to make a multiple selection, then using a List Box with Multi Select set to Simple or Extend would make things a lot simpler.

 

[OhioSteve]

I can't tell from your explanation exactly what you want to do. However, let's assume that you have a table with twenty cities in it. You want the user to choose five cities from that list. Instead of using five pulldowns, you could use a different approach. You could add a yes/no field to the table. Then you could put a subform on the form. The subform would allow the user to change the yes/no field. Then you could run the query using the selected cities.

 

[redsteve11]

randy700, I see what your saying, but what do I substitue "SomeField" with?

 

[randy700]

SomeField would be the name of the field in the table that corresponds to the list you want to create....

However, I suggest you give mndrlion's approach some strong consideration.

 

[OhioSteve]

Yeah, Mndrlion's concept is similar to mine. We are recommending a list-type interface. The user can make multiple choices, but can't choose the same thing twice.