473,320 Members | 1,744 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Cascading ComboBoxes

...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.
Nov 19 '09 #1
16 2370
NeoPa
32,556 Expert Mod 16PB
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.
Nov 19 '09 #2
NeoPa
32,556 Expert Mod 16PB
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.
Nov 19 '09 #3
OldBirdman
675 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. ......
  2. Lake View    | Iowa
  3. Lakeview     | Michigan
  4. Lakeview     | Oregon
  5. ......
  6. Oakland      | California
  7. Oakland      | Michigan
  8. Oakland      | New Jersey
  9. Oakland City | Indiana
  10. ......
Once the decisions about how this should perform are made, the code is very similar to the normal cascading comboboxes.
Nov 20 '09 #4
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.
Nov 20 '09 #5
OldBirdman
675 512MB
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.
Nov 20 '09 #6
topher23
234 Expert 100+
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.
Nov 20 '09 #7
ajalwaysus
266 Expert 100+
@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
Nov 20 '09 #8
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.
Nov 20 '09 #9
topher23
234 Expert 100+
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.
Nov 20 '09 #10
ajalwaysus
266 Expert 100+
@topher23
Also, more proof of that, you should look at the algorithms page. Those guys are so over my head, but it is just more proof of the kinds of smarts this site has attracted =) Amazing!

-AJ
Nov 20 '09 #11
OldBirdman
675 512MB
...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 don't know why you assume we can't understand your actual problem. I'm not sure that you do either, or your 'example scenario' would have mirrored the real problem, which it apparently did not.

I don't see where anybody asked you "why?" True, nobody wrote any code, but that is because we needed to see where you were going with this, and have table names and relationships, etc.

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.
I have a database with a heirarchy based on the taxonomy of Linnaeus, which contains the (Latin/scientific) names of all the birds and some of the other animals. Very few users know the higher levels. Your problem is not unique.
Nov 20 '09 #12
Please stop obsessing over the faults in my post. I didn't post my exact field names. It doesn't matter. I clearly explained that I had a hierarchal order but I wanted the comboboxes to populate each other no matter what order they were selected. I said I appreciate your comments and have moved on, but you still seem to want to critique my methods.
Nov 20 '09 #13
topher23
234 Expert 100+
As Experts or helpers of any other sort, we expect that people asking for help will be 100% forthcoming. Those who are not cause a lot of frustration. We are simply expressing that you have caused frustration in not being completely up-front about your problem, instead couching it in "something you people would understand." Such an attitude borders on arrogance, and is really annoying to people who are just trying to help.

If you want help, we're happy to help. If you want to complain about the way people are treating you, please think about the way you're treating them with that sort of attitude.
Nov 20 '09 #14
I'm sorry if you took my tone as arrogance, but I didn't mean it that way. I am certainly not complaining about the way anybody treated me. I simply just believe that too much of everyone's time was used discussing topics that don't matter. That is why I'm trying to end this conversation, but I sincerely don't want any of you to think I was trying to be arrogant or rude. I honestly do believe that you all are doing a great service and am very appreciative. I'm sorry it is very hard to convey tone and attitude when writing short posts online. I just tried to save some time by using a simple scenario and explaining my situation in English rather than code. I never said it is "something you people would(n't) understand" and was polite as can be. If anybody took this as offensive, then I apologize, but try to take these things with a grain of salt.
Nov 20 '09 #15
topher23
234 Expert 100+
Your contrition is appreciated. Now, before we let you pass, we desire... a shrubbery!
Nov 20 '09 #16
NeoPa
32,556 Expert Mod 16PB
Tarheel,

May I just say that, although you possibly hit a tender spot there with how you replied on the one occasion, I congratulate you on the tone of your responses since then.

Our experts do sometimes have to deal with the downright obnoxious behaviour of some posters (yours was far from that), so it is certainly understandable to me that they may sometimes unload a little. I think you may have been the unlucky straw on the camel's back on this occasion, but I'm glad to say you handled it perfectly. Very few people realise how complicated it is trying to answer questions via such an inflexible medium such as a forum site. It's frequently frustrating to find questions that could be answered if only the question were phrased more clearly (and I appreciate you put time & effort into making it as clear as you could. I'm just explaining from the perspective of the other side).

Anyway, I'm also pleased that you asked your question just for the insight I was able to get from OB's post #4. It's not something I use a lot myself, but the points he made there seemed very important and a lesson for all (at least many) of us.

Now, have you sorted out that shrubbery yet :D
Nov 21 '09 #17

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: JMosey | last post by:
Not sure if this has been covered ( a google search came up pretty bare). I have a site that: - has multi-level cascading menus - floats center of the browser window - Will have fairly heavy...
4
by: Kathy | last post by:
In my custom menu one menu item opens a form to add new Makes to TblMakes and another menu item opens a form to add new Models to TblModels. I have a form that has a combobox for Makes and a...
0
by: cognoscento | last post by:
I'm currently putting together a database for my work (not an expert by any stretch, so muddling through as best as I can... you know the story...) and I could use some advice and hand-holding ...
4
by: pmcguire | last post by:
I have 2 bound ComboBoxes. I want the datasource of the second to be limited by the selection made in the first. I can do this by responding to the SelectionIndexChanged event on the first, but...
6
by: visionstate | last post by:
Hi there, I am building a database that requires cascading lists on a form. I currently have (I may be adding more later) 3 combo boxes on my form - Department, Surname and Forename. The user...
19
by: Amanduh | last post by:
Hi again, brilliant developers. I'm having serious issues with cascading comboboxes. I had it working perfectly before, but then was asked to add an additional variable and everything went haywire...
4
klarae99
by: klarae99 | last post by:
Hello, I am working on an Access 2003 Database. The tables that pertain to this issue are tblOrg, tblState, tblCity, and tblZip. I have posted the table structure with only the pertinant fields...
12
by: azalea45 | last post by:
Hi all I am a newbie when it comes to access. My company requires that all Databases run on the company SQL back-end as a result i have a Access project that connects to the server. I have two...
16
by: AFSSkier | last post by:
I have several cascading ComboBoxes & would like to close Cbo2 & open Cbo1 when there is an error. These CboBoxes are on an Excel spreadsheet, not a UserForm. I have the following code, if the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.