Access Dependent Tables

Question

In access I have a table called complaints, within that I have 2 combo boxes: category and description, Both of them are drop-down menus. I want the description drop-down menu to be filtered as a result of the category that I choose. For example; my categories are: inside and outside. If I choose inside, then the description drop-down menu should only show dent and scratch. If I choose outside, then the description drop-down menu should only show color, centered, scrape, and bump.

Is there a way I can link these two to have the description box options dependent on my category selection?

Thanks,

R

Answer 1

You might like to take a look at DatabaseBasics.zip n my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the section on 'entering data via a form/subforms' includes a contacts form in which the country, region, and city are selected in three correlated combo boxes in the way you describe.  The important thing to note here is that only the lowest level in the hierarchy, city in my case, should be stored in a column in the table,  The country and region should be selected in unbound combo boxes.  This is because region and country are functionally determined by city, so to store them in the contacts table would be redundant.  The table would consequently not be normalized the Third Normal Form (3NF) and at risk of update anomalies.

In a continuous form correlated combo boxes are a little more complicated if the referenced tables in question have surrogate primary keys, usually autonumbers, which are hidden in the combo boxes' lists.  In this case it is necessary to use a 'hybrid' control made up of a combo box upon which a text box is carefully superimposed to give the appearance of a single combo box control.  The second form in the 'entering data via a form/subforms' section of the demo shows the same data in continuous forms view, using this technique.

Answer 2

"...I have a table ... within that I have 2 combo boxes: category and description,"

Are you saying that you have used Lookup Fields in the table itself? If so, that's an unfortunate design decision. Look up fields are interface elements, not data elements. They do not belong in tables. They belong in forms. Microsoft made the original unfortunate design decision when they introduced the capability in tables.

You can apply cascading combo boxes to controls on a form, but not in the table itself.

Do yourself a favor and revert those Lookup Fields to proper Foreign Key fields containing on the Foreign Key from the Lookup table.

Implement the cascading combo boxes on a form, where that is appropriate.

Answer 3

This is called 'cascading combo boxes'. See Creating Cascading Combo Boxes and List Boxes on Microsoft Access Forms

Sample database: https://www.dropbox.com/s/r3h49vica2ewvyx/Cascade2.accdb?dl=1