Repost: Please help w/M:M form/subform problem, weeping w/rage

[EcoLeAnne]

No lie, I am literally weeping in frustration. I apologize for the repeated
postings. I swear, I have done my best to solve this on my own, I have
attempted to implement 1 suggestion I received via this ng (unbound form
w/combo & listbox instead of subform), I have repeatedly built up and torn
down form after form, I have rent my garments and gnashed my teeth, and STILL
I cannot accomplish what would seem to be a fairly simple form design.

Other than early onset Alzheimer's, the only reason I can think of for my
difficulties would be the trickiness of multiple many-to-many relationships
in my db. I have 4 tables, 3 of which are related M:M from a relational
database standpoint: tblWatersheds, tblGoals, tblObjectives. The 4th table,
tblWatershedsGoalsObjs, is the "resolver." The "resolver" contains ONLY its
own pk and the fk's from the other tables...no other fields. All data are
largely static...IOW, I'm not doing data entry, I just want to view stuff in
the tables in a certain way. Now, from a human standpoint, the entity
relationships are less complex: ea. watershed has one or more goals, ea. of
which have one or more objectives associated with them. That is the point of
view from which I'm trying to approach this form. What I want is something
like the Customer Orders form in the N'winds db: a main form that displays
Watersheds; some kind of control (combobox, listbox, subform...it doesn't
matter) that is linked to the main form and displays the goals assigned to
the selected watershed when clicked or whatever; and a second control
(preferably a subform) linked to the main form AND the goal control that
lists the objectives associated with ea. selected watershed + goal. Every!
Single! example I have found in books and on the www is based on 1:M
relationships between the tables; none of the example scenarios seem to work
with M:M. I assumed that once I set up my relationships correctly (and I'm
fairly sure they are), Access would be able to figure out what to do. Boy,
was I wrong. Please, pleeeeaaaassssse help. I'm getting quite desperate.

Many tia,

 

[Carl Rapson]

No lie, I am literally weeping in frustration. I apologize for the
repeated
postings. I swear, I have done my best to solve this on my own, I have
attempted to implement 1 suggestion I received via this ng (unbound form
w/combo & listbox instead of subform), I have repeatedly built up and torn
down form after form, I have rent my garments and gnashed my teeth, and
STILL
I cannot accomplish what would seem to be a fairly simple form design.

Other than early onset Alzheimer's, the only reason I can think of for my
difficulties would be the trickiness of multiple many-to-many
relationships
in my db. I have 4 tables, 3 of which are related M:M from a relational
database standpoint: tblWatersheds, tblGoals, tblObjectives. The 4th
table,
tblWatershedsGoalsObjs, is the "resolver." The "resolver" contains ONLY
its
own pk and the fk's from the other tables...no other fields. All data are
largely static...IOW, I'm not doing data entry, I just want to view stuff
in
the tables in a certain way. Now, from a human standpoint, the entity
relationships are less complex: ea. watershed has one or more goals, ea.
of
which have one or more objectives associated with them. That is the point
of
view from which I'm trying to approach this form. What I want is something
like the Customer Orders form in the N'winds db: a main form that
displays
Watersheds; some kind of control (combobox, listbox, subform...it doesn't
matter) that is linked to the main form and displays the goals assigned to
the selected watershed when clicked or whatever; and a second control
(preferably a subform) linked to the main form AND the goal control that
lists the objectives associated with ea. selected watershed + goal. Every!
Single! example I have found in books and on the www is based on 1:M
relationships between the tables; none of the example scenarios seem to
work
with M:M. I assumed that once I set up my relationships correctly (and I'm
fairly sure they are), Access would be able to figure out what to do. Boy,
was I wrong. Please, pleeeeaaaassssse help. I'm getting quite desperate.

Many tia,

I didn't see your original post, so I'm making an assumption: each
watershed, goal, and objective has a numeric primary key along with the text
value it represents. If this assumption is wrong, you'll need to modify the
following appropriately. For this example, let's call these fields
WatershedID and WatershedName, GoalID and GoalName, ObjectiveID and
ObjectiveName. Be sure to use your own actual names in place of these. The
IDs should be the FK values in tblWatershedsGoalsObjs.

Place an unbound combo box on your main form (let's call it cboGoals). Set
the Bound Column property to 1, the Column Count property to 2, and the
Column Widths property to 0";1" (or whatever width you need to display the
GoalName field). In the Form_Current event of your main form, populate
cboGoals with the records from tblWatershedsGoalsObjs for the goals that
match the current watershed:

Me.cboGoals.RowSource = "SELECT
tblWatershedsGoalsObjs.GoalID,tblGoals.GoalName FROM tblWatershedsGoalsObjs
INNER JOIN tblGoals ON tblWatershedsGoalsObjs.GoalID=tblGoals.GoalID WHERE
tblWatershedsGoalsObjs.WatershedID=" & Me.WatershedID

Note we're concatenating the current WatershedID onto the SQL string, so
cboGoals should only display those goals with an entry in
tblWatershedsGoalsObjs for the watershed displayed.

Create up a query that joins tblWatershedsGoalsObjs to tblObjectives on the
ObjectiveID, and include the ObjectiveName field from tblObjectives along
with the three FK values from tblWatershedsGoalsObjs. Something like:

SELECT
tblWatershedsGoalsObjs.WatershedID,tblWatershedsGoalsObjs.GoalID,tblWatershedsGoalsObjs.ObjectiveID,tblObjectives.ObjectiveName
FROM tblWatershedsGoalsObjs INNER JOIN tblObjectives ON
blWatershedsGoalsObjs.ObjectiveID=tblObjectives.ObjectiveID;

Let's call this query qryWatershedsGoalsObjs. Your subform should be based
on this query, with a default Record Source something like:

SELECT * FROM qryWatershedsGoalsObjs ORDER BY
WatershedID,GoalID,ObjectiveID

By default, also make the subform control hidden (set the Visible property
to False).

Now, in the AfterUpdate event of cboGoals, make the subform control visible
and change its Record Source:

Me.nameofsubformcontrol.Form.Visible = True
Me.nameofsubformcontrol.Form.RecordSource = "SELECT * FROM
qryWaterShedsGoalsObjs WHERE WatershedID=" & Me.WatershedID & " AND GoalID="
& Me.cboGoals & " ORDER BY ObjectiveName"

I don't know if this covers everything, but hopefully it's enough to get you
started and give you some ideas.

Carl Rapson

 

[Mr. B]

No lie, I am literally weeping in frustration. I apologize for the repeated
postings. I swear, I have done my best to solve this on my own, I have
attempted to implement 1 suggestion I received via this ng (unbound form
w/combo & listbox instead of subform), I have repeatedly built up and torn
down form after form, I have rent my garments and gnashed my teeth, and STILL
I cannot accomplish what would seem to be a fairly simple form design.

Other than early onset Alzheimer's, the only reason I can think of for my
difficulties would be the trickiness of multiple many-to-many relationships
in my db. I have 4 tables, 3 of which are related M:M from a relational
database standpoint: tblWatersheds, tblGoals, tblObjectives. The 4th table,
tblWatershedsGoalsObjs, is the "resolver." The "resolver" contains ONLY its
own pk and the fk's from the other tables...no other fields. All data are
largely static...IOW, I'm not doing data entry, I just want to view stuff in
the tables in a certain way. Now, from a human standpoint, the entity
relationships are less complex: ea. watershed has one or more goals, ea. of
which have one or more objectives associated with them. That is the point of
view from which I'm trying to approach this form. What I want is something
like the Customer Orders form in the N'winds db: a main form that displays
Watersheds; some kind of control (combobox, listbox, subform...it doesn't
matter) that is linked to the main form and displays the goals assigned to
the selected watershed when clicked or whatever; and a second control
(preferably a subform) linked to the main form AND the goal control that
lists the objectives associated with ea. selected watershed + goal. Every!
Single! example I have found in books and on the www is based on 1:M
relationships between the tables; none of the example scenarios seem to work
with M:M. I assumed that once I set up my relationships correctly (and I'm
fairly sure they are), Access would be able to figure out what to do. Boy,
was I wrong. Please, pleeeeaaaassssse help. I'm getting quite desperate.

Many tia,

Hi, LeAnne,

This is Mr. B again. Sorry, if I did not provide enough imformation
to really help you in your previous posting.

Let's see if we can't just take this one step at a time.

First, I believe you stated that you were able to create the list of
Watersheds in a combo box. Does this combo box have as it's bound
column the PK of the Watershed table? It must.

Try bringing up your form and make a selection from your Watersheds
combo box.

Now, design a query that will return the list of goals for the
selected Watershed. To create this query you need your "Resolver"
table and the "tblGoals" table linked by the PK in the "tblGoals"
table to the FK to that table in the "resolver" table. Then for your
criteria, use the "Expression Builder" to create an expression that
references your combo box on your form as the criteria in the
"Resolver" table for the FK of the "tblWatersheds" table. Be sure to
inlcude the PK the goal from the "tblGoals" table in your query.

Try running the query. It should produce all of the goals that are
realated to the selectled Watershed.

When your query returns the expected data, you can then use this query
as the record source for either a subform or a list box, which ever
suits your needs best.

You will then use this same type of combination to return the list of
objectives. You will need the "Resolver" table and the
"tblObjectives" table in the query. In this query you will have your
criteria for the FK in the "Resolver" table to be refering the the
value of the selected record from either your subform or listbox.

I sincerely hope this helps you get moving on your project.

Mr. B

 

[EcoLeAnne]

Hi Carl,

Thanks so much for your reply. Your assumptions are largely correct re: my
db entities, attributes, and key fields. And it seems I was sorta-kinda-maybe
heading in the right direction. I had a main form based on tblWatersheds. I
had a query joining tblWatersheds to tblGoals by way of
tblWatershedsGoalsObj, which I was attempting to use as the Record Source for
my first control rather than populating it from the main form's On Current
event; but the SQL was almost identical to your string. I had a second query
joining tblWatershedsGoalsObjs to tblObjectives on which to base the subform.
I had a requery sub for the AfterUpdate event of the first control. What
threw me (and keeps throwing me. Hard.) was figuring out the linking parent
and child fields, and the correct syntax (e.g. how to refer to a control on
the main form if you're in subform 1, etc.). I re-created the form using your
method step by step, and it seems to be working except for one small flaw:
when I first open the form, a MsgBox pops up prompting the user for a
parameter. I'm thinking it might be a "sequencing" issue...that is, one of
the subs is trying to run before the form is fully open & a selection has
been made? Does that make any sense? In any case, the form is now working
very close to the way I wanted it to, and that's a HUGE improvement.

Thank you very, VERY much for your assistance. I owe you a virtual beer!

Best,

 

[Carl Rapson]

Hi Carl,

Thanks so much for your reply. Your assumptions are largely correct re: my
db entities, attributes, and key fields. And it seems I was
sorta-kinda-maybe
heading in the right direction. I had a main form based on tblWatersheds.
I
had a query joining tblWatersheds to tblGoals by way of
tblWatershedsGoalsObj, which I was attempting to use as the Record Source
for
my first control rather than populating it from the main form's On Current
event; but the SQL was almost identical to your string. I had a second
query
joining tblWatershedsGoalsObjs to tblObjectives on which to base the
subform.
I had a requery sub for the AfterUpdate event of the first control. What
threw me (and keeps throwing me. Hard.) was figuring out the linking
parent
and child fields, and the correct syntax (e.g. how to refer to a control
on
the main form if you're in subform 1, etc.). I re-created the form using
your
method step by step, and it seems to be working except for one small flaw:
when I first open the form, a MsgBox pops up prompting the user for a
parameter. I'm thinking it might be a "sequencing" issue...that is, one of
the subs is trying to run before the form is fully open & a selection has
been made? Does that make any sense? In any case, the form is now working
very close to the way I wanted it to, and that's a HUGE improvement.

Thank you very, VERY much for your assistance. I owe you a virtual beer!

Best,
--
LeAnne


"Carl Rapson" wrote:

<snipped>

Your guess about the source of your final problem may be correct, as the
subform generally loads before the parent form does. You say it is prompting
you for a parameter; what is it prompting for? Is the prompt for something
in the subform? That should give you a clue as to where the problem is.

One way to fix this might be to remove the default Record Source for the
subform (clear the Record Source property). This shouldn't be a problem
since you are setting the Record Source yourself when you need it.
Similarly, it could be a problem in cboGoals, if you set its Row Source in
the properties window. You should clear that Row Source also, again since
you are setting it yourself when you need it.

Glad it's finally starting to come together.

Carl Rapson