By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,741 Members | 1,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,741 IT Pros & Developers. It's quick & easy.

DSum / Sum for calculating Order Total

P: 45
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","tblOrder") 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
Feb 6 '07 #1
Share this Question
Share on Google+
33 Replies


Rabbit
Expert Mod 10K+
P: 12,366
What's the meta data for the tables? i.e. The fields of the table and type of data it holds.
Feb 6 '07 #2

P: 45
Expand|Select|Wrap|Line Numbers
  1. tblOrder
  2. OrderID Autonumber
  3. TableID Number
  4. Total Cost Currency
Expand|Select|Wrap|Line Numbers
  1. tblOrderDetails
  2. OrderDetailsID Autonumber
  3. OrderID Number
  4. ItemID lookup
  5. (and other records that I dont think are relevant)
Expand|Select|Wrap|Line Numbers
  1. tblItem
  2. ItemID Autonumber
  3. Cost Currency
  4. (and other records that I dont think are relevant)
Feb 6 '07 #3

NeoPa
Expert Mod 15k+
P: 31,489
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT tblOrder.OrderID,tblOrder.TableID,
  2.        tblOrderDetails.ItemID,tblItem.Cost
  3. FROM (tblOrder INNER JOIN tblOrderDetails
  4.   ON tblOrder.OrderID=tblOrderDetails.OrderID)
  5.      INNER JOIN tblItem
  6.   ON tblOrderDetails.ItemID=tblItem.ItemID
In the Control Source of your Total Cost control you would need :
Expand|Select|Wrap|Line Numbers
  1. =DSum("[Cost]","qryOrder","OrderID=" & txtOrderID)
Feb 7 '07 #4

P: 45
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-

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command18_Click()
  2.  
  3. DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  4.  
  5. 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-

Expand|Select|Wrap|Line Numbers
  1. SELECT tblOrder.OrderID,tblOrder.TableID,
  2.        tblOrderDetails.ItemID , tblItem.Cost
  3. FROM (tblOrder INNER JOIN tblOrderDetails
  4.   ON tblOrder.OrderID=tblOrderDetails.OrderID)
  5.      INNER JOIN tblItem
  6.   ON tblOrderDetails.ItemID=tblItem.ItemID
Im not sure if that means an error somewhere or that I'm going about refreshing the wrong way?
Feb 8 '07 #5

Rabbit
Expert Mod 10K+
P: 12,366
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.
Feb 8 '07 #6

NeoPa
Expert Mod 15k+
P: 31,489
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.
Feb 8 '07 #7

NeoPa
Expert Mod 15k+
P: 31,489
Let us know if this makes sense to you.
If necessary we can break it down into easier to follow steps.
Feb 8 '07 #8

P: 45
I've put it around the

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
and the same error appears, is that the right place?
Feb 8 '07 #9

Rabbit
Expert Mod 10K+
P: 12,366
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.
Feb 8 '07 #10

NeoPa
Expert Mod 15k+
P: 31,489
Instructions for creating the QueryDef (qryOrder).
  1. From the main Database window select Insert / Query.
  2. Click OK on Design View.
  3. Hit <ESC> to avoid adding a table.
  4. Select View / SQL View.
  5. Paste in the SQL posted earlier (Copied below).
  6. Select View / Design View.
  7. Close and, when prompted, select Yes to Save.
  8. Enter "qryOrder" (no quotes) as the name.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblOrder.OrderID,tblOrder.TableID,
  2.        tblOrderDetails.ItemID,tblItem.Cost
  3. FROM (tblOrder INNER JOIN tblOrderDetails
  4.   ON tblOrder.OrderID=tblOrderDetails.OrderID)
  5.      INNER JOIN tblItem
  6.   ON tblOrderDetails.ItemID=tblItem.ItemID
Feb 8 '07 #11

P: 45
In reply to #7 and #8 and judging by 11# the SQL for the query is in the right place and the total cost textbox in the order form will give you the correct total if you close and re-open the form, just not immidiatly. Somehow I must have put the SQL in as code as well, which I have now deleted, and it works fine, thank you so much!!
Feb 9 '07 #12

P: 45
Along a similar line I know I need to put .refresh somewhere so that the form always shows the most current total cost but I'm not sure what to attach it to and what code to put in front of it? Thanks again
Feb 9 '07 #13

NeoPa
Expert Mod 15k+
P: 31,489
You will need a .Requery rather than a .Refresh.
I will try to have a look at this again to see where.
Feb 9 '07 #14

NeoPa
Expert Mod 15k+
P: 31,489
Right, to know where to put the .Requery, we need to consider what is changing the data that this is worked out from.
Expand|Select|Wrap|Line Numbers
  1. =DSum("[Cost]","qryOrder","OrderID=" & txtOrderID)
The code to do it would be :
Expand|Select|Wrap|Line Numbers
  1. Call Me.[Total Cost].Requery
It needs to be set up to trigger after any changes are made to the underlying data that effect the result.
Feb 9 '07 #15

P: 45
Is that VB code that I should put on something like an after update event in the ItemID field of the subform? I'm just getting error messages or nothing at the moment and could do with a pointer?
Feb 10 '07 #16

NeoPa
Expert Mod 15k+
P: 31,489
That's about right I think.
It does depend on which controls cause the result to change. You would need to add it to all of their AfterUpdate event procedures.
Feb 10 '07 #17

P: 45
I've tried that but I can't get it to work, guess that's a bit more complex than I can manage at the moment, I'll just leave it to an update button I think, thanks so much for all your help :)
Feb 10 '07 #18

NeoPa
Expert Mod 15k+
P: 31,489
That's alright.
Have you tried setting it on one control that you know causes the requery to be required, then testing it by changing the value in that control?
A step-by-step approach often works well in debugging.
Feb 10 '07 #19

P: 45
I recon the control would be the ItemID because an addition of an Item is what requires the TotalCost to be recalculated. Thanks for the pointer.

However I keep getting a error saying "can't find the field "I" ' and the option to debug the line
Expand|Select|Wrap|Line Numbers
  1. Call Me.[Total Cost].Requery 
The VB code doesn't have an "I" in it so i don't know what to debug.
Feb 10 '07 #20

NeoPa
Expert Mod 15k+
P: 31,489
Try putting a line :
Expand|Select|Wrap|Line Numbers
  1. Call MsgBox(Me![Total Cost].ControlSource)
before the .Requery line and seeing what it shows you.
Feb 10 '07 #21

P: 45
It says 'Compile Error: Invalid qualifier' and Msg box is highlighted.

Expand|Select|Wrap|Line Numbers
  1. Private Sub ItemID_Enter()
  2.  
  3. Call MsgBox(Me![Total Cost].ControlSource).Requery
  4.  
  5. End Sub
Feb 10 '07 #22

NeoPa
Expert Mod 15k+
P: 31,489
I meant like this :
Expand|Select|Wrap|Line Numbers
  1. Private Sub ItemID_Enter()
  2.  
  3. Call MsgBox(Me![Total Cost].ControlSource)
  4. Call Me.[Total Cost].Requery 
  5.  
  6. End Sub
Feb 10 '07 #23

P: 45
Ah, sorry about that. It brings up a message box saying Run-time error '2465' Microsoft Access can't find the field '[Total Cost]'. I thought that might be because it is in a different form, so replaced Total Cost with Forms![tblOrder]![Total Cost]] :

Expand|Select|Wrap|Line Numbers
  1. Call MsgBox(Me![Forms![tblOrder]![Total Cost] ].ControlSource)
  2. Call Me.[Forms![tblOrder]![Total Cost] ].Requery
But that comes up with the same result. The text box is named Total Cost.
Feb 11 '07 #24

NeoPa
Expert Mod 15k+
P: 31,489
PF, what you've posted as code is not going to work I'm afraid as... well for a number of reasons. I'm confused my code didn't work though.
Can you try my code exactly and tell me what the response is?
Copy & Paste the code into your project. Do not try to type it in manually (Not personal - I always recommend that, as so many people put in typos and I need to know for certain where we are).
Feb 11 '07 #25

P: 45
What does PF stand for?? I'm not good at programming so it figures the code's wrong, that's fine. Copied exactly it comes up with the same error message as in #24, should have made that clearer sorry.

I've put the code on the Change, Enter and After Update properties of ItemID, dunno if that helps.
Feb 11 '07 #26

NeoPa
Expert Mod 15k+
P: 31,489
In that case I can only suggest that you go back to the form design, select the control (not the label attached) and tell me exactly what is written in the Name property (including any spaces, trailing, leading etc). Surround it in brackets ([]) to be clear. This implies strongly that the field isn't actually called [Total Cost] so I hope this is simply down to a confusion (Otherwise I'm confused & I hate that :().
PF = My abbreviation for potassium flower. I'm too lazy to be doing with names that are too long all the time.
It shouldn't matter for now where the code is placed, as long as you can get it to execute. It's important to deal with these things in an orderly manner otherwise we'll all get confused (Did I mention I didn't much like that?).
Let's see what you can dig up on the name first.
Feb 11 '07 #27

P: 45
'Total Cost' is the name as well as the text box, I changed it from 'Text10' to save confusion. Yes you did, buy the way :)
Feb 11 '07 #28

P: 45
Right, if you take the code

Expand|Select|Wrap|Line Numbers
  1. Call MsgBox(Me![Total Cost].ControlSource)
  2. Call Me.[Total Cost].Requery
out of ItemID which is in the subform and put it in the main form you don't get an error. If you put it in the main form on the on-click event (of for instance the OrderID) then a mesage box comes up showing the Dsum code for the Total Cost, which I guess is what that extra bit of code you gave me was suppost to achieve.

However it's the Item ID and it's related fields that change an require the Total Cost to Be re-calculated. So idealy I want the code to be on the 'on change' event of this.
Feb 11 '07 #29

NeoPa
Expert Mod 15k+
P: 31,489
'Total Cost' is the name as well as the text box, I changed it from 'Text10' to save confusion. Yes you did, buy the way :)
Now I'm confused :(
Is it possible that this TextBox is on a subform or even a parent of the subform?
To confirm, is it on the same form as ItemID?

I should commend you for changing the name btw. It's actually quite important but so many people don't realise (and don't rename) so get into trouble later.
Feb 11 '07 #30

NeoPa
Expert Mod 15k+
P: 31,489
Right, if you take the code

Expand|Select|Wrap|Line Numbers
  1. Call MsgBox(Me![Total Cost].ControlSource)
  2. Call Me.[Total Cost].Requery
out of ItemID which is in the subform and put it in the main form you don't get an error. If you put it in the main form on the on-click event (of for instance the OrderID) then a mesage box comes up showing the Dsum code for the Total Cost, which I guess is what that extra bit of code you gave me was suppost to achieve.

However it's the Item ID and it's related fields that change an require the Total Cost to Be re-calculated. So idealy I want the code to be on the 'on change' event of this.
Aaaaaarrrrrgh!!!!!! (Screaming silently to himself for hours)
Feb 11 '07 #31

NeoPa
Expert Mod 15k+
P: 31,489
Let me know if you have troubles getting it to work after checking out this Tutorial (Referring to Items on a Sub-Form).
Feb 11 '07 #32

P: 45
:-D never have I had a bigger grin. Thank you! (Adding .Parent made it work if you hadn't guessed)
Feb 11 '07 #33

NeoPa
Expert Mod 15k+
P: 31,489
I'm very pleased to hear it PF. : phew :
Feb 12 '07 #34

Post your reply

Sign in to post your reply or Sign up for a free account.