Access makes me feel stupid - lookup values

[Zoogrrl]

Hello all, I am an on and off Access user with some basic db design. I
am trying to create a database with a lookup field that changes the
choices available in a subsequent field. Here is my example:

I have one table that has several categories of enrichment:

ID Category of Enrichment
1 Browse
2 Exercise
3 Food based
4 Non food based

Then, another table with the specific types of enrichment:
Category Type of Enrichment
Browse Pine
Browse Honey Locust
Food based Puzzle feeder
Non food based Scent bottle

The final table will be a subform where I would like to have a combo
box drop down list with the category and another drop down combo box
with the type of enrichment. I have done that but I would like the
choices in the drop down for type of enrichment to change in response
to the category of enrichment selected. Is there a simple way I am
overlooking on how to do this? Thanks!!

 

[Graham Mandeno]

Hi Zoogrrl

These are called "cascading combo boxes". The trick is to make the
RowSource of the second combo box dependent on the selection in the first.

There are several ways to do this, but is your combo boxes are in a subform
then I think it is easiest to reassign a SQL statement to the RowSource
property when the first combo box is updated. Something like this:

Private Sub cboEnrichmentCategory_AfterUpdate()
Dim sRowSource as String
If not IsNull(cboEnrichmentCategory) Then
sRowSource = "Select TypeID, TypeName from EnrichmentTypes" _
& " where TypeCategory=" & cboEnrichmentCategory_
& " order by TypeName;"
End If
cboEnrichmentType.RowSource = sRowSource
End Sub

Here I have made the following assumptions:

1. Your table is named "EnrichmentTypes"

2. It has 3 fields: TypeID (numeric primary key), TypeName (text), and
TypeCategory (numeric foreign key related to your EnrichmentCategories
table)

3. Your first combo is named "cboEnrichmentCategory"

4. Your second combo is named "cboEnrichmentType"

Change the names as required.

There are further complications if your subform is continuous (showing
multiple records). If this is the case then post back for more info.

 

[Jeff Boyce]

Terminology, like spelling, counts! You described tables and forms and ...

I suspect you are working with a form (based either directly on a table or
on a query that's based on a table). It sounds like you want to have the
list in one combo box limited by what gets selected in an earlier combo box.
Take a look at mvps.org/access or at Google.com or in Access HELP for
"Cascading Combo Boxes".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Zoogrrl]

Thanks Jeff, you are absolutely right about terminology - forgive my
newbie errors! Thanks for the assist.

 

[Zoogrrl]

Thanks Graham! Knowing the terminology for what I want to do will help
immensely. Thanks again!

Hi Zoogrrl

These are called "cascading combo boxes". The trick is to make the
RowSource of the second combo box dependent on the selection in the first.

There are several ways to do this, but is your combo boxes are in a subform
then I think it is easiest to reassign a SQL statement to the RowSource
property when the first combo box is updated. Something like this:

Private Sub cboEnrichmentCategory_AfterUpdate()
Dim sRowSource as String
If not IsNull(cboEnrichmentCategory) Then
sRowSource = "Select TypeID, TypeName from EnrichmentTypes" _
& " where TypeCategory=" & cboEnrichmentCategory_
& " order by TypeName;"
End If
cboEnrichmentType.RowSource = sRowSource
End Sub

Here I have made the following assumptions:

1. Your table is named "EnrichmentTypes"

2. It has 3 fields: TypeID (numeric primary key), TypeName (text), and
TypeCategory (numeric foreign key related to your EnrichmentCategories
table)

3. Your first combo is named "cboEnrichmentCategory"

4. Your second combo is named "cboEnrichmentType"

Change the names as required.

There are further complications if your subform is continuous (showing
multiple records). If this is the case then post back for more info.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello all, I am an on and off Access user with some basic db design. I
am trying to create a database with a lookup field that changes the
choices available in a subsequent field. Here is my example:

I have one table that has several categories of enrichment:

ID Category of Enrichment
1 Browse
2 Exercise
3 Food based
4 Non food based

Then, another table with the specific types of enrichment:
Category Type of Enrichment
Browse Pine
Browse Honey Locust
Food based Puzzle feeder
Non food based Scent bottle

The final table will be a subform where I would like to have a combo
box drop down list with the category and another drop down combo box
with the type of enrichment. I have done that but I would like the
choices in the drop down for type of enrichment to change in response
to the category of enrichment selected. Is there a simple way I am
overlooking on how to do this? Thanks!!