Question Multiple Combo Boxes from same list

Soton

Registered User.
Local time
Today, 19:03
Joined
Mar 17, 2008
Messages
17
I am trying to create a form with a number of Combo Boxes which look at the same 'single-field' list. The list might contain up to say 20 items and there could be 5 combo boxes.

The selection criteria are:

If an item is chosen in cbox1 (say Item3), it should not then appear in the list shown for cbox2.

If an item is then chosen in cbox2 (say Item5), then neither Item3 nor Item5 should appear in the list for cbox3.

And so on for the other boxes.

I have seen threads which filter another field depending on what is chosen ia a combo box but have not found one which only has one field where the number of items available to choose reduces as each combo box is filled. An analogy would be a shopping list of 20 items but you are only choosing five unique items from the list in sequence.

I could probably create filters for what's not in (Cbox1), then whats not in (cbox1 and cbox2), then whats not in (cbox1 and cbox2 and cbox3) and so on but after five or six boxes it gets a bit cumbersome.

I was wondering if there was a more elegant solution available which would allow for a larger number of cboxes.
 
What you're trying to do for multiple combo boxes isn't easily achievable, so maybe you want to rethink ;) The term you're referring to is cascading combo boxes.

Here's a link that you would find useful:
http://baldyweb.com/CascadingCombo.htm
 
Last edited:
I had looked at that link and it suggests it is for tables with more than one field where each 'selection' works on the next field. I'm not sure I am looking for Cascading ComboBoxes but multiple boxes looking at a list based on a single field. If combo boxes are not the answer, do you have any other suggestions.:(
 
The combo boxes are bound correct? You would need to build a where string and cocatenate it to the recordsource of combo 2 using NOT IN (1, 2, 3) for example.

Code:
"SELECT * WHERE [ID] Not In (1, 2, 3)"

Try it in the query first before thinking about coding the SQL.
 
Sorry if I'm being thick but, in "SELECT * WHERE [ID] Not In (1, 2, 3)", are '1', '2' and '3' the names of the Combo boxes. Would the SQL string be something like "SELECT * FROM tblSourceData WHERE [ID] Not In (1, 2, 3)" with [ID] being the 'list' field.
 
Thanks vbaInet. Just tried it on a mock-up and it works a treat. Now just need to incorporate process into main database.:)
 

Users who are viewing this thread

Back
Top Bottom