Others

Sum an Unbound Calculated Field in a Microsoft Access Subform

Nimra MalikNimra Malik Nov 14, 2026 868 views

Question details

Display the sum of calculated item totals from a continuous subform on an invoice's main form.

Sum an Unbound Calculated Field in an Access Subform
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.
Before you start

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.

Solution 1Recommended

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.

1
Add a footer text box

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.

2
Set the total expression

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.

3
Show the total on the main form

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.

4
Test the invoice

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 the total in the subform footer and reference it from the main form
Check performance and missing rates: A lookup for each line can be slow on large datasets. The expression treats a missing rate as zero, so check for missing rates separately if they should be flagged instead.
Free Microsoft Office alternative

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.

Calculate invoice line totals and sums in a spreadsheet.Open and save Microsoft Excel formats such as XLSX.Work with a familiar spreadsheet interface.Use a free, lightweight office suite.
microsoft office alternative - wps office

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.