Dsum or Sum question 1

[molly]

I have a main form and a subform, each has their own Qry.
The subform contains Bill Of Material info like Usage% and Usage$.

On the Main form, I would like to have 2 calculated form field for the sum of the Usage% and Usage$.

What is the formula to put into these 2 main form fields?

Up to now, I have found the following to work okay. But it is a Dsum using their respective QRY. Would another method work better? I seem to have to hit a Requery Button (that i made) to force the figures to recalculate whenever i add new info. Here is the equation in the main form for the usage% calculated field. it looks to a Qry for its answer.

=DSum("[Usage%]","qryBomDetail","[FormulaID]=" & [cboFormulaID])

Is there a better way which will reduce my having to hit my Requery button all the time?? Thanks

 

[PHV]

In the Footer section of the subform create 2 textboxes with contol source like this:
=Sum([Usage%])

In the main form change the control source like this:
=Nz([subform control name].Form![name of total control],0)

In the AfterUpdate event procedure of the subform:
Me.Parent.Recalc

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[molly]

You are so smart. Your detailed note worked perfectly. I spent so much time on this and you figured it out so fast.

One of the calcs on the main form acts funny though.
The only difference that i see is that one calc uses the SAME sfrmBomDetail but the calc not working uses two different forms. I have no idea why the 2nd one does not work.

this works:
=Nz([sfrmBomDetail].Form![TxtCur$RawExtended],0)/Nz([sfrmBomDetail].Form!TxtUomUsageTot,0)

this one does not work:
=Nz([sfrmPkgDetail].Form![txtPkgCur$Tot],0)/Nz(frmPriceMain.Form!BatchSizePkg,0)

I tried to put [ ] brackets around frmPriceMain but when i click away, the brackets disappear and i get the above calc in the the text field. Strange.

Thanks if you have time to look.