Can You Sum Calculated Fields in a Subform?

0
votes

I am trying to total a subform so I can bring the value up to the main form.

In the subform, the user enters: UnitPrice, which is formatted as a Currency with Decimal Places 0 and Quantity, which is formatted as General Number with Decimal Places 0

The subform calculates Extension = [unitPrice]*[Quantity] in a text box called Extension.

I have created an unbound text box in the subform's footer an inserted the expression =Sum([Extension])

I get #Error when I run the subform and, obviously, an error when I pull the value up to the top level form.

This form contains a combo box for data selection and other form based calculations (not calculated fields in tables...learned my lesson there). These are all working.

When I simply type =[Extension] into the unbound text box in footer, I get the expected value.

When I type =Sum([unitPrice]*[Quantity]) I get the sum of everything in the form's underlying table (expected), but I get the correct value (i.e. only the values related to the top level form) when running the top level form.

Is this the expected behavior or does it indicate a design problem with my data base?

Thanks as always.

Edit. Added screenshot of top level form and subform holding details.

enter image description here

1
ms-accesssubformtotals
Aggregate functions must reference fields, not controls. - June7
Is there a better way to accomplish my goal? Calculated fields in the underlying tables appear to cause my earlier issues. - Exiled_In_CA
Can calculate in query and use query as RecordSource. What issue caused by calculated field in table? But =Sum([unitPrice]*[Quantity]) should work just fine. Is it really important to view aggregate calc on form? A report is really best vehicle for this. - June7
Thanks again, @June7. I am building a sales opportunity tracker. Is very useful to enter line items and see values based on factoring (done via combo boxes). I posted an updated photo to give you an idea. I think calculated fields in an underlying table caused design issues where Access was trying to overwrite keys. Also, based on reading I've done, seems tables should just be for data and calcs should be done elsewhere. Summing the factored value (see above) threw an error. Will play around with queries next. - Exiled_In_CA

1 Answers

0
votes

I ended up creating a query - qryItemTotals - to do the required math (Calculate Extension, Ptotal and Factored Value). I then used Dsum in the value and factored value text boxes in the top level form.

=DSum("Extension","[qryItemTotals]","[opportunityID]=" & [opportunityid])

and

=DSum("factoredvalue","[qryItemTotals]","[opportunityID]=" & [opportunityid])

Not sure this is the best way, but it worked for me.