Forms, subforms, comboboxes, and M:M relationships

[EcoLeAnne]

I have 3 entities (Watersheds, Goals, Objectives), all related many-to-many.
These are represented in my Access XP db as tblWatersheds, tblGoals,
tblObjectives, and tblWatershedsGoalsObjs as the junction table to resolve
the multiple M:M relationships. E.g., each watershed can have 1 or more
goals. Each goal can belong to 1 or more watersheds. Ea. goal can have 1 or
more objectives. Each objective can accomplish 1 or more goals. The resolver
table contains a pk (WGO_ID) and the fk's from the other 3 tables (WSID, GID,
OID). Incidentally, this table will eventually be joined with a fourth
entity, tblRecommendations...each unique combination of watershed + goal +
objective will be related 1:M to assorted recommended actions, policy
changes, etc..

I would like to create a form with subform to enable me to view a Watershed
Name, a list-or combo-box to view & select from the goals associated with the
selected watershed, and a continuous subform that displays all the objectives
associated with the selected watershed + goal. Child's play to an experienced
guru, no doubt. Unfortunately, I'm very experienced at db design & working
with tables & queries, but have almost no acquaintance with forms. I can make
the form with subform, I can make the combobox that looks up the values I
want, but I can't seem to tie all of the controls together on a main form.
Any assistance would be appreciated.

tia,

 

[Steve Schapel]

LeAnne,

Try this for a start...

Set the Row Source of the combobox to something equivalent of this...

SELECT Goals.GID, Goals.GoalName
FROM Goals INNER JOIN WatershedsGoalsObjs ON Goals.GID =
WatershedsGoalsObjs.GID
WHERE WatershedsGoalsObjs.WSID = [Forms]![YourForm]![WSID]

Set the LinkMasterFields and LinkChildFields properties of the subform to:
[WSID];[GID]
(assumes the combobox's name is GID)

The Record Source of the main form is the Watersheds table.

There, that should take care of it.