How to filter combo box based on multiple fields

[Chief_Up_Yours]

On a form, I have COUNTRY, STATE and CITY fields.
Each field has a corresponding table.
COUNTRY - Country_Abbrev, Country_Name
STATE - Country_Abbrev, State_Abbrev, State_Name


CITY - Country_Abbrev, State_Abbrev, City_Name
The lookup on the COUNTRY field is trivial.
What I want to do is filter the STATE lookup list so that only states in the specified country appear in the list.
Likewise on the CITY field, I want to filter the list of cities so that only cities in the specified country and state appear.
How do I go about building queries to satisfy the STATE and CITY lists?

[June7]

You are saving text Abbrev of country and state into CITY?

You appear to be describing cascading (or dependent) combobox/listbox. Common topic. Suggest you do research.

[Chief_Up_Yours]

@june7 - Yes, as I stated, the CITY table has country and state abbreviations.

I found the following thread:
https://www.accessforums.net/showthread.php?t=76425
Which solved the problem.

[ArviLaanemets]

You first problem is that it looks like you use country and state abbreviations as primary keys for your tables. For countries table, it will be not a problem when you use international abbreviations, but with states there may be problems:
1. The state abbreviations are usually determined by country, and different countries may have same abbreviation used;
2. Not all countries have states, or any equivalent of them. Those countries are regionally divided to some kind of territories with regional management (the naming of them varies, and there may be more than one levels of them.)

So my advice will be:
1. Design your tables like
tblCountries: CountryID, CountryAbbrev, CountryName, RegionType (you'll need also a table, where all region types are defined - State being one of them);
tblRegions: RegionID, CountryID, RegionAbbrev, RegionName (this design takes care of cases, where the same abbreviation is used in different countries, as those regions will have different ID's));
tblCities: CityID, RegionID, CityName (having CountryID here is abundant).

What you mean by "State lookup list"? Are you using lookup fields in your tables? Scrap it! Users must not allowed to change anything in tables directly - they can do this only using forms. And on forms, you use combo boxes to select a country, region, or city, based on queries to get those selections.

[Chief_Up_Yours]

@ArviLaanemets - Thanks for the suggestion regarding the REGION table.
Other than the terminology, I'm not sure that is much different from my STATES tables.
My STATES table has {COUNTRY_ABBREV, STATE_ABBREV} as the primary key which accommodates the same state abbreviation in multiple countries.
You make a good point about some countries not having states. So far, that's not been a problem.

You said: What you mean by "State lookup list"?
I don't see where I referred to a "State Lookup list".
I do have a QSTATES query which provides the list for the STATES combo box.

SELECT States.State_Abbrev, States.State_Name
FROM States
WHERE (((States.Country)=[Forms]![UserNames_SF1]![SF_Country]))
ORDER BY States.State_Name;

[Chief_Up_Yours]

I'm using the QSTATES query I listed in my previous post as the query for my STATES lookup list.
This works fine if I open the UserNames_SF1 form directly.
If I embed UserNames_SF1 within another form, when I open the outer form I get a popup to "Enter Parameter Value" for [UserNames_SF1]![SF_Country].
The subform (UserNames_SF1) has not painted completely, although the COUNTRY field has painted and is correctly filled in from the underlying table.
How can I correct this?

Edit:
I do have a requery on the SF_Country field to update the state list query.

Private Sub SF_Country_AfterUpdate()
    SF_State.Requery
End Sub

[June7]

I avoid compound keys whenever possible. Use of autonumber ID as key would accomplish that. CITY table would not hold country or state abbreviations, it would have a single key field in their place.

[ano]

popup means field not found
solution put hidden field on main form for the query and fill it from ur field with me. bec if i understand u well u have to places for that form

[ano]

im still confused how a primary key superseed if the structure of the tables is fine then abbrevation as key make sence unless join only work with 1 condition then i would not use join , combined with the rownum option i smell a contradiction ;; create pk because it is teached but not create rownum field which speed up query a lot

[Chief_Up_Yours]

@June7 - I don't understand the advantage of an auto-number field as the primary key. It adds an additional index to the table. The unique compound index on {Country_Abbrev,State_Abbrev} is still required.

@ano - Yes, I know the pop-up means the field is not found. However, the field IS on the subform. This happens only when displaying the first record in the table. Subsequent records in the table display correctly.
This also only happens when the subform is embeded in an outer form. If the subform is opened by itself, the first record displays correctly.

@ano - Joins work just fine with compound keys.

[Welshgasman]

This also only happens when the subform is embeded in an outer form. If the subform is opened by itself, the first record displays correctly.

That would infer your syntax is incorrect?
Forms: Refer to Form and Subform properties and controls

[Micron]

There are 2 reasons that I read on this subject at some point.
1) A combo doesn't work with a composite index - it needs a primary key to function properly.
2) you can't relate pk to index or index to index - only pk to fk (or pk to pk but that is another matter).

I can't prove either since I dropped the idea of compound keys being OK long ago. Adding the autonumber pk as another index should not matter in simple tables.

[Chief_Up_Yours]

@Micron - I've changed both the Country and States tables to have an auto-number as thier primary key.
The schema for COUNTRY table now looks like:
COUNTRIES - {Country_ID,Country_name}
The Row Source for the combo box is the following query:

SELECT Countries.Country_ID, Countries.Country_Name
FROM Countries
ORDER BY Countries.Country_Name;

This works fine now.


I'm having trouble getting the cascading STATE combo box to work when the subform is first painted and embedded in an outer form. Neither States nor Country are present in the outer form.
The schema for the STATES table is:
STATES - {State_ID,State_Name,Country_ID}
The Row Source for the combo box is the following query:

SELECT STATES.State_ID, STATES.State_Name
FROM STATES
WHERE (((STATES.Country_ID)=[forms]![UserNames_SF1]![SF_Country_ID]))
ORDER BY STATES.State_Name;

When the UserNames_SF1 subform is first painted, the SF_State combo box doesn't appear immediately, instead I get a parameter prompt for forms.UserNames_SF1.SF_Country_ID.
UserNames_SF1.SF_Country_ID IS on the form and contains the correct value.
Once I give the parameter prompt a value, the STATE combo box appears.


As I stated before, this only occurs when the subform is first painted and is embedded in an outer form. When I position to subsequent records, I do not get the parameter prompt and the Country and State combo boxes fill in correctly on the subform.


The SF_Country_ID combo box has the following event attached:

Private Sub SF_Country_ID_AfterUpdate()
    SF_State_ID.Requery
End Sub

I have checked the TAB ORDER to make sure that the County combo box comes before the States combo box.


@Welshgasman - As I stated before, if I open the subform by itself, it paints correctly.
Both the Country and State combo boxes appear with no parameter pop-up. But when I embed the subform in an outer form, I get the parameter pop-up when the embedded subform is first painted and the STATES combo box has not yet been displayed.


I've checked the link you provided and I believe that [forms]![subform1]![field] syntax is correct for an embedded subform.

[Welshgasman]

Subforms are loaded before mainform?

[Chief_Up_Yours]

@welshgasman - Was that a question? I would think that the main form is loaded first, but not sure how that is relevant here.
The subform has linking fields to the outer form, but Country_ID and State_ID are local to the subform and not passed from the outer form.