Sum calculation issue in master form

  • Thread starter Thread starter Josh C Y
  • Start date Start date
J

Josh C Y

Hi there,

I am constructing recipe db using ms access. I use s sub form
(datasheet default view) to display the ingredient list and percentage,
cost, etc. within a master form.

In the sub form, a cost textbox calculate values from other fields that
convert ingredient cost to cost per LB bases. I use the "IIf(....)"
statement in the control source of that textbox to do the
job.

However, the textbox in the master form that sum the ingredient cost
based on the text box in the sub form gives me an error. The Access
keeps changing the content inside the Sum()statement in the control
source of the textbox to something else and won't do the calculation
and show ERROR.

Can someone tell me what can be the cause?

Thanks,

Josh
 
What is the equation that you have in the control source of the textbox on
the main form?
 
The subform txtbox control source equation is like this:

=IIf([Cost_UOM]="KG",[TxCost]*2.2046*[TxtPercent]/100,IIf([Cost_UOM]="GAL",[TxCost]/3.7854/[TxDensity]*2.2046*[TxtPercent]/100,IIf([Cost_UOM]="LB",[TxCost]*[TxtPercent]/100,IIf([Cost_UOM]="EACH",[TxCost],IIf([Cost_UOM]="g",[TxCost]*1000*2.2046*[TxtPercent]/100,IIf([Cost_UOM]="fl_oz",[TxCost]/0.02957373/[TxDensity]*2.2046*[TxtPercent]/100,IIf([Cost_UOM]="L",[TxCost]/[TxDensity]*2.2046*[TxtPercent]/100,IIf([Cost_UOM]="oz",[TxCost]*16*[TxtPercent]/100,"NA"))))))))

where Cost_UOM is txtbox for Unit of Measure, TxCost is txtbox for cost
value, TxtPercent is txtbox for % of that ingredient.

Thanks,

Josh C Y
 
The equation in the subform textbox looks ok and, I assume from what you've
said so far, that it is returning the correct value. Is this textbox on each
record in the subform? If so, what are you trying to "total" on the main
form, the sum of each of these in the subform's recordset? What is the
equation in the main form's textbox?
 
The subform shows records (ingredients) that match the formula ID. In
other word, only ingredients under selected formula will showup in the
subform when a formula ID is selected.

The mainform textbox equation is like this:
=Sum(FFormulaPerc_Sub.Form!TxtCosteachLB), where FFormulaPerc_Sub is
the subform name and TxtCosteachLB is the txtbox name.

If I use the simple equation like the foolwoing one in the subform
txtbox then the mainform sum txtbox works:
=[lb_Cost]*[Percent]/100

Thanks,

Josh
 
Josh,

I'm at a loss as to why it will work with one equation in the subform but
not another. Can you send me a copy of the file? Remove any "private" data,
do a compact, then zip the file. Send it to (e-mail address removed).
 
Josh,

I moved the calculation from the control on the subform to the query feeding
the subform. I then bound the control on the subform to this calculated
field in the query. I was then able to Sum this calculated field in the
footer of the subform. Next, I set the Sum textbox on the main form equal to
the Sum textbox in the footer of the subform.
 
Back
Top