How do I setup cascading combo boxes - (end to end)

  • Thread starter Thread starter Denniso6
  • Start date Start date
D

Denniso6

Good morning all !

I’ve added a ‘SubCategory’ field to the Cases table in the Customer Service
database template.

My idea here is to setup the corresponding ‘Category’ and ‘SubCategory’
fields (in the ‘Case Details’ form) as cascading combo boxes so that a user
may do the following:

1.User selects either 'Hardware', 'Software' or 'Services' from the
‘Category’ combo box
2.User then may select filtered choices from 'SubCategory' combo box based
on his/her choice in the ‘Category’ combo box. (For example..., selecting
'Hardware' will cause 'SubCategory' to only display 'Computer', 'Printer',
'Handhand', 'Blackberry' – or selecting ‘Software’ category would only
display ‘MS Word’, ‘MS Excel’, etc., in the ‘SubCategory’ combo box)

I’ve attempted to do this using Example 1: 'Multiple Row Source Tables' at
link http://www.fontstuff.com/access/acctut10.htm, but to no avail.

When I open the 'Case Details' form and click the 'Category' combo box, only
a series of vertically aligned numbers; 1, 2, 3..., are displayed. Absolutely
nothing is displayed in the 'SubCategory' combo box.

Is there anything I’m missing here ? (…such as the settings for ‘Row
Source’, or ‘Row Source Type’ (either on the Data tab of the form property
sheet or Lookup tab in the table itself ?)

Any assistance would be most appreciated.

DennisO
 
What I would have is have a table for category and a table for subcategory
and establish a 1-to-many relationship.

The first combo has the list of categories (the name of the combo is
'category')

The second combo (called 'subcategory') will have a query as the source:

first column is subcategory
second column is category

The second column has the following in the criteria list

[forms]![form name]![category]


The bound column for the second combo is column1, select the number of
columns in the format tab to 2 and select the widths as 4cm and 0cm.

In the 'after update' event of the first combo put:

[forms]![form name]![subcategory].requery
 
Ok..., I think I'm with you here...,

Question..., does the 'SubCategory' table have 3 fields; (i.e..., ID,
Category and Subcategory) ?

I'm assuming the 'One' side of the relationship is represented by the
'Category' table and the 'many' side is represented by 'SubCategory', yes ?

----------------------
scubadiver said:
What I would have is have a table for category and a table for subcategory
and establish a 1-to-many relationship.

The first combo has the list of categories (the name of the combo is
'category')

The second combo (called 'subcategory') will have a query as the source:

first column is subcategory
second column is category

The second column has the following in the criteria list

[forms]![form name]![category]


The bound column for the second combo is column1, select the number of
columns in the format tab to 2 and select the widths as 4cm and 0cm.

In the 'after update' event of the first combo put:

[forms]![form name]![subcategory].requery




Denniso6 said:
Good morning all !

I’ve added a ‘SubCategory’ field to the Cases table in the Customer Service
database template.

My idea here is to setup the corresponding ‘Category’ and ‘SubCategory’
fields (in the ‘Case Details’ form) as cascading combo boxes so that a user
may do the following:

1.User selects either 'Hardware', 'Software' or 'Services' from the
‘Category’ combo box
2.User then may select filtered choices from 'SubCategory' combo box based
on his/her choice in the ‘Category’ combo box. (For example..., selecting
'Hardware' will cause 'SubCategory' to only display 'Computer', 'Printer',
'Handhand', 'Blackberry' – or selecting ‘Software’ category would only
display ‘MS Word’, ‘MS Excel’, etc., in the ‘SubCategory’ combo box)

I’ve attempted to do this using Example 1: 'Multiple Row Source Tables' at
link http://www.fontstuff.com/access/acctut10.htm, but to no avail.

When I open the 'Case Details' form and click the 'Category' combo box, only
a series of vertically aligned numbers; 1, 2, 3..., are displayed. Absolutely
nothing is displayed in the 'SubCategory' combo box.

Is there anything I’m missing here ? (…such as the settings for ‘Row
Source’, or ‘Row Source Type’ (either on the Data tab of the form property
sheet or Lookup tab in the table itself ?)

Any assistance would be most appreciated.

DennisO
Click to expand...
Click to expand...
 
If 'ID' is your bound field and is PK/FK then 'yes' and you can alter the
format in the combo box so you see the category list rather than the ID.

The answer to 2nd question is also 'yes'

Denniso6 said:
Ok..., I think I'm with you here...,

Question..., does the 'SubCategory' table have 3 fields; (i.e..., ID,
Category and Subcategory) ?

I'm assuming the 'One' side of the relationship is represented by the
'Category' table and the 'many' side is represented by 'SubCategory', yes ?

----------------------
scubadiver said:
What I would have is have a table for category and a table for subcategory
and establish a 1-to-many relationship.

The first combo has the list of categories (the name of the combo is
'category')

The second combo (called 'subcategory') will have a query as the source:

first column is subcategory
second column is category

The second column has the following in the criteria list

[forms]![form name]![category]


The bound column for the second combo is column1, select the number of
columns in the format tab to 2 and select the widths as 4cm and 0cm.

In the 'after update' event of the first combo put:

[forms]![form name]![subcategory].requery




Denniso6 said:
Good morning all !

I’ve added a ‘SubCategory’ field to the Cases table in the Customer Service
database template.

My idea here is to setup the corresponding ‘Category’ and ‘SubCategory’
fields (in the ‘Case Details’ form) as cascading combo boxes so that a user
may do the following:

1.User selects either 'Hardware', 'Software' or 'Services' from the
‘Category’ combo box
2.User then may select filtered choices from 'SubCategory' combo box based
on his/her choice in the ‘Category’ combo box. (For example..., selecting
'Hardware' will cause 'SubCategory' to only display 'Computer', 'Printer',
'Handhand', 'Blackberry' – or selecting ‘Software’ category would only
display ‘MS Word’, ‘MS Excel’, etc., in the ‘SubCategory’ combo box)

I’ve attempted to do this using Example 1: 'Multiple Row Source Tables' at
link http://www.fontstuff.com/access/acctut10.htm, but to no avail.

When I open the 'Case Details' form and click the 'Category' combo box, only
a series of vertically aligned numbers; 1, 2, 3..., are displayed. Absolutely
nothing is displayed in the 'SubCategory' combo box.

Is there anything I’m missing here ? (…such as the settings for ‘Row
Source’, or ‘Row Source Type’ (either on the Data tab of the form property
sheet or Lookup tab in the table itself ?)

Any assistance would be most appreciated.

DennisO
Click to expand...
Click to expand...
Click to expand...
 
Back
Top