Display subtotal from subform in main form

[dougdrex]

I'm using Access 2010 and have a subtotal displaying in a subform called FORM_ExpenseTransactionDetails. I created a field in the main form (FORM_ExpenseTransactions) and referred to the subtotal field, or at least I thought I did.



The field displays #Name? in Form View and I've tried a variety of things to solve the issue, but to no avail.

I've attached the file for review. If anyone can point me in the right direction, I would really appreciate it.

Thanks!
Doug

[ranman256]

You could put a txt box in the footer of the sub-form. =sum([myfield]) (name it txtSubTotal)
then put another txtBox in the main form that looks at it.. =forms!frmMain!subForm.form.txtSubtotal
(ALWAYS use the builder to get the path correct)

or

make a query that summs the data based on the master key...qsSumSubTot
then the mainform txtBox = dLookup("[total]",qsSumSubTot)
BUT you have to refresh it on record change.

[dougdrex]

Hi ranman,

Thanks for the input, I really appreciate it! I created a query as you suggested and called it ExpenseSubtotals. Following is the SQL code from the query:

SELECT TBL_TransactionDetails.TransactionID, TBL_Transactions.TransactionCounterParty, Sum([ExpenseAmount]-[IncomeAmount]) AS SubtotalFROM TBL_Transactions INNER JOIN TBL_TransactionDetails ON TBL_Transactions.TransactionID = TBL_TransactionDetails.TransactionID
GROUP BY TBL_TransactionDetails.TransactionID, TBL_Transactions.TransactionCounterParty, TBL_TransactionDetails.IncomeAmount
HAVING (((TBL_TransactionDetails.IncomeAmount)<=0));

In the Control Source property of the text box in the main form, I entered "=DLookUp("[Subtotal]",[ExpenseSubtotals],[TransactionID])". However, I'm getting the same error message as before. Am I doing something wrong?

[June7]

Try:

=DLookUp("[Subtotal]", "[ExpenseSubtotals]", "TransactionID=" & [TransactionID])

Actually, it should automatically recalculate when navigating records. Domain aggregates can perform slowly. You might even see a delay in the textbox recalc.

[dougdrex]

June7,

That worked perfectly!!

Do you mind explaining the logic behind that syntax and where I went wrong in my previous attempt? I'd like to learn from this experience so I don't run into a similar issue in the future.

Thank you SO MUCH!!!
Doug

[June7]

See if this helps http://rogersaccessblog.blogspot.com...mystified.html