Subform total out of sync with form 1

[adizukerman]

First, I would like to thank everyone for taking the time to read this post.

I have a form, frmOrder, with a sub-form, subfrmDetail. frmOrder represents an order (like a purchase order) and each record in subfrmDetail is a line item on the order. In subfrmDetail Footer I have a text box “Order Subtotal” with control source equal to “=Sum([Total Line])”

On frmOrder I have text box “Order Subtotal” with control source “=[subfrmOrderDetail].[Form]![Order Subtotal]” which represents the sum of all the line items on the order. To find the order total I need to add shipping. On frmOrder I have text box “Order Total” with control source “=([Order Subtotal]+[Rush Ship Charge])” I want to store in the database Order Total in field [Total Order Amount] but I don’t know how to have a text box calculate a value and save it in a field so I created text box “Total Order Amount” with a control source “Total Order Amount”. To summarize:

Form control name control source
subfrmDetail Order Subtotal =Sum([Total Line])
frmOrder Order Subtotal =[subfrmOrderDetail].[Form]![Order Subtotal]
frmOrder Order Total =([Order Subtotal]+[Rush Ship Charge])
frmOrder Total Order Amount Total Order Amount

I put, in the subfrmDetail, the following code in the Form_BeforeInsert, Form_BeforeUpdate, Form_AfterUpdate and Form_AfterInsert events:
Me.Parent.Total_Order_Amount = Me.Parent.Order_Total

The problem is that at times Total Order Amount and Order Total come out of sync. I put the following in the sub-form Form_AfterUpdate() event:
Debug.Print "Me.Order_Subtotal = " & Me.Order_Subtotal
Debug.Print "Me.Parent.Order_Total = " & Me.Parent.Order_Total
Debug.Print "Me.Parent.Total_Order_Amount = " & Me.Parent.Total_Order_Amount
Me.Parent.Total_Order_Amount = Me.Parent.Order_Total
Debug.Print "COPIED VALUE OVER"
Debug.Print "Me.Parent.Total_Order_Amount = " & Me.Parent.Total_Order_Amount
Debug.Print "Me.Order_Subtotal = " & Me.Order_Subtotal
If I run the code I get the following output:
Me.Order_Subtotal = 1262.5
Me.Parent.Order_Total = 1262.5
Me.Parent.Total_Order_Amount = 1262.5
COPIED VALUE OVER
Me.Parent.Total_Order_Amount = 1262.5
Me.Order_Subtotal = 1262.5

BUT the value on the screen for Order_Subtotal is $1725.00!

If I put a breakpoint on the first debug.print and then press F5 I get the following output:
Me.Order_Subtotal = 2050
Me.Parent.Order_Total = 2050
Me.Parent.Total_Order_Amount = 1887.5
COPIED VALUE OVER
Me.Parent.Total_Order_Amount = 2050
Me.Order_Subtotal = 2050

Which is what I would expect. Why does the breakpoint make a difference? How can I get this to work?

Again, thank you for taking the time to look at this. If there is any other information I can provide please let me know.

Regards,

Adi Zukerman
adizukerman@hotmail.com

 

[vbajock]

You might want to re-thing how you are doing it. You should be able to accomplish the same thing with a call to the Dsum
function. Look at Dsum in the help file. The key is the criteria you feed it in the where clause