Using Linked Combo Boxes with a subform

I am working in Access 2007. I have a Client table (with unique ID), and a
separate ClientGoals table - one to many between the two - which contains
records identifying the client ID and a goal ID, plus date set, date reached,
outcome.

There are dozens (100+) of goals but each belongs to one or more categories,
only 12 categories. I have a separate table for Categories (each with unique
CatID) and another separate Goals table (with unique GoalID). There is also a
separate crosswalk table containing records consisting of only the CatID and
the GoalId - showing which goals go to which categories.

I used Feddema's linked combo boxes method (MS Access 2002 Inside and Out,
2002, p. 205) to create a form where I can select a category and then see
only the goals that pertain to that category. All this works [separately] so
far.

My database has several separate main form-subforms linked to the "parent"
Client table records. Each subform allows input of different types of info
(e.g., emergency contacts, veteran status, children, jobs, training programs,
learning needs, test scores). I want one main form-subform to allow entering
the client's goal(s). As with the other main forms-subforms, I want to click
a "GOALS" button on the Client Main Form, which opens the main form-subform
["child" is ClientGoals Subform]. I want the subform to allow me to select a
category first and then a goal that fits into that category, then finish the
dates and outcome fields. I want to be able to add as many records
representing individual goals, selecting them first by category, as the
client states.

My other main forms-subforms carry the Client ID over to the subform records
(e.g., ClientEmergencyContatcs Subform). I can set up this ClientGoal Subform
to enter individual goals - but not by selecting the category first and then
the subset of eligible goals.

I have been fiddling with this for days, trying to see how to make nested
subforms or something else that allows me to select a category first and then
the goal, then go ahead and fill in the other info for the ClientGoals table.
I am stumped.

Can anyone please, please help me figure this out? I have my fingers crossed!

L B Tague

I have a sample database using listboxes, that opens a form in the manner
you desire. You could also have a subform linked to that form, or if the
subform was unbound, use the listbox (or combo, they work practically the
same) to get to the record that you desi

http://www.accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"LBRT" wrote in message
...
I am working in Access 2007. I have a Client table (with unique ID), and a
separate ClientGoals table - one to many between the two - which contains
records identifying the client ID and a goal ID, plus date set, date
reached,
outcome.

There are dozens (100+) of goals but each belongs to one or more
categories,
only 12 categories. I have a separate table for Categories (each with
unique
CatID) and another separate Goals table (with unique GoalID). There is
also a
separate crosswalk table containing records consisting of only the CatID
and
the GoalId - showing which goals go to which categories.

I used Feddema's linked combo boxes method (MS Access 2002 Inside and Out,
2002, p. 205) to create a form where I can select a category and then see
only the goals that pertain to that category. All this works [separately]
so
far.

My database has several separate main form-subforms linked to the "parent"
Client table records. Each subform allows input of different types of info
(e.g., emergency contacts, veteran status, children, jobs, training
programs,
learning needs, test scores). I want one main form-subform to allow
entering
the client's goal(s). As with the other main forms-subforms, I want to
click
a "GOALS" button on the Client Main Form, which opens the main
form-subform
["child" is ClientGoals Subform]. I want the subform to allow me to select
a
category first and then a goal that fits into that category, then finish
the
dates and outcome fields. I want to be able to add as many records
representing individual goals, selecting them first by category, as the
client states.

My other main forms-subforms carry the Client ID over to the subform
records
(e.g., ClientEmergencyContatcs Subform). I can set up this ClientGoal
Subform
to enter individual goals - but not by selecting the category first and
then
the subset of eligible goals.

I have been fiddling with this for days, trying to see how to make nested
subforms or something else that allows me to select a category first and
then
the goal, then go ahead and fill in the other info for the ClientGoals
table.
I am stumped.

Can anyone please, please help me figure this out? I have my fingers
crossed!

L B Tague