Hide selected value from combobox

[ezybusy]

Hi,


Let say I have a combo box with the following items: Blue - Green - White - Yellow.

When the user select one item, another combobox is displayed to allow him to choose another item.

Now what I want to do is: when the user select an item, that item should not show anymore when the other combobox appears.
Example: If the user chooses blue the first time, then blue should not show again when the user want to choose another item.

Can someone help me with that?

[rpeare]

So your problem is you have two combo boxes and both are meant to select colors but you don't want it to be possible for the same color to appear in both selections.

You can do this with cascading combo boxes but be aware this can't be a one way check.

For instance, if your user selects BLUE for the first combo box, your second combo box would say GREEN WHITE YELLOW for it's choices.
Let's say the user then selected GREEN
Now the user goes back to the first color selection you would want the choices there to be BLUE WHITE YELLOW (omitting green)

So let's say your table is named tblColors

tblColors
C_ID   C_Desc
1     Blue
2     Green
3     White
4     Yellow

On your table/data entry you have two color combo boxes

C_ID_Primary
C_ID_Secondary

in the row source for your first combo box you would have something like

SELECT C_ID, C_Desc FROM tblColors WHERE C_ID <> [Forms]![MYFORMNAME]![C_ID_Secondary])

The row source for your second combo box would likewise be something like

SELECT C_ID, C_Desc FROM tblColors WHERE C_ID <> [Forms]![MYFORMNAME]![C_ID_Primary])

[Micron]

I interpret the question differently - the value chosen in the first should be removed from the first.

[ezybusy]

YeS. The value chosen in the first should be removed from the first.
How can I achieve that?

[ezybusy]

I am not using 2 differents combobox, it's the same combobox that loads again once a value is selected.

[Micron]

YeS. The value chosen in the first should be removed from the first.
How can I achieve that?

More info is required. Do you mean once used the value should never be available again, or just for the session, and when this form is reopened some other time all should be available again? The first is easy, the second not so much. Where do the combo values come from and where are they going? Are there a lot of choices or just a few?

[ezybusy]

just for the session, and when this form is reopened some other time all should be available again.

Combobox values are from a table.

I have about 20 values.

[orange]

Here is an older video that deals with the concept of selecting something, then removing that item from the next choice.
It deals with listbox but I think you will get some useful ideas from the video.

Good luck.

[Micron]

making the controls into list boxes as suggested (or even using just one) might be much simpler, else I think we're looking at either a way to flag an item as having been used (an extra table field as yes/no) or a temp table that you can remove the item from once selected, but is re-populated with all the values next time the form is open (or if a new record is chosen, as the case may be). In the first case, the flags have to be reset to no once the appropriate action occurs.