Hi, I'm trying to put a value from subform's textbox B to form's textbox A when form is about to close.

The main form is based on Order table, subform is based on Order_Detail, Order_Detail has a textbox Subtotal in its footer =Sum(EachPrice)

Order table has a field sum, which is a textbox in main form. I would like to do something like this:

When main form is about to close (OnClose event)
For each Order
Order.sum = Order_Detail.Subtotal
Next Order

Pls don't ask me why I would do this, why not make it for a afterupdate event on Subtotal etc. I have been asked to do something like this, not others.

Thanks every much for any help and idea.

Anyway, solved by myself.

--------------

Private Sub Form_Close()

On Error GoTo Err_Form_Close

GoSub Update_Form_Close

Update_Form_Close:

Me.Sum = Me.Subform.Form.Subtotal

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

DoCmd.RunCommand acCmdRecordsGoToNext

GoSub Update_Form_Close

Return

Exit_Form_Close:
DoCmd.Close
Exit Sub

Err_Form_Close:
Resume Exit_Form_Close
End Sub

Let me get this straight. You have a main form based on order table which probably contains date, order number, customers details etc and a subform which records the order details namely Products, Price per unit, Quantity, Price p/u * Quantity. Now as far as I understand you have already created a text box that calculates the total in the subfrom.

now below is my suggestion. let us assume the TextBox in you subform calculating the total is OrderSubtotal, Name of your Subform is Orders Subform. Create a text box (MainfrmTotal) on the main form and in its data source type the following code(marked in black). This will show you the subform total on the main form even when you are entering the product details in the subform. Now I understand that you have another field on your main form in which you will like to record the data on the OnClose Event of the form type the code marked in red.


=[Orders Subform].Form!OrderSubtotal

Me.MainfrmTotal=Order.Sum 'This Order,Sum is the sum field on your order main form.

try this out and let me know at silverback_bats@yahoo.co.in

Hi maximus, thanks for your idea!

Actually, I've tried to make the question simple, so i called it Order here, in real it's something works like it but much more complicated.

SUBTOTAL in subform is based on a SQL query with SUM() in it (SELECT Order.OrderID, SUM(Order_Detail.Price) FROM......) , but SUM in Orders is a field of a table, result of SUBTOTAL changes while i'm editing record and final result will go to SUM when i close the form.

Any changes on so-called "Subtotal (i.e Sum)" for one so-called "Order" will affect all other "Orders", and that's why i decide to update all "Order"'s SUM when close.

I've got an idea and put it on #2.

Thanks again.