Totaling multiple calcualted fields in a subform

[logopolis]

I have a form (call it [parentdetails]) and this form has a subform (call it [childdetails]). There are therefore several children attched to each parent. The subform has been set up from an original form so is displayed as a straightforward form not a list as it would if access had auto created the form.

The subform has a field in it which is the total(Sum) of a field in a subform of that subform.(call this subform, [childpurchases]. This subform is a list and so sum is no problem by hiding the calculation the the footer and then refferencing it in the subform [childdetails].

I would like to add together all of these total fields for all the children attched to each parent. So the individual parent would have a total field which would show the total owed for all of their own children. This field which is being added obviously only exist in the subform[childdetails] and not in any table.

If anyone can follow this, can you help.
Many thanks

John

 

[PHV]

Take a look at the DSum function.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[logopolis]

Someone else mentioned Dsum function to me but the problem I had was that it didn't seem geared to acccessing data from a form and wanted it from a table or query, so I didnt quite know how to apply it.

 

[PHV]

Aren't yours Total(Sum) in childpurchases based somehow on column(s) in table(s) joined to parent table ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[logopolis]

they are bassed on one table but only linked to the parent table through the child table, if u see what I mean.

 

[PHV]

No common field between childpurchases and parent ?
If childdetails is mandatory to get all chidpurchases for a given parent, you may consider a recordset with a sql like this:
sql = "SELECT Sum(ChidPurchase.ColumName) FROM ChildDetail" _
& " INNER JOIN ChildPurchase ON ChildDetail.ID=ChildPurchase.ChildID" _
& " WHERE ChildDetail.ParentID=" & Me!NameOfParentIDcontrol.Value

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[logopolis]

No there is no common field. The Childpurchase is lined to the child and the child to the parent.

Thanks for that code, i can see how that would work, just one problem, I have no idea where to put it. You couldn't spell it out for me. Sorry for being so dim?

 

[PHV]

In the Current event procedure of the parent form:
Dim db as DAO.database
Dim rs as DAO.recordset
Set db = CurrentDB
sql = "SELECT Sum(ChidPurchase.ColumName) AS Total FROM ChildDetail" _' see previous post
Set rs = db.OpenRecordset(sql)
Me!NameOfTotalControl.Value = Nz(rs("Total"), 0)
rs.Close
Set rs = Nothing
Set db = Nothing

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[logopolis]

cheers for that will give it a try