Please help how to calculate order detail

[tonysi]

Hi Everyone,

I am sorry if my post has been cover so many times. I really appreciate for helping me with my problem.

The problem that I am having is how to recalculate all order detail item. Sorry not sure how to explain
FORM
Main form = Tblorder : orderID, CustumerID, TotalSquare
Sub form = TblOrderdetail : orderdetailID, OrderID, itemname, unitprice, total

Example: if I set up the totalsquare FIRST = 10 and I select the the itemname, it will calculate the total = unitprice * totalsquare this work fine.

The problem:
I have 20 item in sub form orderdetail and every item was calculate based on totalsquare = 10. For some reason, I have to change the totalsquare = 20.
How do I make so that it will recalculate all 20 items in subform/orderdetail instead of deleting all item and re enter it again?

Sorry if my question wasn't clear and thank you for your help!

 

[burrina]

You could do a Me.Requery after entering the item.

 

[tonysi]

Thank you for your respond.

It still doesn't work. I have attached the image and I hope you can see it.

In this image item name has 4 products.
The first 2 Total was calculate base on quantity * unit price.
The last 2, column landmark, highlander,......... was calculate based unitprice * total square.

Currently measurement is 500 and total square is 9. If I change the measurements to any number, the total square will also change.

The last 2 items vehicle and plywood calculate based on total square.
I put me.requery in measurement text after update even. Not sure if this is correct and it still doesn't recalculate the 2 items.

If I have 20 items, I have to delete all items and re enter it again.

 

[burrina]

So you are doing the calculation in the subforms footer? Is this a calculated expression in your query or just and expression? Are you using the forms afterupdateevent or the controls afterupdateevent?

Here is an Example:

Total: CCur(Nz([qty],0)*Nz([cost],0))+[FT]	'In Query
FT: CCur(Nz([Qty],0)*Nz([Feet],0))		'In Query
Total =Nz(Sum([Total]),0) 'Form Footer

 

[tonysi]

Hi All,

Thank you for respond. I have attached sample database so it is easy to understand.

There 1 order there currently totalsquare = 50.
Total is calculate based on Unitprice * totalsquare

I have 4 products listed there. If I changed the totalsquare = 100 from 50, How do I make so all my products will recalculate the total.

Thank you.

 

[jdraw]

I suggest relationships along the line of the attached. jpg.

Do not use the UnitPrice in the Product table as the Price in the OrderDetail. If you do, and you change the Product's unitPrice, then all of your existing records could be modified and you would lose all history.
In the OrderDetail -- identify the quantityOrdered and the AgreedToUnitPrice which is the Price you charged that Customer on this Order. This allows you to change the Price of the Product and not affect existing records. It also allows you to have a Customer loyalty program, or clearance sale, or a discounted Price for some reason.
Since your Order showed a Customer, I added the Customer table and some generic fields. CustomerID in Order table is Foreign key to link Customer to Order. You link/join on key fields. I also added the referential integrity that now show
1 Customer may have 1 or many Orders
An Order may have 1 or many DetailRecords
An Product may be associated with many Order details

I would not have TotalSquare as a field in atable. You can clculate this by adding the QuantityOrdered values for an Order.

Hope this is helpful.