Calculated totals from Subform, normal method is not working any more

[RECrerar]

Hi this is similar to the multitude of posts of posts about getting totals form a subform onto the main form, in that, that is exactly what I am trying to do.

Initially I had a subform with two fields, Week and Cost.

In the footer of the subform I had a textbox called [TotalCost] with the expression =Sum([Cost])

Then on the main form I had a textbox with the expression
=[sfrmTPCosts_byWeek].Form!TotalCost

This all worked fine!

Since then I have modified the subform to have another field of data which is calculated from the [Cost] field on the subform and two fields from the main form. The specific expression is

=Round(([Cost]/Forms!frmApproved_PT!Rel_Total)*Forms!frmApproved_PT!Total_Hours,2)

This works fine to calculate the values in the extra field, the field is currently not enabled but enabling seems to make no difference to the issue I am having.

On the main form I want the TotalCost that I had before and also a total of the hours (the extra field that I have just created)

So in the footer of the subform I now have two textboxes:

TotalCost =Sum([Cost])
TotalHours =Sum([Hours]) The new Fieldname

then on the main form I have two text boxes:

=[sfrmTPCosts_byWeek].Form!TotalCost
=[sfrmTPCosts_byWeek].Form!TotalHours

Neither of these are working anymore. Can anyone think why?

I think it may be something to do with working with calculated values which may explain why the total hours nolonger works but I can not see why the TotalCost has stopped working as I haven't changed this at all.

If anyone has any suggestion it would be greatly appreciated as it is driving me nuts.

 

[MarkK]

- My general philosopy is to get each object to handle it's own data, and to this end, I'd consider doing the main form calculation independent of the subform.
- Say your main form is fInvoice and your subform is fInvoiceDetail and they're linked on InvoiceID. To get a total on the main form set the control source of a text box to something like...

=DSum("Quantity * UnitCost", "tInvoiceDetail", "InvoiceID = " & Nz([InvoiceID], 0))

- So if your subform breaks, or doesn't load, or has an error, or your main form has a new record in it, etc..., your still get a meaningful result in the main form.

 

[missinglinq]

If absolutely nothing has changed, I don't see why

=[sfrmTPCosts_byWeek].Form!TotalCost

has stopped working either. As to the calculated field, you can't use Aggregate functions directly against a calculate field. Simply said, if you have

CalculatedField = A*B

you can't do

= Sum(CalculatedField)

you have to use

= Sum(A*B)

 

[RECrerar]

Thanks for the responce, sorry for the slow reply, i'm sure nothing has changed except that I use the weekly cost field in the other calculation, but didn't think it should affect that.

Thanks for the pointers on the otehr field, i will try that and get back to you