Results 1 to 4 of 4
  1. #1
    3rixhm is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010

    Question How to update a textbox based on another in its subform?

    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.

  2. #2
    3rixhm is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Anyway, solved by myself.


    Private Sub Form_Close()

    On Error GoTo Err_Form_Close

    GoSub Update_Form_Close


    Me.Sum = Me.Subform.Form.Subtotal

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    DoCmd.RunCommand acCmdRecordsGoToNext

    GoSub Update_Form_Close


    Exit Sub

    Resume Exit_Form_Close
    End Sub

  3. #3
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    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

  4. #4
    3rixhm is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-05-2010, 10:10 AM
  2. Update 2 fields based on where condition.
    By Confused in forum Access
    Replies: 2
    Last Post: 11-19-2009, 05:21 PM
  3. update table in loop based on IF statement
    By LALouw in forum Access
    Replies: 0
    Last Post: 07-26-2009, 08:46 PM
  4. Replies: 1
    Last Post: 05-27-2006, 12:35 PM
  5. Open form based on Subform criteria
    By Suzan in forum Programming
    Replies: 0
    Last Post: 04-25-2006, 02:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums