How do I create simple Cascading Combo Boxes in Access

There are many articles that instruct on how to create Cascading or Synchronized combos.

Thank you for the response! I do appreciate it!

 

I have done exactly as instructed however dont achieve the desired results.  The Row Source for the  Type Field shows as:

 

SELECT [Category&Type].Type FROM [Category&Type] WHERE ((([Category&Type].Type)=[Forms]![AssistanceTracker]![CategoryCombo]));

 

Which populates when the Query is built under the criteria:

 

Using the Category and Type Table (ID, Type, Category)

• Field: Type
• Table: Category&Type
• Sort: Blank
• Show: Checked
• Criteria: [Forms]![AssistanceTracker]![CategoryCombo]

What am I missing? . . .

The criteria needs to be more like:

 

WHERE ((([Category&Type].Category)=[Forms]![AssistanceTracker]![CategoryCombo]));

Your Rowsource should be something like:

SELECT Type FROM Category&Type WHERE Category = Forms!AssistanceTracker!CategoryCombo;

I there a way to create the Cascading drop down box using the Relationships within Access?  I am seeing the option to "Enforce Referential Integrity" and then "Cascade Update Related Fields."  Im sure this is now Microsoft "Built in" the ability to create Cascading Drop down Boxes. . . If so, whats the best way to set up each table?

 

I have several different tables that have been built in my attempts to create the Cascading Drop down List.  Mind you I only have 2 drop down boxes:

 

1 drop down box to Select the "Category" & a 2nd drop down box needed to Select the "Type" under that Category.

 

What is the best way to set up each table(s) and relationships to create the Cascading Drop Down Box?

 

 

Thank you for your time. . .

 

I there a way to create the Cascading drop down box using the Relationships within Access?  I am seeing the option to "Enforce Referential Integrity" and then "Cascade Update Related Fields."  Im sure this is now Microsoft "Built in" the ability to create Cascading Drop down Boxes.

Not so; the enforcement of cascade updates simple means that if you change the value in a primary key column of a referenced table, the values in the corresponding foreign key columns in any referencing tables will automatically change.  It has nothing to do with the creation of correlated combo boxes, which you must set up yourself in the way described by Scott and Marshall.

You'll find examples of the use of correlated combo boxes in various contexts in ComboDemo.zip in my public databases folder at:

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

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

Note that, unlike some demos you'll see, this demo is specifically intended to illustrate how to build this sort of functionality in the context of a set of correctly normalized tables.  In your case this would mean that if the combo boxes are being used to enter data into a referencing table, this table should only include a foreign key Type column, not a Category column, as the latter is determined by the former, so its inclusion would mean that the table is not normalized to Third Normal Form (3NF), which requires that all non-key columns are determined solely by the whole of the table's key.  A table which is not normalized to 3NF is at risk of update anomalies.

I have a simple task to complete in Access. I understand it's called Cascading Combo boxes.  I have a list of "Categories" and a list of "Types" under each Category.  I need each user to be able to select the proper Category, and the Type Combo Box to only populate the Types for the Category. . .

 

I have a table for Categories Only (ID, Category), and a 2nd table listing Types (ID, Type, Category).

 

What is the simplist way of doing this? . . . Thank you for your time. . .

I'm Sorry everyone, I still cant seem to get this to work.  I have used/done several instructions on several sites and forums and still cant get this to work. . . Is there any further advice or information that may assist me with this?

 

Thank you for your time, I do appreciate it!

Can you upload a copy of your database to a cloud drive (like SkyDrive) so we can take a look at it?

Unfortunately no, The network I'm in doesnt allow me to do so.

 

I'll have to create a database outside of the network and upload it then.  That's a great idea!

I still cant seem to get this to work.

The way to get things to work is to understand how they work.  In the case of correlated combo boxes it's quite straightforward:

1.  The first combo box's RowSource property is a query which simply returns rows for all the items in the top level of the hierarchy, categories in your case.

2.  The second combo box's RowSource property is a query which returns rows for those items in the next level down of the hierarchy, types in your case, restricted to those rows where the category for the type is that selected in the first combo box.  This is done by referencing the first combo box as a parameter for the relevant category column.  You need to be sure that the value of the first combo box is that which the values in the category column will match, so if the value of the category column is a hidden number, not the text value, which it will be if, for instance, the dreaded lookup field wizard was used when designing the table, the value of the first combo box needs to be the primary key numeric column (usually an autonumber) of the categories table, which means the first combo box's BoindColumn property will be a hidden numeric column, thought you'll see the text values from a non-key column in the combo box.

3.  In the AfterUpdate event procedure of the first combo box you need to do two things:

3.1  Set the second combo box to Null so that any current value which it might have is removed.

3.2  Requery the second combo box so that it lists only the types for the selected category.

Once you have a good understanding of the process involved, setting up correlated combo boxes is very straightforward.  The issue of normalization of the form's underlying table, while important for the integrity of the database, is not directly related, so concentrate on getting your teeth around the principles behind correlated combo boxes as outlined above.  My demo, to which I referred you earlier, can then be used as a paradigm for making any amendments necessary to use the controls in the context of a table normalized to Third Normal Form.