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

how to update total cost in order table when you edit order item table

P: 3
hi i'm trying to update total order cost in order table , basically what i want when i edit order item table such as changing quantity then it automatically update the total cost in order table

i mean if there are two order items in orderitemtable such as for item 1 and the hire cost is 20 and quantity is 2 for each item and these two different orderitems ID are under one orderID and it calculates the total order cost but doesnot shows in the order Table

als if i try to edit orderitem form such as changing quantity
it only change the total order cost on the order form but doesnot change the total order cost in order table
i think i did not explain properly so here is the coding

Dim strSQL As String
Dim strAddItem As String
Dim strOrderID As String

Call clearOrderItemsList
decTotal = 0
strOrderID = txtOrderID.Value

strSQL = "SELECT tblOrderItem.* "
strSQL = strSQL & "FROM tblOrderItem "
strSQL = strSQL & "WHERE tblOrderItem.[OrderID] = " & "'" & strOrderID & "' ;"
'MsgBox strSQL

Set rstOrderItemDetails = dbase.OpenRecordset(strSQL, dbOpenDynaset)

strAddItem = "OrderItemID" & ";" & "ToolID" & ";" & "HireDate" & ";" & "TotalCost"
lstOrderItems.AddItem (strAddItem)
strAddItem = ""
While Not rstOrderItemDetails.EOF

strAddItem = rstOrderItemDetails("OrderItemID") & ";" & rstOrderItemDetails("ToolID") & ";" & rstOrderItemDetails("HireDate") & ";" & rstOrderItemDetails("TotalCost")
decTotal = decTotal + Format((rstOrderItemDetails("Quantity") * rstOrderItemDetails("HireCost")), "0.00")
lstOrderItems.AddItem (strAddItem)
rstOrderItemDetails.MoveNext
Wend
txtTotalCost.Value = decTotal
rstOrderItemDetails.Close
Call UpdateTotalCost
End Sub

please help very very urgent
thanks
Apr 22 '10 #1
Share this Question
Share on Google+
3 Replies


mshmyob
Expert 100+
P: 903
@125a29
Unfortunately you have not given us enough information to work with but I would say you are going about this wrong.

You should never store the total of something (total cost) in the database. You should only store the quantity and unit cost, then let the software calculate the total cost in your queries and reports as needed.

cheers,
Apr 22 '10 #2

NeoPa
Expert Mod 15k+
P: 31,492
It sounds like Normalisation and Table structures may be worth reading.
Apr 22 '10 #3

mshmyob
Expert 100+
P: 903
125 please do not edit your post AFTER people have responded - create a new post responding to the previous post.

Your original post did not have the code but all of a sudden it does. That gets confusing. Anyways I see you created a new thread with this exact same problem so I will leave this thread now unless you want some help.

cheers,
Apr 22 '10 #4

Post your reply

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