Inconsistent delay of total from Continuous subform to main form

[virtualgreek]

Dear all,

I have a scenario that is driving me nuts. (MS Access 2003)

I have a form/subform (Continuous form) where it gets its data from tables Order and Order_Details.

In the footer section of the subform I have a hidden textbox named txtOrder_Total_Amount which displays the total of the whole order. On the main form I have a bound contol from the Order table, namely Order.Order_Total_Amount_Euro

On the subform I have fields such as Product, Quantity, Unit_Price, Discount, Value_In_USD, Value_In_EUR. (certain Orders_Details are in USD whilst others are in EUR, but since I only care about EUR I calculate the exchange rate for all Order_Details that are listed in USD)

If the user changes the Product, Quantity or the Discount then the rest are modified on the exit event of each field. I then call the sub 'Update_Totals' which simply will refresh the txtOrder_Total_Amount (found in the footer of the subform ) based on the Order_Details.Value_In_EUR field

here is the code

Private Sub cmdUpdateTotals()

' Refresh the Order_Total_Amount

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Immediately after this code, I have the following code (to populate the main form Order_Total_Amount_Euro field.

Forms!Order.Order_Total_Amount_Euro = Forms!Order.OrderDetail!Order_Total_Amount

End sub

I have made both the Order.Order_Total_Amount_Euro and the Order.Order_Details.txtOrder_Total_Amount visible so I can view the results.

The problem is that although the code executes with no syntax errors (and I dont see any logic error here) and although the txtOrder_Total_Amount is correctly updated, the main form Order.Order_Total_Amount_Euro updates sometimes with a delay and sometimes not at all.

Example.
Order_Detail 1. Quantity = 100, Product_Price = 10 EUR, Discount = 50EUR, Value_In_Eur = 950.

Order_Detail 2. Quantity = 200, Product_Price = 10 EUR, Discount = 150EUR, Value_In_Eur = 1850.

txtOrder_Total_Value (in footer of subform) = 2800
Mainform.Order_Total_Value_Euro = 2800

If I change the quantity of the first Order_Detail record from 100 to 110 (and subsequantly the Value_In_Eur = 1050) then

txtOrder_Total_Value (in footer of subform) = 2900
Mainform.Order_Total_Value_Euru = 2800.

It may update to 2900 after a certain delay or may not at all.

Now if I repeatedly clickand tab away on the Quantity field at some inconsisent point the Mainform.Order_Total_Value_Euru will eventually update.

Any ideas ?
Kind Regards

VirtualGreek

[puppydogbuddy]

Try changing this:
Forms!Order.Order_Total_Amount_Euro = Forms!Order.OrderDetail!Order_Total_Amount

To:
Forms!Order.Order_Total_Amount_Euro.Value = Forms!Order.OrderDetail!Order_Total_Amount


In the subform, put the following code in the OnChange event for the textbox that has computation of the detail total so that the mainformTotal will be recalculated.
Forms!Order.Order_Total_Amount_Euro.Recalc

[virtualgreek]

Try changing this:
Forms!Order.Order_Total_Amount_Euro = Forms!Order.OrderDetail!Order_Total_Amount

To:
Forms!Order.Order_Total_Amount_Euro.Value = Forms!Order.OrderDetail!Order_Total_Amount


In the subform, put the following code in the OnChange event for the textbox that has computation of the detail total so that the mainformTotal will be recalculated.
Forms!Order.Order_Total_Amount_Euro.Recalc

Thanks puppydogbuddy. You pointed me to the right direction.

Take care!
VG

[puppydogbuddy]

[color=black][size=3][font=Times New Roman]VG,[/font][/size][/color]

[color=black][size=3][font=Times New Roman]Forgot to tell you that you should replace this code line: [/font][/size][/color][size=3][font=Times New Roman]DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70[color=black][/color][/font][/size]

[color=black][size=3][font=Times New Roman][/font][/size][/color]
[color=black][size=3][font=Times New Roman]With either this line:[/font][/size][/color]
[color=black][size=3][font=Times New Roman]DoCmd.RunCommand acCmdSaveRecord[/font][/size][/color]

[color=black][size=3][font=Times New Roman]Or this line:[/font][/size][/color]

[color=black][size=3][font=Times New Roman] Me.Dirty = False[/font][/size][/color]

[color=black][size=3][font=Times New Roman] [/font][/size][/color]

[color=black][size=3][font=Times New Roman]The DoMenuItem syntax is not used in access versions 2000 and higher. Please confirm that you got this message.[/font][/size][/color]

[color=black][size=3][font=Times New Roman] [/font][/size][/color]

[color=black][font=Times New Roman][size=3]Thanks. [/size][/font][/color]