cascading combo box

[celeris]

Hello, before posting this topic I've tried find and anwer in this forum without success. I hope you can help me.
I want to make a form that allows to user register elements classified in 1 categorie and 2 subcategories. So I've created 3 tables for the levels and on for the elements.
There is on form (tbL1) with subform for the entries of the differents leves (it woks fine). Also there is a form frmElment wich on seems to work well but when you navigate from between records sometimes it get lost data (doesn't remain the previously selected level. I've attached the sample database.
Can you help me? It urges me to find a solution. Thank you!

 

[Mile-O]

A three-tiered cascading combo is no different from the more simple two-tier. You just apply the same technique of querying the bottom tier based on the selection in the previous tier.

 

[celeris]

Mile,

I'm sorry but I don't understand what you mean.
Can you give me detailed information based on the example?. I think that the design is correct, of course, I'm not sure.
The first combo gets data directly from table. The second one gets data from a select wich take the value from the first combo to make the condition into the select...
If you can clarify the solution a little I will thank you.

 

[Mile-O]

Let’s say the topmost category was Continents, subcategory1 was Countries, and subcategory2 was Cities.

The table structure would be:

tblContinents
ContinentID – autonumber
Continent – text

tblCountries
CountryID – autonumber
Country – text
ContinentID - number

tblCities
CityID – autonumber
City - text
CountryID – number

On our form (we’ll call the form: frmExample – our three combos: cboContinent, cboCountry, and cboCity

The RowSource of the topmost combo is:

SELECT ContinentID, Continent FROM tblContinents ORDER BY Continent;

The RowSource for the middle combo is:

SELECT CountryID, Country FROM tblCountries WHERE ContinentID = [Forms]![frmExample]![cboContinent] ORDER BY Country;

The other RowSource is:

SELECT CityID, City FROM tblCities WHERE CountryID = [Forms]![frmExample]![cboCountry] ORDER BY City;


On the AfterUpdate event of cboContinent put:

Me.cboCountry.Requery


On the AfterUpdate event of cboCountry put:

Me.cboCity.Requery

 

[celeris]

Hello Mile,

First, thanks for your time and your fast response.

Well, now I've understand what you had said. I think that my first database already follows yours recommendations. The difference between your design and mine seems to be that my Primary Key was composed by two keys (thus I allowed the same code for a subcategorie that belongs to different category). Do you thing this was wrong?
I don't still understand because it failed but I surrender to the evidence (now the new database doesn't lost the previous data but it has another problem).
Now I've attached the new database, with a indivisible Primary Key and the problem is another.

When I change the selected value in a combobox don't put to blank the values in the related combobox and it also allows a value outside of the list (remain the old value when I change to another register althougth I've put limitToList TRUE).