filtering one drop down to another drop down

[Tina Marie]

Hello ... using Access 2003 ...

I have a list of themes, a list of subthemes and a list of subsubthemes ...
when I choose to enter a theme, I use a drop down ... now when I choose my
theme and then want the next field (the subtheme field) to ONLY list the
subthemes that are relevant to that theme and the same for subsubthemes ...

Currently I have 3 tables that 'house' the drop down information for themes,
subthemes and subsubthemes

I also have 3 tables that are 'many' tables to accept my choices of theme,
sub and subsub per record

I can't figure out how to make the subtheme drop down list filter for only
the theme choice I have made ...

Any suggestions ... Thanks, Tina

 

[Jeanette Cunningham]

Hi Tina Marie,
have a look at this link - it is a 'how to' for synchronized combos.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

http://msdn.microsoft.com/en-us/library/bb404901.aspx

 

[Tina Marie]

Hi Jeanette ... thanks so much for getting back to me ... I've printed off
the info and will give it a try ... I'm using 2003 but hopefully the same
concepts will apply!
--
Thanks!!

T. Marie

Hi Tina Marie,
have a look at this link - it is a 'how to' for synchronized combos.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

http://msdn.microsoft.com/en-us/library/bb404901.aspx

 

[Tina Marie]

Hey Jeanette ... tried the code, not working .. it lets me chose from the
first combo box but then the second combo box has nothing in it ... then I
get a Jet engine error ...

The link explains the code but what if I don't have the 'dummy tables' (the
tables that 'house' the themes, subthemes, etc.) set up right or what if the
form with the 2 combo boxes is not accessing a source correctly?

My 3 tables that house the themes ... table 1 (themes) is just a list of
themes, table 2 (subthemes) has field one as the themes, field 2 as the
subthemes (so now field 1 would have dups of course)

The 3 tables that act as the 'child' tables for data capture are linked
table one strtheme to table 2 strtheme (but there is a subtheme field) and so
on ...) and of course are 'lookups' to the 3 tables that 'house' the theme
info.

The query that I used as the 'source' for the test form with the 2 combos on
it ... I tried linking using just the 3 'data capture' tables then with the
'parent' table (a table that captures document title info which may have
multiple themes, etc.)

Does this make any sense at all??

Help ... thanks, Tina
--
Thanks!!

T. Marie

Hi Tina Marie,
have a look at this link - it is a 'how to' for synchronized combos.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

http://msdn.microsoft.com/en-us/library/bb404901.aspx

 

[Jeanette Cunningham]

Yes, getting the tables set up is the most important part of the database.
If you don't get the tables right, the rest becomes extremely difficult.

Something like this should be workable-->

tblTheme
ThemeID -- primary key
Theme - the name of the theme

tblSubTheme
SubThemeID -- primary key
ThemeID -- foreign key related to tblTheme
SubTheme - the name of the sub theme

tblSubSubTheme
SubSubThemeID -- primary key
SubThemeID -- foreign key related to tblSubTheme
SubSubThere - the name of the sub sub theme

One theme can have many sub themes - this is one to many
One sub theme can have many sub sub themes - this is one to many

Do you know how to set up the one to many relationships?
If you need some help with this, there is a tutorial here
http://www.accessmvp.com/strive4peace/


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hey Jeanette ... tried the code, not working .. it lets me chose from the
first combo box but then the second combo box has nothing in it ... then I
get a Jet engine error ...

The link explains the code but what if I don't have the 'dummy tables'
(the
tables that 'house' the themes, subthemes, etc.) set up right or what if
the
form with the 2 combo boxes is not accessing a source correctly?

My 3 tables that house the themes ... table 1 (themes) is just a list of
themes, table 2 (subthemes) has field one as the themes, field 2 as the
subthemes (so now field 1 would have dups of course)

The 3 tables that act as the 'child' tables for data capture are linked
table one strtheme to table 2 strtheme (but there is a subtheme field) and
so
on ...) and of course are 'lookups' to the 3 tables that 'house' the theme
info.

The query that I used as the 'source' for the test form with the 2 combos
on
it ... I tried linking using just the 3 'data capture' tables then with
the
'parent' table (a table that captures document title info which may have
multiple themes, etc.)

Does this make any sense at all??

Help ... thanks, Tina

 

[Tina Marie]

Hi Jeanette ... been away for a few days ... I believe I have the tables set
up this way ... but I will check again ... and yes I've set up the 1 to many
.... haven't turned on 'referential integrity' though yet ... so I'll check
again ... and then I'll send the code as it relates to this set up ...
perhaps you will see I problem that I don't ...

Keep on trying!!