Problem with Combobox hierarchy list of options

[Never Hide]

Hello everyone, great forum, I've got soooo much help from all of you guys!! Thank you very much

I have a Database with Geogafical Regions, Municipalities, Cities etc.
Because it's difficult to explain how my DB works using my tables( it has to do with how the system of municipalities works in Greece) I'll use the same structure but use simple data like Table_1, Table_2, Field_1, Field_2 etc.
So lets start, I hope this won't be too confusing

In my database there are 5 tables. Table_1, Table_2, Table_3, Table_4, Table_5
Table_1: (id,name)
Table_2: (id,name,parent_id)
Table_3: (id,name,parent_id)
Table_4: (id,name,parent_id)
Table_5: (id,name,parent_id)
(Table_5 has more fields in it but are not impotant)

The hierarchy from top to bottom goes like this Table_1->Table_2->Table_3->Table_4->Table_5
Now here is the tricky part. some entries from Table_5 don't "belong" to the immediate higher in hierarchy table(Table_4) but instead "belong" Table_3 and there are some that "belong to Table_2. So that means that in the "paent_id" field you get the "id" of Table_4 or Table_3 or Table_2 depending on that specific entry.
The same thing is for some entries of Table 4. Not all "belong" to Table_3. Some of the entries belong to Table_2. So that means that in the "paent_id" field you get the "id" of Table_3 or Table_2depending on that specific entry.

I have created an Unbound form with 5 comboboxes,one for each table. When you make a selection in the table1_combox the list options of the table2_combobox are limited to those that have "parent_id" equal to the "id" of the selection in the table1_combobox. Then when you make a selection in the table2_combobox then the list of options in the table3_comobobox is limited to those that have "parent_id" equal to the "id" of the selection in the table2_combobox and so on. It also checks to see if there are entries in Table_4 and Table_5 that have the selected "id" as "parent_id" and if there are then the list of options in the according combobox is limited to those.
You can also make a selection in any of the comboboxes and then the according comboboxes of higher hierarchy dispplay where that selection belongs to.
It all works great, thanks to a lot of help fom all of you guys

Now to explain what I'd like to accomplish now is a bit difficult fo me using abstract names(Table_1,Table_2 etc) I'll try this,
Keeping in mind the hierarchy of the db given above. Lets say that Table_1=Countries and Table_5=Cities. Since there isn't a direct connection between cities and countries, is it possible when you select a country(selection in table1_combobox) have the list of options in cities(table5_combobox) limited to the cities that belong to that country?

I tried to explain things the clearer I could, I hope it is clear enough

 

[jdraw]

Look at these cascading combo free videos

http://www.datapigtechnologies.com/flashfiles/combobox1.htm
lhttp://www.datapigtechnologies.com/flashfiles/combobox2.html

Selection in combo1 restricts what's shown in combo2 and
selection in combo2 restricts selection in combo3.....

 

[Never Hide]

Hi there jbraw,
thanks for your reply. I watched the combobox2 video(the link for the combobox1 video ruterns a page not found error but I belive I know which video it is and I've watched both before)
I already know how to do what is shown in the video. What I'm trying to do is this,
lets say you have 3 comboboxes 1st is for state, 2nd for post code and 3rd for address name. You select a state and that restricts what's shown in the post code combobox and when you select a post code that restricts what's shown in the address name combobox.
What I want to do is to be able to select a state and when you select a state, that will ristrict what's shown in the address name combobox and it'll be all the address names that respond to the selected state

 

[Never Hide]

[COLOR=Red]Me.table5_combob.RowSoursce="SELECT Table_5.* " & _
                            "FROM (Table_5 " & _
                            "INNER JOIN Table_4 ON Table_5.parent_id=Table_4.id) " & _
                            "INNER JOIN Table_3 ON Table_4.parent_id=Table_3.id " [/COLOR]& _
                            "INNER JOIN Table_2 ON Table_3.parent_id=Table_2.id " & _
                            "INNER JOIN Table_1 ON Table_2.parent_id=Table_1.id " & _
                            "WHERE (((Table_1.id) = [Forms]![Test_Form_1]![table1_combob])) "

This is what I have in mind, I've tested it and when I use just the part in red, then I get the expected result (for that part of course).
Could someone point me to the right direction as to how to move on?
Maybe I shouldn't be using something like this at all, I don't know...
any pointer are GREATLY appreciated

 

[jdraw]

What version of access do you have? I have 2003 and can read .mdb file. If you can post a copy of your db in mdb format, I can look at it.

You are dealing with cascading comboboxes. A combo for each major category. You need to deal with the recordsource of each combo. The next combo is further restricted by the selection from the previous combo.