How to filter one drop down combo box to another

[Tina Marie]

Hello ... trying to use a combo box on a form so that when you chose from the
first combo box (themes), it filters the next combo box for only that one
theme and displays its specific subthemes ... then repeat for subsubthemes ...

Here are the 3 tables that 'house' the data, the 3 tables the have the drop
down info and the code I put into the 'after update' event on the theme combo
box:

Can anyone see what is wrong ... currently, I click the first drop down
(theme) and I choose one but then the next drop down lists nothing!! Help!!
See below

Tables that will ‘house the data’:
Table 1 Table 2 Table 3
tblThemes: tblSubThemes tblSubSubThemes
strThemeID strSubThemeID strSubSubThemeID
strTheme strSubTheme strSubSubTheme
strAgencyID strThemeID strSubThemeID
strLogID

Tables that hold the drop down info:
tblListofThemes tblListofSubThemes tblList ofSubSubThemes
strTheme strTheme strTheme
strSubTheme strSubTheme strSubSubTheme

On the form, the combo box ‘after update’ event is:

Private Sub cboThemes_AfterUpdate()
Me.cboSubThemes.RowSource = "select strsubtheme from" & _
"tbllistofsubthemes where strtheme=" & _
Me.cboThemes & _
"order by strtheme"

Me.cboSubThemes = Me.cboSubThemes.ItemData(0)

End Sub

 

[Ken Snell MVP]

See this article:
http://www.mvps.org/access/forms/frm0028.htm

 

[Tina Marie]

Thanks Ken ... I will give this a try ... from what I put in my original
question .. am I on the right track?
--
Thanks!!

T. Marie

See this article:
http://www.mvps.org/access/forms/frm0028.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[huangda 晋江兴达机械]

Hello ... trying to use a combo box on a form so that when you chose from
the
first combo box (themes), it filters the next combo box for only that one
theme and displays its specific subthemes ... then repeat for subsubthemes
...

Here are the 3 tables that 'house' the data, the 3 tables the have the
drop
down info and the code I put into the 'after update' event on the theme
combo
box:

Can anyone see what is wrong ... currently, I click the first drop down
(theme) and I choose one but then the next drop down lists nothing!!
Help!!
See below

Tables that will ‘house the data’:
Table 1 Table 2 Table 3
tblThemes: tblSubThemes tblSubSubThemes
strThemeID strSubThemeID strSubSubThemeID
strTheme strSubTheme strSubSubTheme
strAgencyID strThemeID strSubThemeID
strLogID

Tables that hold the drop down info:
tblListofThemes tblListofSubThemes tblList ofSubSubThemes
strTheme strTheme strTheme
strSubTheme strSubTheme strSubSubTheme

On the form, the combo box ‘after update’ event is:

Private Sub cboThemes_AfterUpdate()
Me.cboSubThemes.RowSource = "select strsubtheme from" & _
"tbllistofsubthemes where strtheme=" & _
Me.cboThemes & _
"order by strtheme"

Me.cboSubThemes = Me.cboSubThemes.ItemData(0)

End Sub

 

[Ken Snell MVP]

Yes. The trick is to requery the next combo box in the sequence in the
current combo box's AfterUpdate event. Post back if you have questions.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks Ken ... I will give this a try ... from what I put in my original
question .. am I on the right track?

 

[Tina Marie]

Hi Ken ... is there any way you can provide a simple example ... I can build
fairly extensive databases as long as I can use access' wizards to help write
the sql and vba code ... that's where I fall short ... an example i'm working
on to make things simplier ... involves having a table of car makes and a
table of car models ... so ... tblallmakes and tblallmodels ... I created the
2 'dummy' tables which simple 'house' the various makes in one and the
various models in another ... they 2 tables are linked via the makeID
(primary to foreign) ...

Then I have 2 tables that will actually 'house' the choices I make ... they
are linked the same way but have nothing in them (obviously to begin with)
.... tblmakes and tblmodels

when I create the form, I 'attach' it to the tblmakes and put into the
design the field 'make' and have it be a drop down which looks at the
tblallmakes (same for models)

in the combo for make, the 'after update' event would have sql code on it
.... something like:

Private Sub cboMake_AfterUpdate()
Me.cboModel.RowSource = "SELECT model FROM" & _
" tblallmodels WHERE make = " & _
Me.cboMake & _
" ORDER BY model"

Me.cboModel = Me.cboModel.ItemData(0)

End Sub

But not working ... ahhhhhhhhhhh ... very frustrating ... to go to a help
link .. is very confusing to me ... are you able to check what I've done
above and correct ...

p.s. my regular email is (e-mail address removed) if this is easier ...

 

[Tina Marie]

Hi Ken ... is there any way you can provide a simple example ... I can build
fairly extensive databases as long as I can use access' wizards to help write
the sql and vba code ... that's where I fall short ... an example i'm working
on to make things simplier ... involves having a table of car makes and a
table of car models ... so ... tblallmakes and tblallmodels ... I created the
2 'dummy' tables which simple 'house' the various makes in one and the
various models in another ... they 2 tables are linked via the makeID
(primary to foreign) ...

Then I have 2 tables that will actually 'house' the choices I make ... they
are linked the same way but have nothing in them (obviously to begin with)
.... tblmakes and tblmodels

when I create the form, I 'attach' it to the tblmakes and put into the
design the field 'make' and have it be a drop down which looks at the
tblallmakes (same for models)

in the combo for make, the 'after update' event would have sql code on it
.... something like:

Private Sub cboMake_AfterUpdate()
Me.cboModel.RowSource = "SELECT model FROM" & _
" tblallmodels WHERE make = " & _
Me.cboMake & _
" ORDER BY model"

Me.cboModel = Me.cboModel.ItemData(0)

End Sub

But not working ... ahhhhhhhhhhh ... very frustrating ... to go to a help
link .. is very confusing to me ... are you able to check what I've done
above and correct ...

p.s. my regular email is (e-mail address removed) if this is easier ...

 

[Ken Snell MVP]

To make it easier for me to help, please provide the following information:

Name of first combo box
Name of second combo box
SQL statement of the query that is the RowSource for the second combo box
(if you're just using a table name as the RowSource for the second combo
box,
tell us the name of the table)
Data type of the field that is to be filtered in the second combo box
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Tina Marie]

Hi Ken ...

I found an example in the help using the Northwind database ... will try and
explain my situation as best I can with the info you need:

2 tables that list themes and subthemes:

table 1 (call it tblListofThemes) has fields:
strTheme=text

table 2 (call it tblListofSubThemes) has fields:
strSubTheme=text
strTheme=text

2 tables that will 'capture' what I chose in combo boxes on a subform that is
linked to a parent table via a multi-field primary key strAgencyID (text)
and strLogID
(autonumber)


table 1 has fields:
strThemeID=autonumber
strTheme=text
strAgencyID=text
strLogID=number

table 2 has fields:
strSubThemeID=autonumber
strSubTheme=text
strThemeID=number

On the subform (call it sfrmAllThemes) we have 2 combo boxes, call one
cboTheme and one cboSubTheme

The cboSubTheme has a select statement on its rowsource property created by
the wizard where in a query builder environment it has:

strSubthemeID strSubtheme strThemeid
strlistofsubthemes strlistofsubthemes strlistofsubthemes

[forms]![sfrmAllThemes]![strtheme]

Hope this helps ...

Thanks!!

T. Marie

 

[Ken Snell MVP]

OK.

On your subform, create the two combo boxes: cboTheme and cboSubTheme

Set up the cboTheme combo box so that it allows you to choose the theme. The
query for the RowSource would be
SELECT strTheme FROM tblListofThemes ORDER BY strTheme;

Set up the cboSubTheme combo box so that it allows you to choose the
subtheme, with a filter based on the value selected in cboTheme. The query
for the RowSource would be
SELECT strSubTheme FROM tblListofSubThemes WHERE strTheme = [cboTheme]
ORDER BY strSubTheme;

Create this event procedure for the AfterUpdate event of the cboTheme combo
box:

Private Sub cboTheme_AfterUpdate()
' Filters the second combo box based on
' what was just selected in the first combo box
Me.cboSubTheme.Requery
End Sub


Create this event procedure for the Current event of the subform:

Private Sub Form_Current()
' Filters the second combo box based on
' the value that is in the first combo box for this record
Me.cboSubTheme.Requery
End Sub


This setup should yield the results that you want.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken ...

I found an example in the help using the Northwind database ... will try
and
explain my situation as best I can with the info you need:

2 tables that list themes and subthemes:

table 1 (call it tblListofThemes) has fields:
strTheme=text

table 2 (call it tblListofSubThemes) has fields:
strSubTheme=text
strTheme=text

2 tables that will 'capture' what I chose in combo boxes on a subform that
is
linked to a parent table via a multi-field primary key strAgencyID (text)
and strLogID
(autonumber)


table 1 has fields:
strThemeID=autonumber
strTheme=text
strAgencyID=text
strLogID=number

table 2 has fields:
strSubThemeID=autonumber
strSubTheme=text
strThemeID=number

On the subform (call it sfrmAllThemes) we have 2 combo boxes, call one
cboTheme and one cboSubTheme

The cboSubTheme has a select statement on its rowsource property created
by
the wizard where in a query builder environment it has:

strSubthemeID strSubtheme strThemeid
strlistofsubthemes strlistofsubthemes strlistofsubthemes

[forms]![sfrmAllThemes]![strtheme]

Hope this helps ...

Thanks!!

T. Marie

To make it easier for me to help, please provide the following
information:

Name of first combo box
Name of second combo box
SQL statement of the query that is the RowSource for the second combo box
(if you're just using a table name as the RowSource for the second combo
box,
tell us the name of the table)
Data type of the field that is to be filtered in the second combo box
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Tina Marie]

Excellent Ken ... I will give this a try ... keep your fingers crossed!!
--
Thanks!!

T. Marie

OK.

On your subform, create the two combo boxes: cboTheme and cboSubTheme

Set up the cboTheme combo box so that it allows you to choose the theme. The
query for the RowSource would be
SELECT strTheme FROM tblListofThemes ORDER BY strTheme;

Set up the cboSubTheme combo box so that it allows you to choose the
subtheme, with a filter based on the value selected in cboTheme. The query
for the RowSource would be
SELECT strSubTheme FROM tblListofSubThemes WHERE strTheme = [cboTheme]
ORDER BY strSubTheme;

Create this event procedure for the AfterUpdate event of the cboTheme combo
box:

Private Sub cboTheme_AfterUpdate()
' Filters the second combo box based on
' what was just selected in the first combo box
Me.cboSubTheme.Requery
End Sub


Create this event procedure for the Current event of the subform:

Private Sub Form_Current()
' Filters the second combo box based on
' the value that is in the first combo box for this record
Me.cboSubTheme.Requery
End Sub


This setup should yield the results that you want.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken ...

I found an example in the help using the Northwind database ... will try
and
explain my situation as best I can with the info you need:

2 tables that list themes and subthemes:

table 1 (call it tblListofThemes) has fields:
strTheme=text

table 2 (call it tblListofSubThemes) has fields:
strSubTheme=text
strTheme=text

2 tables that will 'capture' what I chose in combo boxes on a subform that
is
linked to a parent table via a multi-field primary key strAgencyID (text)
and strLogID
(autonumber)


table 1 has fields:
strThemeID=autonumber
strTheme=text
strAgencyID=text
strLogID=number

table 2 has fields:
strSubThemeID=autonumber
strSubTheme=text
strThemeID=number

On the subform (call it sfrmAllThemes) we have 2 combo boxes, call one
cboTheme and one cboSubTheme

The cboSubTheme has a select statement on its rowsource property created
by
the wizard where in a query builder environment it has:

strSubthemeID strSubtheme strThemeid
strlistofsubthemes strlistofsubthemes strlistofsubthemes

[forms]![sfrmAllThemes]![strtheme]

Hope this helps ...

Thanks!!

T. Marie

To make it easier for me to help, please provide the following
information:

Name of first combo box
Name of second combo box
SQL statement of the query that is the RowSource for the second combo box
(if you're just using a table name as the RowSource for the second combo
box,
tell us the name of the table)
Data type of the field that is to be filtered in the second combo box
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Hi Ken ... is there any way you can provide a simple example ... I can
build
fairly extensive databases as long as I can use access' wizards to help
write
the sql and vba code ... that's where I fall short ... an example i'm
working
on to make things simplier ... involves having a table of car makes and
a
table of car models ... so ... tblallmakes and tblallmodels ... I
created
the
2 'dummy' tables which simple 'house' the various makes in one and the
various models in another ... they 2 tables are linked via the makeID
(primary to foreign) ...

Then I have 2 tables that will actually 'house' the choices I make ...
they
are linked the same way but have nothing in them (obviously to begin
with)
... tblmakes and tblmodels

when I create the form, I 'attach' it to the tblmakes and put into the
design the field 'make' and have it be a drop down which looks at the
tblallmakes (same for models)

in the combo for make, the 'after update' event would have sql code on
it
... something like:

Private Sub cboMake_AfterUpdate()
Me.cboModel.RowSource = "SELECT model FROM" & _
" tblallmodels WHERE make = " & _
Me.cboMake & _
" ORDER BY model"

Me.cboModel = Me.cboModel.ItemData(0)

End Sub

But not working ... ahhhhhhhhhhh ... very frustrating ... to go to a
help
link .. is very confusing to me ... are you able to check what I've
done
above and correct ...

p.s. my regular email is (e-mail address removed) if this is easier
...


--
Thanks!!

T. Marie


:

Yes. The trick is to requery the next combo box in the sequence in the
current combo box's AfterUpdate event. Post back if you have
questions.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thanks Ken ... I will give this a try ... from what I put in my
original
question .. am I on the right track?
--
Thanks!!

T. Marie


:

See this article:
http://www.mvps.org/access/forms/frm0028.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Hello ... trying to use a combo box on a form so that when you
chose
from
the
first combo box (themes), it filters the next combo box for only
that
one
theme and displays its specific subthemes ... then repeat for
subsubthemes
...

Here are the 3 tables that 'house' the data, the 3 tables the
have
the
drop
down info and the code I put into the 'after update' event on the
theme
combo
box:

Can anyone see what is wrong ... currently, I click the first
drop
down
(theme) and I choose one but then the next drop down lists
nothing!!
Help!!
See below

Tables that will 'house the data':
Table 1 Table 2 Table 3
tblThemes: tblSubThemes tblSubSubThemes
strThemeID strSubThemeID strSubSubThemeID
strTheme strSubTheme strSubSubTheme
strAgencyID strThemeID strSubThemeID
strLogID

Tables that hold the drop down info:
tblListofThemes tblListofSubThemes tblList ofSubSubThemes
strTheme strTheme strTheme
strSubTheme strSubTheme strSubSubTheme

On the form, the combo box 'after update' event is:

Private Sub cboThemes_AfterUpdate()
Me.cboSubThemes.RowSource = "select strsubtheme from" & _
"tbllistofsubthemes where strtheme=" & _
Me.cboThemes & _
"order by strtheme"

Me.cboSubThemes = Me.cboSubThemes.ItemData(0)

End Sub