Populate combo box based on value chosen in another combo box

[Gary Childress]

This is what I'd like to be able to do:



I have a table called "MainTable". It has a bunch of fields in it. Most of the fields are based on lookup fields. Each lookup field may have at most 10 possible answers or less in it. So there are a limited number of possible values which a particular field can have. For instance, one lookup field may have 3 possible answers, "Yes";"No";"N/A". Another lookup field may have a different set of possible answers.

I have a form called "Switchboard". It has 2 combo boxes on it, "ComboBox1" and "ComboBox2".

When I click on "ComboBox1", I want it to display specific fields in "MainTable" in its dropdown list, but not all the fields.

When I select a value from the drop down list in "ComboBox1", I want "ComboBox2" to populate with all the different answers available for that field in "MainTable".

Next I want to be able to click a button and call up a query based upon the values in "ComboBox1" and "ComboBox2" where "ComboBox1" represents the field on the table I want searched and "ComboBox2" represents the value in that field I want displayed in the query.

I'm attaching a copy of the database I'm working on. I've almost got it to where it is doing what I want above except for a few problems:

1. I don't want "ID" or "CLASS" to show up in ComboBox1. In other words I don't want to query those fields.

2. "MainQuery" is confusing fields "Row4" and "Row5" because they have identical value lists. Both fields have "Yes";"No";"N/A" for their values. Therefore the way I have it set up now it seems to query ALL fields with the value of ComboBox2. I just want it to pick up the value in ComboBox2 of the field selected in ComboBox1.

Thanks for any help!

[alansidman]

What you appear to be referring to are cascading combo boxes. Look at this link on how to make them work.

http://www.fontstuff.com/access/acctut10.htm

He describes two ways to do this.

Additionally, you may want to look at this link and scroll down to the section on combo boxes.

http://www.datapigtechnologies.com/AccessMain.htm

[ssanfu]

Most of the fields are based on lookup fields.

Step 1: Get rid of the lookup fields. See http://access.mvps.org/access/lookupfields.htm Also see: http://access.mvps.org/access/tencommandments.htm

When I click on "ComboBox1", I want it to display specific fields in "MainTable" in its dropdown list, but not all the fields.

1. I don't want "ID" or "CLASS" to show up in ComboBox1. In other words I don't want to query those fields.

A) In the criteria row of the query, enter <> "ID" and <>"Class"

or

B) Put the fields (but not "ID" or "Class") in a table. Base the row source property of ComboBox1 on a query of that table. (My preference)

When I select a value from the drop down list in "ComboBox1", I want "ComboBox2" to populate with all the different answers

This is called cascading combo boxes. See Rogers Access Library http://www.rogersaccesslibrary.com/forum/topic389.html or Google "cascading combo boxes"

-----------------
Me being picky: Tables have fields. Forms have controls. If the form is bound to a record source, then a control can be bound to a field.

[iib]

Step 1: Get rid of the lookup fields.

Very often we get this advice. But no one tells us what to use instead!!!

As a beginner I am using a lot these Lookup Fields for almost all the FK. I want very much to avoid them, but I don't know what shall I use instead.

I would appreciate your suggestion on this matter.