subform within subform

Question

I have a form which needs to list services provided per member and each service has different goals that need listed under the service.  I have created a subform that shows specific services per member and that subform has a subform that shows goals for each service.  The form will not let me do continuos forms on the subform for service and subform for goals.  How can this be done.  The final outcome needs to look something like

member information

    service a

       goal a

       goal b

       etc

    service b

      goal a

     goal b

     etc

service c

etc.

Thank you.

Answer 1

You'll have to include a Boolean (Yes/No) field in the underlying table and bind the check box to this.  That way you can select rows individually.

---

Ken Sheridan, Stafford, England

Answer 2

I think you are misunderstanding the means of achieving correlated subforms.  A continuous forms view subform cannot be embedded in the detail section of another continuous forms view form, be it a parent form or a subform.  In this case, however, both the services subform and the goals subform are in continuous forms view, and (this is the important point) both are subforms of the main parent form, not one of the other.  The second is correlated with the first via the hidden text box control in the main parent form.  Consequently the goals continuous forms view subform is requeried as you navigate from row to row in the services continuous forms view subform.

---

Ken Sheridan, Stafford, England

Answer 3

thank you for the clarification.  I am now on my way and off dead center.

Answer 4

thank you for your response.  each subform shows the records it needs to but one at a time.  In other words a member might have 5 services and it shows those 5 services one at a time.  Using navigation buttons I can look at each of the 5 one at a time but I want to see all 5 at the same time.  A continuos form does wonders here but when I place the goal subform on the service subform I can no longer use the continuos form option for either subform.  so looking at the goal subform of the service subform the same thing needs to occur.  a service might have 3 goals that need to all be seen at the same time not as I scroll throught with navigation buttons.  Again a continuos form would be very helpful here but it is not an option.  I thought maybe a list box with a concurrent list box for goals but that is not working as i want either.  Thank you for further wisdom.

Answer 5

You need to correlate two subforms, both within the main parent form, so that as you navigate to a row in the first subform the second is requeried to show the rows relating to the current row in the first.  There are a couple of ways of doing this:

1.  In the parent form add a hidden text box whose ControlSource property is a reference to the first subform's primary key, e.g.

=ServicesSubformControl.Form.ServiceID

Set the LinkMasterFields property of the second (Goals) subform control to the name of the hidden text box, e.g. txtServiceIDHidden, and the LinkChildFields property to the name of the corresponding foreign key in the subform's underlying table or query, e.g.ServiceID.

2.  Alternatively base the goals subform on a query with a reference to the first subform's key as a parameter on the ServiceID column, e.g.

Forms!YourParentForm!ServicesSubformControl.Form!ServiceID

and in the first subform's Current event procedure requery the second subform control.

The first method is more usual and more efficient.  I did find I had to resort to the second recently, however, because for some unaccountable reason the first method did not work correctly.  Only adopt the second method as one of last resort, though.

---

Ken Sheridan, Stafford, England