473,287 Members | 2,263 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

DSum / Sum for calculating Order Total

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
33 8151
Rabbit
12,516 Expert Mod 8TB
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
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
32,554 Expert Mod 16PB
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
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
12,516 Expert Mod 8TB
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
32,554 Expert Mod 16PB
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
32,554 Expert Mod 16PB
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
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
12,516 Expert Mod 8TB
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
32,554 Expert Mod 16PB
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
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
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
32,554 Expert Mod 16PB
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
32,554 Expert Mod 16PB
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
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
32,554 Expert Mod 16PB
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
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
32,554 Expert Mod 16PB
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
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
32,554 Expert Mod 16PB
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
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
32,554 Expert Mod 16PB
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
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
32,554 Expert Mod 16PB
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
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
32,554 Expert Mod 16PB
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
'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
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
32,554 Expert Mod 16PB
'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
32,554 Expert Mod 16PB
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
32,554 Expert Mod 16PB
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
:-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
32,554 Expert Mod 16PB
I'm very pleased to hear it PF. : phew :
Feb 12 '07 #34

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

Similar topics

0
by: Rolan | last post by:
I'm using Access 97 and need some assistance in sorting out a proper DSum expression, or maybe even DCount might be an alternative. I have tried numerous combinations, but with no apparent success....
1
by: Dalan | last post by:
I have tried both methods of using DSum and creating a Function to address summing some number columns, but to no avail. Since this has been a popular topic over the years, I'm sure I'll receive...
2
by: Dalan | last post by:
I seemed to be having problems with structuring the use of NZ with a DSum expression. Having tried numerous variations of the expression without success, I'm asking for assistance. First some...
4
by: John Baker | last post by:
Hi: I have a form, and wish to show on the form the current total for a single field on a table. I have set this up thus: =DSum(,!) in an unbound text field. These are correct field and table...
1
by: phaddock4 | last post by:
Being fairly inexperienced at Access 2000, i've been reading many posts here for the last several days, and testing myself to find the best approach to do the following in A2K: SET UP: I have...
6
by: pixie | last post by:
Hi. I'm having problems with the following DSum in my report footer. It gives me #Error when I run the report. I hope someone can help me out as I am at my wits end. What I am trying to do is to...
1
by: Simon | last post by:
I am trying to have a button on my order form that will work out how much is outstanding on the order on the form i have the total value of the the order and also have text box that i want to...
10
by: Lisa | last post by:
In translating the formula for calculating lottery odds for various conditions into a Visual Basic Program, I have apparently missed something in that I get errors in the part of the calculation...
3
by: Bhujanga | last post by:
I have a field on a form that I'm trying to show the total of the invoices that have come in for the project. The ProjectID is field on the form which is in the forms primary table (ContractInfo)....
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.