How to set a list of values for a Combo box based on a selected value in another Co

[croby]

Hi, I’m designing a form by using ACCESS 97. My database includes 3 tables: System, Subsystem and Major. They are linked by their IDs. Suppose the System table has 3 data values: Sys_A, Sys_B, Sys_C. The Subsystem has 10 data values: Sub_1, Sub_2, Sub_3, ……Sub_10. The Major table has 20 data values: Major_1, Major_2, ……Major_20.

Sub_1, Sub_4 and Sub_7 are under Sys_C. Major_3, Major_6, Major_8, and Major_11 are under Sub_4. etc.

On my form, I use Combo Boxes for System, Subsystem and Major.

The requirement is: when I click the down arrow on Combo System, Sys_A, Sys_B and Sys_C should appear in the drop down list. I select Sys_C, move to Combo Susbsystem, then click the down arrow, only Sub_1, Sub_4 and Sub_7 appear in the drop down list. I select Sub_4, then move to Combo Major. I click the down arrow, only the 4 Majors which belong to Sub_4 appear in the list.

Please help me to design this form. Thanks a lot! Chris

 

[skwilliams]

Attached is a word document I've created which better explains cascading combo boxes.

HTH

 

[croby]

Reply to Skwilliams -- combo box issues

Your example is very good. I followed your instructions and applied them to my database. As I said in my last post, I have 3 tables. The list values in the second Combo box should be a JOIN results. The first Combo box (cboSystem) works fine. But the 2nd Combo Box (cboSubSystem) appears no data. I attached my screen shot in Word format.
Please tell me if anything I did wrong. I really appreciate your help. Chris.

 

[ghudson]

My 2 cents...

Your criteria for the combo box does not appear to be correct. It should look like this...

Like [Forms]![Test_Combo]![cboSystem]

Test_Combo = the name of the form
cboSystem = the name of the combo box

It is best to "build" your criteria when referencing foreign objects so ensure the syntax is correct.

HTH

 

[ghudson]

croby,

Check out my posted filteredcomboboxes.zip sample. It should show you how to do what you want with three combo boxes.

HTH

 

[croby]

Dynamic Comboboxes

I clicked the link filteredcomboboxes.zip (from ghudson, I downloaded), which leads me to Lancer’s message page. Here I found a Tutorial Dynamic Comboboxes . I don’t know who wrote this tutorial.(there are many names on that page.) It is pretty good. I followed the samples and made my 3 Combos (System, Subsytem and Major as I posted on 2/28/03) work.

Now my questions is, how to store my entered data into a table? The tutorial mentioned about this, but I still did not get the way to do it.
I want to store the data in to a table called IC. It has 4 fields: ic_ID, ic_System,ic_SubSystem and ic_Major. They are all Number fields. Then I opened the 3 Combos’s property sheet. Under the Data tab I found the Control Source. I filled in the 3 field names of the IC table. Then all the 3 Combos become Bound control. When I run the form. I can no longer select the data in the drop down list. ACCESS displays a message:

“Control can’t be edited; it’s bound to unknown field ‘ic_System’.”
(When I use Code builder, the field name becomes an expression [ic]![ic_System])

The tutorial said, “ Link the control Source property of each of the three comboboxes as follows….”. How to LINK? I’m sure I did not do the right LINK.
Please help. I send my great appreciation to you all. Chris