Cascading ComboBoxes

[Tarheel]

...You're fast! I appreciate that.

Thanks! I look forward to your explanation.

One last problem I have on my form is getting all of my comboboxes to update each other. I know how to update them as a cascade (i.e. Region updates Country which updates State which updates City) but I would like them to be interdependent (i.e. if I select a City, I want the Region, Country, and State comboboxes to populate with the corresponding data. Hoping to get this effect when I select any of these given boxes.

For ease of understanding, let's assume these are all of my comboboxes which correspond to a field of the same name, sans "com":
comRegion
comCountry
comState
comCity

Thanks again. You're a pro.

[NeoPa]

Tarheel,

Your earlier post just scraped by the thread-hijacking rule. This one blew it wide open. It is not your thread to change the subject in. That requires a new thread.

I've moved it for you. The thread this was split from is Checkbox form to allow user to select query output fields.

[NeoPa]

As for your question, there are a couple of articles based around this subject. I think the better one for you would be Cascading Combo/List Boxes, but the other (Example Filtering on a Form) may also be worth a look.

[OldBirdman]

This problem, as presented, has not been thought through.

Cascading ComboBoxes are a straight-forward concept. A user selects from the first (upper) box, and the selection in the next box is limited by this choice. Here, however, the user could select from a lower box first. So consider ...
1) If a city has already been selected, say USA->California->San Francisco, then Miami or New Orleans will not show, and will not be available for selection.
2) If a city is to be selected first, and there are more than one with that name, how is user to select. Google "Lakeview" or "Oakland" to realize this is not workable.

But there is a benefit to be able to choose California->San Francisco without selecting Americas->United States first. In my hobby of birding, I also have County after state, and Location after City, making the desire to shortcut the entire tree even more desirable.

I also have the problem that many geographic names in California start with "Santa ..." or "San ..." so typing in the combobox may not produce a unique result for many characters "Santa Clara" and "Santa Cruz" become unique on the 8th character.

Some suggestions that worked for me.
A) Have a 'Backfill' command button to fill in the cascade. This eliminates the problems in 1) above.
B) Have a 'Clear Down' command button at each level.
C) Limit the number of levels that can be skipped. I have found 3 is comfortable. Set Enabled=False for levels below this.
D1) Dynamically change the number of columns shown to display the missing levels.
D2) Sort by the lowest column level.

For example, user selects 'United States'. They skip the state, and display the list of cities. One level was skipped, so the City combobox will display 2 columns:

......
Lake View    | Iowa
Lakeview     | Michigan
Lakeview     | Oregon
......
Oakland      | California
Oakland      | Michigan
Oakland      | New Jersey
Oakland City | Indiana
......

Once the decisions about how this should perform are made, the code is very similar to the normal cascading comboboxes.

[Tarheel]

Hi OldBirdman,

Thanks so much for your comments and ideas. You make very good points about logical order and duplicate values. However, I was just using the Region, Country, State, City scenario as an example to make my point a bit clearer. In my database and on my form it is more of a common occurrence for the user to know the last value but not the preceding three (or any combination of each). This would be like the user not knowing the country or state, so they enter the city and the other boxes populate based on the city entry. This scenario is more likely in my actual case than this example.

Also, it may be necessary for the sake of querying to have interdependent boxes (pertaining to your Lakeview and Oakland problem: The user may want to know how many or which states have a city named Lakeview or Oakland).

I can write the logical statement for this in English, but cannot find the right combination of SQL and Event code.

If I cannot reach a solution to this, I will probably settle for one of your before-mentioned methods which to lend some great help.

I know all of this sounds convoluted and it is my fault for making you assume that example was my actual case.

Thanks again for your comments. They are very valid, but the main goal of this form is to make it as intuitive and user friendly as possible. Any follow-up or breakthroughs are very welcome.

[OldBirdman]

You seem to be writing code (SQL statements and event procedures) before you have a design to work from. As a believer in planning before writing code, I'm glad you found my comments useful. I too like extreme 'User Friendly' interfaces. But your coding seems premature.
Perhaps if you gave the actual problem, comments could be more specific to your needs. I think you are attempting to choose from a list of items that are better recognized than remembered. Perhaps there are duplicates, or near duplicates. Perhaps the items are in a natural language and, like city names, tend to look similar, sound similar, and be unevenly distributed within the alphabet.
In your comboboxes, are you selecting to a single record, or can the final choice be multiple records? How will these be displayed? What is to be done with them once displayed? Such questions may be necessary to find a 'user friendly' design.

[topher23]

Tarheel,

If you're going to provide an example, the example needs to be what you're actually working on and not completely contrived. You said

let's assume these are all of my comboboxes which correspond to a field of the same name, sans "com":
comRegion
comCountry
comState
comCity

which, obviously, leads anyone looking over this post to help out to assume those are your actual fields and combo boxes. If they are not, then we can assume you don't want an answer to your actual problem, otherwise you'd post it.

P.S. I don't mean to be an arse here, but it always bothers me when someone doesn't ask the question they actually want answered, whether it be on the internet or in real life. One of my pet peeves I suppose.

[ajalwaysus]

@topher23
Also, no offense Tarheel, since I do not know your level of expertise in Access, but it helps to post the actual full information because:
1. You may not have a full understanding of what is relevant and what isn't, yet, which doesn't mean you won't get there. We are here to help you get there. =)
2. You should never skim on information, because you never know when someone may have an "AHA!" moment that will by far help you the most. I have seen it many time on this site.

-AJ

[Tarheel]

A sincere thank you for all of your comments and no offense taken. I see what you mean. I did however feel that my examples were a relevant scenario and posed a simple explanation of my intended goal. I feel like people focused too much on the "why" aspect of my question instead of the "how." I do see where you're coming from with the "AHA!" moment and it is my mistake for not providing my actual scenario but please take my word for it when I say that my DB and fields cover a topic that is not widely known or understood. I did pose the example scenario because I felt this problem was one widely experienced, and once the answer was provided it was going to be an easy fix that I simply didn't think of. I have decided to go a different route with my user interface as a whole. It is great to see that you are all very knowledgeable and so willing to help complete strangers. Thank you all for your time and have a nice day.

[topher23]

Trust me when I say that it doesn't matter what you're building the database for, there is always someone other than you who knows what you're talking about. If not, many of us are willing to research the field in order to better understand the issue.