REPOST: Forms, subforms, comboboxes, & M:M relationships (Easy!)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EcoLeAnne - 15 May 2007 13:03 GMT

Please help! 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, as I desperately need this tool.

tia,

Signature

--
LeAnne

Reply to this Message

Mr. B - 16 May 2007 15:46 GMT

On May 15, 7:03 am, EcoLeAnne <EcoLeA...@discussions.microsoft.com>
wrote:

> Please help! I have 3 entities (Watersheds, Goals, Objectives), all related
> many-to-many. These are represented in my Access XP db as tblWatersheds,
[quoted text clipped - 24 lines]
> --
> LeAnne

LeAnne,

Although you could use a subform with a sub-subform to get the desired
results, I really think that you can do what you want by using your
combo box for selecting the Watershed. Then using the selected
Watershed as the criteria for a record set for a listbox that will
show the list of Goals for the selected Watershed. The you can have a
selection from the Goals listbox that will provide the criteria for
the Objectives listbox that would display the list of objectives for
any selected Goal.

Try setting up the Goal listbox first. Once you have the listbox
displaying the list of Goals for any selected Watershed, then you can
work on the Objectives piece.

Hope this helps to get you started.

Mr. B

Reply to this Message