Combo Box changes list based on another

[deeds]

I have 2 combo boxes. I select the first one and all the name choices are
available. I choose JOE, I then move to the 2nd Combo Box and select it, I
want JOE excluded from that list. How can I do this?
Ultimately I want to expand this but for each combo box list I want it to
exclude any names already chosen in other combo boxes. Thanks.

 

[John W. Vinson]

I have 2 combo boxes. I select the first one and all the name choices are
available. I choose JOE, I then move to the 2nd Combo Box and select it, I
want JOE excluded from that list. How can I do this?
Ultimately I want to expand this but for each combo box list I want it to
exclude any names already chosen in other combo boxes. Thanks.

This is sort of the reverse of the typical "cascading combo" (where you might
select a State from the first combo and have the second combo select only
cities in that state), but the technique is similar. You would base the second
combo on a query *EXCLUDING* the names in the first combo, such as

SELECT yadda, yadda, yadda
FROM tablename
WHERE [fieldname] <> Forms!YourformName!FirstCombo
ORDER BY yadda, yadda;

 

[deeds]

That works great. However, how do I "refresh" the query behind the combo
box. When I first go into form it works like it should, however, if I stay
in the form and re-choose the first combo box, the second combo box still has
the same choices as the first pass. Somehow, I need to "re-run" the query
each time I choose from the first combo box. Any ideas? thanks again!

I have 2 combo boxes. I select the first one and all the name choices are
available. I choose JOE, I then move to the 2nd Combo Box and select it, I
want JOE excluded from that list. How can I do this?
Ultimately I want to expand this but for each combo box list I want it to
exclude any names already chosen in other combo boxes. Thanks.

This is sort of the reverse of the typical "cascading combo" (where you might
select a State from the first combo and have the second combo select only
cities in that state), but the technique is similar. You would base the second
combo on a query *EXCLUDING* the names in the first combo, such as

SELECT yadda, yadda, yadda
FROM tablename
WHERE [fieldname] <> Forms!YourformName!FirstCombo
ORDER BY yadda, yadda;

 

[John W. Vinson]

That works great. However, how do I "refresh" the query behind the combo
box. When I first go into form it works like it should, however, if I stay
in the form and re-choose the first combo box, the second combo box still has
the same choices as the first pass. Somehow, I need to "re-run" the query
each time I choose from the first combo box. Any ideas? thanks again!

Oops! Sorry, should have mentioned that step.

Use one line of VBA code in the first combo box's AfterUpdate event:

Private Sub combo1_AfterUpdate()
Me!combo2.Requery
End Sub