Sum an Unbound Calculated Field in a Microsoft Access Subform
Question details
Display the sum of calculated item totals from a continuous subform on an invoice's main form.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Each invoice line multiplies a quantity by the latest matching item rate, and the main form needs the combined total.
- Observed behavior
- The desired invoice total should include each line's calculated quantity and latest matching rate.
Open the invoice form and subform in Design View and confirm the quantity field, item ID field, and subform control names. Create or verify a query that returns one latest rate per item, using a date or other explicit ordering field and a tie-breaker if needed.
Calculate the total in the subform footer and reference it from the main form
Use this approach to sum the underlying quantity and rate values without looping through the subform recordset.
The example assumes qry_LatestSalesRate returns exactly one latest rate per numeric InvItem. Replace the query, field, and control names with those in your database. DLast does not guarantee the most recent rate unless the data source itself identifies that record.
Open the subform in Design View. If needed, use Design > Form Header/Footer to show the footer, then add a text box to the Form Footer and name it txtSum.
In the text box's Property Sheet, set Control Source to: =Sum(Nz([QTY],0)*Nz(DLookup("RateSale","qry_LatestSalesRate","InvItem=" & Nz([ItemID],0)),0)). This treats a missing quantity or matching rate as zero.
In the main form's Design View, add a text box and set its Control Source to =[SubformName].Form!txtSum. Replace SubformName with the name of the subform control on the main form, which may differ from the subform object's name.
Open the main form in Form View, check several line items against their latest rates, and verify that the displayed total changes as expected when you edit or filter the subform.

Calculate invoice totals in WPS Spreadsheets
For spreadsheet-based invoices, WPS Office is a free, lightweight alternative with a familiar interface and Microsoft Office format compatibility for an easier transition. It does not fix or run Microsoft Access forms.

Frequently Asked Questions
Can I sum the unbound total text box directly?
Instead of summing the unbound control by name, put a Sum expression over its underlying quantity and rate calculation in a text box in the subform's Form Footer.
Does DLast return the most recent rate?
Not necessarily. Create a query that explicitly identifies the latest rate for each item using a date or other ordering field, and resolve ties so that only one rate is returned per item.
Why does the main form show an error for the subform total?
Check the subform control's Name property on the main form and the footer text box's Name property. Use the control name in an expression such as =[SubformName].Form!txtSum.
What happens if an item has no matching rate?
The example uses Nz around DLookup, so a missing rate contributes zero to the total. Check for missing rates separately if that should be treated as an error.




