Calculated totals in form from subform

[logopolis]

I have a form with a subform, which was created from an existing form. The form records can have multiple records in the subform attached to it (eg one parent that has multiple children). I wish to group a total from all of the records attached to the individual record and display that total in the main form.(eg if i wanted the total age of all the children linked to the parent)

Any Ideas

many thanks

John

 

[scriverb]

Create a calculated control on your main form and perform a DSum of the field using criteria from your main form.

=DSum("[yourfieldname]", "[yourtablename]", "[recordIDfield] = " & Me![RecordIDcontrolOnMainForm] )

This may have to be tweaked a little depending on the ID field datatype. This is setup for a numeric value. If the ID field is text that the following needs to be used:

=DSum("[yourfieldname]", "[yourtablename]", "[recordIDfield] = '" & Me![RecordIDcontrolOnMainForm] & "'" )

Post back with any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[TheAceMan1]

How are ya logopolis . . . . .

Add an hidden control on the subForm that sums you control of interest. Call the subForm total control [blue]MyTotal[/blue] for now. Set the [blue]Control Source[/blue] property to:

[blue]=Sum([purple]YourControlName[/purple])[/blue].

Then for the control on your main form, set its [blue]Control Source[/blue] to myTotal:

[blue]=[purple]YourSubFormName[/purple]![purple]YourTotalControlName[/purple][/blue]

See Ya! . . . . . .

 

[logopolis]

Hi Bob

Me again, sorry. I see the logic to your answer. The only difference I have is that the data I wish to total is not stored in a table, it is calculated in the child table. I therefore have multiple calculated field in a table which i wish to sum which are linked to one record in a parent table.

Many thanks

John