Dsum from sub form

[John thomas]

Hi i have an orders form with aorder details sub form
I need a grand total of the order that i need to store in my orders table
Cant seem to make anything to work is this becouse my form is a sub form
Basically i am doing a dsum quantity *sold at price with a where id information
Doi i have to do anything special as the info is in the sub forms table

 

[TJPoorman]

In a normalized database it is bad process to store a total value based on your sub-tables. Best practice is to sum those up via queries.

 

[John thomas]

Thanks for your help
I need to store the invoice grand total
can you explain how i would do this using a query

 

[TJPoorman]

not sure what procedure you would use to kick this off, but this code should update the Order table with the totals of all sub items:

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("SELECT SUM([Quantity]*[PriceSold]) AS OrderTotal FROM tblOrderItems WHERE OrderID=" & Me.OrderID)

CurrentDb.Execute("UPDATE tblOrders SET InvoiceGrandTotal = " & rs!OrderTotal & " WHERE OrderID=" & Me.OrderID)

You will need to modify the names of your tables and fields, but that should get what you're looking for. I would imagine you would want to fire this on an event that submits the order.