Cascading combo box problem - please help (1 Viewer)

[choaspes]

Hi All

I'm still fairly new to Access, and am attempting to solve a problem that I seem incapable of describing to google in such a way that it returns me a solution, I would be very grateful if one of you lovely people can help.

I want to create a cascading combo box - so far so simple, but in all the tutorials I can find the options in the second combo box are exclusive to the first e.g. combo box 1 selects "state", combo box 2 then displays "city". Each city can only be in one state.

In my scenario each city is in several different states - and so far I'm failing to come up with any sort of strategy as to how to deal with this. I tried giving each state a "yes/no" formatted field in the "city" table, figuring if I could use the first combo box to point access to the right field in the "city" table I could filter those records by "WHERE [fieldnominatedbycombobox1] =True" - but I don't know how to make a one combo box determine which field a subsequent combo box filters by. Am I going about this in totally the wrong way?

 

[John Big Booty]

Are the cities the same even if they appear in more than one state or are they different cities that share a common name but nothing else?

 

[choaspes]

Perhaps my choosing "state" and "city" to illustrate my dilemma is actually making it harder to visualise what I'm trying to do. Let's say "states" overlap like a venn diagram and "cities" are single, unique entities that only occur once on the map. A "city" could be located in just one "state", but where "states" overlap - that "city" will be in more than one "state".

 

[choaspes]

I was hoping that I could get the second combo box to filter by a field selected by a number produced by the first combo box, but the only way I can think of is by using the syntax WHERE [table or query]![column].(number produced by combo box one)=True....and that syntax doesn't seem to work when referring to tables or queries, it's always an "undefined function" error.

 

[John Big Booty]

What I would be looking at in this case would be a junction table, that indirectly links your "States" and "Cities", this will then make a Cascading Combo Box schema workable.

 

[choaspes]

Thanks John. I'm sorry but I've fallen at the first hurdle here - I am unable to follow the instructions in that tutorial. The "field size" for the primary key fields in both of the tables I want to join is "long integer", and Access refuses to allow me either to enter "long integer" as the field size for those two primary key fields in the junction table, or to change "long integer" to a number in my exisiting tables. This leaves me unable to "enforce referential integrity" when I join my tables to the junction table.

 

[choaspes]

I can get it working by starting from scratch in design view and writing both of the tables all over again, but having done this - what now? I am pretty new to this and I can't see the steps in between "you have created a junction table" and "you have a solution to your problem".

 

[jdraw]

I see John has things under control.

I'm not sure I understand your issue so I built a form with 2 combos -
combo0 for State and combo2 for City.

In the attached jpg you will see the form with combos and beside each combo is the rowsource for that combo.

I have shown the link to a free zip city state lat/long etc file. Since my table also includes zip code, and there can be several zips for a city, I am using DISTINCT in the SQL to hide duplicates.

In this form you select State, and then City - the cities are confined to the State selected.

The second jpg shows the States which have a city ODESSA.

Hope it's useful to you.

 

[John Big Booty]

You will need to create a query that uses your three tables.

It will need to be constructed so that it selects cities that are linked to states through your junction table. As it's criteria you will use the following;

[Forms]![YourFormName]![YourMasterComboName]

you will need the following code in the Master Combo's After Update event as well as the form's On Current event;

Me.YourCasacadedComboName.Requery

At a very basic level your query will look something like;

SELECT TBL_State.StateID, TBL_Cities.CityID
FROM (TBL_State INNER JOIN TBL_StateCity ON TBL_State.StateID = TBL_StateCity.StateID) INNER JOIN TBL_Cities ON TBL_StateCity.CityID = TBL_Cities.CityID
WHERE (((TBL_State.StateID)=[forms]![YourFormName]![YourMasterComboName]));

 

[choaspes]

OK. Thanks very much both of you.

I am much, much more confused than I was to start with, but that says a lot more about me than it does about you, and I very much appreciate that you've taken the time to try and help me.

I don't have enough proficiency with Access to grasp why I'm performing the steps you advise or precisely what they mean, so it's rather like learning to repeat the words of a Latin mass without being able to construct a basic prayer of my own - but I will persevere. Thank you again, I'm sure I'll be able to work some of this out if I stare at it for long enough.

 

[choaspes]

Primarily what I don't grasp is how to go about linking each "city" to several different states on my junction table.

Say I've got 3 cities and 2 states. One state contains all 3 cities and the other state contains just 2, in which fields in which tables do I explain that to my database?

 

[John Big Booty]

I probably should have mentioned that the query we made in my previous post will form the Row Source of your Cascaded Combo

As to how to maintain your junction table, perhaps the attached sample will help you understand.

 

[choaspes]

Yes that's very helpful indeed, I think I'm starting to get the idea.

Very kind of you, thanks again!

 

[jdraw]

There are 2 free videos here
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html