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!!

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

"Zoogrrl" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!!
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!!
>

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

Jeff Boyce wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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!!
> >

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

Graham Mandeno wrote:
> 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
>
> "Zoogrrl" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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!!
> >