How 2 $um fields 4 specific records in one table and store in another 2

[Stolman]

Two tables: tblOrderDetail and tblOrder
Two forms: frmOrderDetails (subform) and frmOrders
Common data field of each table is “Order#” (key in tblOrders and non-key/required in tblOrderDetal)

In subform frmOrderDetails, I am able to calc tblOrderDetails.ExtAmt (currency) field using a CALC textbox assoc with VBA code to multiply Quan, Amt and Discount (if any) fields but …
I want to also sum ExtAmt field for all records per “Order#” in tblOrderDetail and store summed total in TotalOrderAmount field of tblOrders.

Assume VBA is the best way to do this via an Event associated with the subform, but cannot find an example anywhere on the net and am reluctant to remove the few remaining hairs on my head trying to further figure on my own.

Would appreciate sage suggestions from those with greater wisdom and experience.
Thanks in advance.

 

[Remou]

This can be done easily with a query, but it is against the rules of normalization (

http://r937.com/relational.html).

If you must store this calculated value, you should do so when the order is created or updated, otherwise it will get out of synch very quickly. The After Update event of the Order Detail subform is probably most suitable. Something on the lines of:

Me.Recalc 
Me.Parent.OrderTotal=Me.txtCalcValue

Totals for orders can be got with a query on these lines:

SELECT OrderID, Sum(Quan), Sum(Amt), Sum(Discount), 
     Sum((Quan*Amt)-Discount) As ExtAmt
FROM OrderDetail
GROUP BY OrderID

 

[Stolman]

Thank you for this. If you would earn my thanks even more, please help with an additional item.

Per your suggestion I have eliminated the storage of a totaled field (thank you). Now, using your suggestion, I have created a query to be called by the parent form. However, the query opens a new window with totals for each order. How do I display only the ExtAmt total for a given order in the parent window?

Please accept my apologies in advance if this is a lame question. It has been many years since I coded in mainframe assembly language and I am having a difficult time coming up to speed with Access, SQL and VBA.

Thanks very much.

 

[PHV]

Have a look at the DSum function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Stolman]

Thanks PH. DSum was the first thing I tried. I used "=DSum(ExtAmt, tblOrderDetail)" (sans quotes) as an expression for a textbox in the Parent Form and all I could get was "#NAME?" in the textbox. So I came to the forum to see if there was another way. DSum should work but I have no clue why it won't.

 

[dhookom]

The domain aggregate functions used in control sources require quotes around most arguments. If you want to return the sum of the ExtAmt field from table tblOrderDetail, then use:

=DSum("ExtAmt", "tblOrderDetail")

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

Is there a reason why you are not using the subform footer to do these calculations?

http://office.microsoft.com/en-us/access/HP051879091033.aspx

You can use a where statement with DSum:

=DSum("ExtAmt", "tblOrderDetail", "ID=Forms!frmF!ID")


You may also wish to look at the Northwind sample database that ships with every copy of Access. It can also be downloaded from:

http://www.microsoft.com/downloads/...72-8DBE-422B-8676-C632D66C529C&displaylang=EN

 

[Stolman]

Many thanks to each of you who took the time to read and respond, especially dhookom and Remou. I realize now how finicky the syntax parser is (quotes around arguments). And Remou's suggestion (AND link) to the old version of Northwind was the best advice I have gotten in weeks. (I was using the 2007 flavor of Northwind).

I am now off to the races. Thanks again to all.
-Bill