DSum / Sum for calculating Order Total

[potassium flower]

I am trying to create a food order system for a restaurant.

I have tried using both the DSum and Sum functions to calculate the total cost of an order. The total cost is a textbox on the form tblOrder (and a field in tblOrder) and the list of meals along with their costs are in the tblOrderDetail Subform (the columns in this form come fromt two tables - tblOrderDetails and tblItems).

I have been putting the DSum / Sum in the control soure property. With the expression builder I have tried:
DSum ( [tblOrderDetail Subform].Form![Cost] , [tblOrderDetail Subform].Form )
result: #error
=DSum([Cost],[tblOrder]) result: #name?
=DSum("Cost","t blOrder") result: #error
=DSum("[Cost]","[tblOrder]") result: #error

Sometimes I get #name? error, which i can solve by using a more lengthy description (not sure that's the right word) though all the internet examples only use one word in quotation marks, or i get #error which i can't solve at all.

I haven't specified a criteria as I wanted it to calculate the cost for all the items in that order

Both the table and the form are called tblOrder becasue I didnt change the name Access chose in the form wizzard

I am using Access 2000

[Rabbit]

What's the meta data for the tables? i.e. The fields of the table and type of data it holds.

[potassium flower]

tblOrder
OrderID Autonumber
TableID Number
Total Cost Currency

tblOrderDetails
OrderDetailsID Autonumber
OrderID Number
ItemID lookup
(and other records that I dont think are relevant)

tblItem
ItemID Autonumber
Cost Currency
(and other records that I dont think are relevant)

[NeoPa]

Check out this link (Normalisation and Table structures). It explains why storing the calculated value is not a good idea.
Are you sure the tblItem table doesn't have a name field of some form? Hard to think this could be irrelevant.
Ignoring the updating of the [Total Cost] field, you will need a QueryDef (saved query) called something like qryOrder, whose SQL is :

SELECT tblOrder.OrderID,tblOrder.TableID,
       tblOrderDetails.ItemID,tblItem.Cost
FROM (tblOrder INNER JOIN tblOrderDetails
  ON tblOrder.OrderID=tblOrderDetails.OrderID)
     INNER JOIN tblItem
  ON tblOrderDetails.ItemID=tblItem.ItemID

In the Control Source of your Total Cost control you would need :

=DSum("[Cost]","qryOrder","OrderID=" & txtOrderID)

[potassium flower]

Thanks, not storing the Total Cost makes sense and the code will now calculate the Total for that order. However I have added a button for refreshing the form, so that you dont have to open and close the form to get the correct total-

Private Sub Command18_Click()
 
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
 
End Sub

But when I click it a message box appears "Compile Error: Expected: Case" and tblOrder is highlighted from the first line of the code for calculating the total cost-

SELECT tblOrder.OrderID,tblOrder.TableID,
       tblOrderDetails.ItemID , tblItem.Cost
FROM (tblOrder INNER JOIN tblOrderDetails
  ON tblOrder.OrderID=tblOrderDetails.OrderID)
     INNER JOIN tblItem
  ON tblOrderDetails.ItemID=tblItem.ItemID

Im not sure if that means an error somewhere or that I'm going about refreshing the wrong way?

[Rabbit]

You are getting an error because you are trying to use straight SQL code as visual basic code.

Either have the SQL in a query and run the query from code or use a DoCmd.RunSQL "SQL Code Here". Quotes included.

[NeoPa]

In this case, referring back to the instructions in post #4, you need to save this SQL into a QueryDef called qryOrder. Otherwise the DSum() function cannot work.

[NeoPa]

Let us know if this makes sense to you.
If necessary we can break it down into easier to follow steps.

[potassium flower]

I've put it around the

DoCmd.RunSQL "DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

and the same error appears, is that the right place?

[Rabbit]

That's the wrong place, you put it around the select statement. You're going to have to put the Select SQL statement on one line.