Results 1 to 6 of 6
  1. #1
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    214

    Update Main Form Control

    Hi Everyone



    I have cross posted this topic on the following Forum https://www.utteraccess.com/forum/in...&#entry2743003

    I have a Main Form with a Subform.

    When I update a Record in the Subform it adds to a Running Total in the Subform.

    What I then need to happen is when the Record is updated in the Subform then the Running Total Value would update a Control named "OriginalContractSum" with the Current "Running Total" Value.

    Any help appreciated

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,888
    use the full path:
    forms!fMyMainForm!OriginalContractSum.requery


  3. #3
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    214
    Hi Ranman256

    Using the Full Path did not work either?

  4. #4
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    214
    Hi ranman256

    I changed the code to the On Exit of the Control "Labor" on the Subform

    Code:
    Private Sub Labor_Exit(Cancel As Integer)
    
    
    10        On Error GoTo Labor_Exit_Error
           Dim strSQL As String
             ' update parent record with current Total
    20      CurrentDb.Execute "UPDATE tblContractPurchaseOrder SET OriginalContractSum = " & Me.Total _
           & " WHERE CustomerPurchaseOrderID = " & Me!CustomerPurchaseOrderID & ";", dbFailOnError
              
    
    
    
    
          ' refresh parent form
    30    [Forms]![frmContractAIADetails]![OriginalContractSum].Recalc
              
    40        On Error GoTo 0
    50        Exit Sub
    
    
    Labor_Exit_Error:
    
    
    60        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Labor_Exit, line " & Erl & "."
    
    
    End Sub
    I now get the following error and when I click OK the correct value is displayed in the Control "OriginalContractSum"
    Attached Thumbnails Attached Thumbnails error.PNG  

  5. #5
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,165
    can you post a copy of the db
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  6. #6
    Join Date
    Apr 2017
    Posts
    1,042
    Simply write total value from subform into control in parent form.
    Code:
     
    Private Sub txtLabor_AfterUpate()
        ...
        Me.Parent.txtOriginalContractSum = Nz([txtTotal], 0)
        ...
    End Sub
    NB! I added 'txt' prefix to control names, to differ them and source field names! And AfterUpdate event is used becasue it is running only when value in control was edited. In case value for txtTotal in subform was calculated by some event, then it must be calculated before the total is written into main form control - i.e. in top part of AfterUpdate event, or in some event which precedes AfterUpdate event.

    Edit: As afterthought, you have to use AfterUpdate event of subform instead of AfterUpdate event of txtLabor. You can't calculate total before this, because the value in txtLabor in current row is not saved jet to table - it will be saved only when you leave this record (and you must use DSum() or run a query to calculate the value for total - from source table of subform).

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

Similar Threads

  1. Replies: 39
    Last Post: 07-28-2018, 12:27 PM
  2. Replies: 10
    Last Post: 07-05-2017, 11:31 AM
  3. Replies: 1
    Last Post: 10-06-2015, 06:50 AM
  4. Replies: 10
    Last Post: 02-20-2013, 07:04 AM
  5. Replies: 3
    Last Post: 03-29-2012, 12:40 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
  •  
Tech Forums: Microsoft Office Forums