How can I build a subform within a subform.

[Gina]

I want to build a subform within a subform. I have the following
tables.

tbl-Clients
ClientCode (Pri Key)
ClientName

tbl-Jobs
JobNumber (Pri Key)
JobName
ClientCode

tbl-TimeCards
Time-ID (Pri Key)
Date
Time On
Time Off
JobNumber
TaskCode

tbl-Tasks
TaskCode (Pri Key)
TaskName
TaskRate

tbl-Expenses
Expenses-ID (Pri Key)
Date
ExpAmount
ExpDetails
JobNumber

The three levels of the form I will call Child, Parent and
Grandparent.

I need to have the 'Grandparent' form built from 'tbl-Clients' with
the record in Form view. This is to enable the Parent generation
records to be displayed in Datasheet view.

The Parent Form/Subform is to be built from 'tbl-Jobs'. Then when a
'Parent' record is selected, the respective child records are to be
displayed in Datasheet view.

I want to have the Child form built from a combination of 'tbl-Tasks'
and 'tbl-TimeCards' without repeating 'TaskCode'.

I have been working on this for days and in the process learnt quite a
lot, ... .but not how to do it.

Appreciate any help you may offer.

With thanks,
Gina

 

[JK]

Hi Gina,

Your Child (the middle one) form has to be a single form too, only single
forms can have sub-form. the sub-form can be either single or continuous

Regards/Jacob

|I want to build a subform within a subform. I have the following
| tables.
|
| tbl-Clients
| ClientCode (Pri Key)
| ClientName
|
| tbl-Jobs
| JobNumber (Pri Key)
| JobName
| ClientCode
|
| tbl-TimeCards
| Time-ID (Pri Key)
| Date
| Time On
| Time Off
| JobNumber
| TaskCode
|
| tbl-Tasks
| TaskCode (Pri Key)
| TaskName
| TaskRate
|
| tbl-Expenses
| Expenses-ID (Pri Key)
| Date
| ExpAmount
| ExpDetails
| JobNumber
|
| The three levels of the form I will call Child, Parent and
| Grandparent.
|
| I need to have the 'Grandparent' form built from 'tbl-Clients' with
| the record in Form view. This is to enable the Parent generation
| records to be displayed in Datasheet view.
|
| The Parent Form/Subform is to be built from 'tbl-Jobs'. Then when a
| 'Parent' record is selected, the respective child records are to be
| displayed in Datasheet view.
|
| I want to have the Child form built from a combination of 'tbl-Tasks'
| and 'tbl-TimeCards' without repeating 'TaskCode'.
|
| I have been working on this for days and in the process learnt quite a
| lot, ... .but not how to do it.
|
| Appreciate any help you may offer.
|
| With thanks,
| Gina

 

[Guest]

Gina:

To have the 'parent' subform in datasheet or continuous form view you'll
need to put two separate correlated subforms for this and the 'child' subform
in the main 'grandparent' form. First create the forms for the two subforms
and embed them in the main form, basing the 'child' subform on a query
joining the two tables on the TaskCode columns. Include only one of the
TaskCode columns in the query's returned columns.

Link the 'parent' subform to the main form in the usual way on the
ClientCode columns.

To correlate the 'child' and 'parent' subforms add an unbound text box,
txtJobNumber to the main 'grandparent' form, set its Visible property to
False (No in the properties sheet) to hide it, and as its ControlSource
property reference the 'parent' form's current JobNumber, e.g.

=sfrJobs.Form.JobNumber

where sfrJobs is the name of the subform control. i.e. the control in the
main form housing the subform, not the name of its underlying source form
object (unless both have the same name of course).

Now set the LinkMasterFields property of the 'child' subform control to the
name of the hidden control, txtJobNumber, and its LinkChildFields property to
JobNumber. As you move from row to row within the jobs subform the
time-cards/tasks subform should show only the rows for the currently selected
job.

Ken Sheridan
Stafford, England