Design 3 1:M Levels in a Form (s?)

[Guest]

Hi, I am doing a favor for a nonprofit organization and building a db for
them in MS Access 2003.

I am clear on the table relationships:
1st one-to-many (1:M) Level: Each [client] has zero or more [questions];
each [question] applies to only one [client].
2nd 1:M Level: Each [question] has zero or more [recommendations] [rec];
each [rec] applies only to one [question]. If a [rec] applies to many
[questions], the [rec] will be repeated.
3nd 1:M Level: *Each [rec] has zero or more [results]; each [results]
applies only to one [rec]. If a [result] applies to many [rec], the [result]
will be repeated.

Now I have 3 questions:
Question 1: Can you show these 3 Levels of 1:M in one form?
Question 2: Or do you have to use one form per 1:M Level?

What I have done so far:
In the form, I put [client] in the Form Header and [questions] in a subform
in the Detail section.

Showing the 2nd & 3rd 1:M Level on the same form is where I am stuck.

Question 3: If it has to be in multiple forms, how do you take the user
from the client form, having chosen one of the questions, to the next form
keeping the one question and then showing the many recommendations for that
question. (Then of course I would apply this to showing the 3rd 1:M level.)

I really appreciate your time, and effort in replying and enlightening my
mind on how to design the form!

Thank you very much! LifeLovin (Maryanne)

 

[Guest]

An official expert may have a better answer, but as a self-taught
do-it-yourselfer, i would suggest trying to build it backward...instead of
starting from level 1 and adding level 2, start by creating a form with level
2 and adding the subform for level 3. THEN use THAT form as a subform to
build level 1 to level 2. Hope that's at all useful.

--robin a.k.a. rbyteme

 

[Baz]

You can nest subforms, but you can only go down two levels below the main
form, so that isn't going to work for you (plus there are other reasons why
it might not be a great idea).

Sychnronised subforms might be what you want. On your main form, drop a
subform control for your questions subform (as it sounds like you have
already done). Also, put a text box on the main form, set it's name to
txtQuestion, and set it's Visible property to No.

In your questions form code the Current event as follows:

Private Sub Form_Current()
Me.Parent!txtQuestion = [question_number]
End Sub

I have assumed a primary key named question_number, replace this with the
correct name.

Now, back on the main form (NOT the questions form!), drop another subform
control for the recommendations subform. Link this subform on
question_number (or whatever) as the child field, but link it to (and here's
the clever bit!) the text box txtQuestion as the master field.

You now have two subforms at the same level (i.e. on the main form) but they
are synchronised: when you navigate to a question in the questions subform,
the recommendations subform will automatically show the recommendations for
that question.

Add another subform to the main form for the results, and synchronise it
with the recommendations subform using the same technique.

Hi, I am doing a favor for a nonprofit organization and building a db for
them in MS Access 2003.

I am clear on the table relationships:
1st one-to-many (1:M) Level: Each [client] has zero or more [questions];
each [question] applies to only one [client].
2nd 1:M Level: Each [question] has zero or more [recommendations] [rec];
each [rec] applies only to one [question]. If a [rec] applies to many
[questions], the [rec] will be repeated.
3nd 1:M Level: *Each [rec] has zero or more [results]; each [results]
applies only to one [rec]. If a [result] applies to many [rec], the [result]
will be repeated.

Now I have 3 questions:
Question 1: Can you show these 3 Levels of 1:M in one form?
Question 2: Or do you have to use one form per 1:M Level?

What I have done so far:
In the form, I put [client] in the Form Header and [questions] in a subform
in the Detail section.

Showing the 2nd & 3rd 1:M Level on the same form is where I am stuck.

Question 3: If it has to be in multiple forms, how do you take the user
from the client form, having chosen one of the questions, to the next form
keeping the one question and then showing the many recommendations for that
question. (Then of course I would apply this to showing the 3rd 1:M level.)

I really appreciate your time, and effort in replying and enlightening my
mind on how to design the form!

Thank you very much! LifeLovin (Maryanne)

 

[Joan Wild]

If you have set the relationships among these tables in the relationship window, the form wizard will do it for you.

When you run the wizard, choose the client table and its fields, then instead of hitting Next, select the second table, and its fields, and then the third table and its fields. Then hit Next, and follow the steps.

 

[Larry Linson]

You can nest subforms, but you can only go down two levels below the main
form, so that isn't going to work for you (plus there are other reasons why
it might not be a great idea).

That limit was removed... Access 2000, I think. If you're still using
Access 97 or older, what you describe is the way it operates. You can now
add enough levels of subform to confuse even experienced users.

I fear what the original poster would like to do is to have Subform Controls
containing Continuous Forms View Forms at all levels, and only the lowest
level of Subform can contain a Continous Forms View Form.

Larry Linson
Microsoft Access MVP

 

[Baz]

That limit was removed... Access 2000, I think. If you're still using
Access 97 or older, what you describe is the way it operates. You can now
add enough levels of subform to confuse even experienced users.

I fear what the original poster would like to do is to have Subform Controls
containing Continuous Forms View Forms at all levels, and only the lowest
level of Subform can contain a Continous Forms View Form.

Larry Linson
Microsoft Access MVP

I suspect you are right, but synchronised subforms are a good alternative.
Thanks to the earlier poster for pointing out how to do this with the
wizard, I didn't know that.

 

[Larry Linson]

I suspect you are right, but synchronised subforms
are a good alternative.

I haven't had the need very often, but have used subforms on the same level
in a synchronized manner to do this. Another alternative that may serve the
same or similar purpose, but has a little steeper learning curve, is to use
the api for the List View Control.

I try to avoid the hassle of distributing ActiveX controls, but using
Windows built-in features via the API doesn't complicate your distribution
and installation.

Larry Linson
Microsoft Access MVP