Results 1 to 13 of 13
  1. #1
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163

    Events for form/subform

    I have a field on a form that calculates a total with VBA code. I want the field to recalculate everytime there is a change in either of two subforms as it is based on them. I'm having a tough time figuring out what event applies. I've tried OnEnter, Lostfocus, gotfocus, onExit for both the subforms and the main form but none have worked. I've also tried some fields in both forms...afterupdate and such. Where should I be looking for the correct event?

    There are three fields in play. Two are being added together for a total. FieldOnMainFOrm + FieldOnSubform1 = TotalFieldOnMainForm.


    The FieldOnMainForm is simply equal to FieldOnSubForm2.

    I'd like ToitalFieldOnMainForm to update anytime there is a change on SubForm1 or SubForm2


    Thanks

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the subform control contains a form and not a query or table, then use the AfterUpdate of the subform(s) to populate the main form control. This is expressed backwards, I think:FieldOnMainFOrm + FieldOnSubform1 = TotalFieldOnMainForm.

    Maybe something like

    Code:
    Forms!MainFormName.TotalFieldOnMainForm = Me.FieldOnSubform+Forms!MainformName.FieldOnMainForm
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    I should add that the procedure I wrote is in a module. The procedure is called CalcTotal

    I'm putting a procedure in the class object Form_MyForm that calls the procedure. It works fine with a command button or when I hit F5 in VB. I just can't find event(s) that will trip it.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yes, that's important. Then I don't think replacing Me with the full reference will work because IIRC subforms are not part of the forms collection when open. Likely you'll have to pass the value from the control(s) to the function. If you get stuck, post the relevant code (likely at least one subform AfterUpdate plus the function from the standard module, or place the function on the form instead of a standard module (unless you need to call it from multiple forms).

    EDIT - just notice the form_Myform part. That's a reference to the form class which ought to give you access to the subform itself, but it's not a method I've ever used. I think you'd have more success passing values to your function right from the subform AfterUpdate event instead.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Moving it to the class object section and using the after_update did the trick for one of the subforms. Not working on the other but I think I know why. The event is firing properly (I was able to test it) but the FieldOnSubform1 is just an unbound textbox with =Sum(Field1). I think that calculation is taking place somewhere after the after_update and I'm missing it. Is there an event on a subform that fires after after_update? There's no unload I would imagine since it's just sitting there. I'm not even sure if it's considered loaded.

    On the other subform, I'm thinking it's working property for one of two reasons.
    1. The calculation on the subform is simply multipication. Not a sum function.
    2. I've set a field on the main form equal to it, which is why the math problem has FieldOnMainForm + FieldOnSubform.

    I'll try to upload

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Between form and control events there are a lot of events that one can use. In this case, why have the expression as the controlsource at all? Just set it in code since you're using code anyway.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Quote Originally Posted by Micron View Post
    Between form and control events there are a lot of events that one can use. In this case, why have the expression as the controlsource at all? Just set it in code since you're using code anyway.
    Brilliant. I'll do that. It's easy to fall down a rabbit hole with this stuff.

  8. #8
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Also, give a try to the
    Code:
    Me.Parent.Refresh
    in the AfterUpdate event of each subform.

  9. #9
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    I think I'm going to have lose all of these calculated fields. They seem to calculate late. So any onload or update happens before them it appears.

  10. #10
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Quote Originally Posted by accesstos View Post
    Also, give a try to the
    Code:
    Me.Parent.Refresh
    in the AfterUpdate event of each subform.
    Thank you. On a related note, does deleting a record kick off the afterupdate? Or should I include the refresh in the afterdelete event as well?

  11. #11
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by lefty2cox View Post
    Thank you. On a related note, does deleting a record kick off the afterupdate? Or should I include the refresh in the afterdelete event as well?
    You are welcome.

    For deleting, the AfterUpdate event doesn't occur, but for a new record does. So, you have to deal with the AfterDelConfirm event for the cases of deleting.

    Please take a look to this link:
    https://support.microsoft.com/en-us/...86553682f9#bm2

  12. #12
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    I often use private instances of Form object in the module of the main form to manage the events of the sub forms.

    For example, a main form with two subforms could have a code like this:
    Code:
    Option Compare Database
    Option Explicit
    
    'Code for the main form.
    
    Private WithEvents mSF1 As Form
    Private WithEvents mSF2 As Form
    '
    
    Private Sub Form_Load()
        Set mSF1 = Me.sfrm1.Form
        mSF1.OnCurrent = "[Event Procedure]"
        Set mSF2 = Me.sfrm2.Form
        mSF2.OnCurrent = "[Event Procedure]"
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
        Set mSF1 = Nothing
        Set mSF2 = Nothing
    End Sub
    
    Private Sub mSF1_Current()
        'Event procedure from the first subform.
        Me!txtSubForm = mSF1.Name & " ID= " & mSF1!ID
    End Sub
    
    Private Sub mSF2_Current()
        'Event procedure from the second subform.
        Me!txtSubForm = mSF2.Name & " ID= " & mSF2!ID
    End Sub

  13. #13
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Thank you very much

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

Similar Threads

  1. Trigger subform events
    By Radwar in forum Forms
    Replies: 4
    Last Post: 09-29-2018, 12:01 PM
  2. Subform events do not seem to be firing
    By mantooth29 in forum Programming
    Replies: 2
    Last Post: 12-12-2013, 03:13 PM
  3. Subform Events Has No BeforeUpdate Event
    By CementCarver in forum Programming
    Replies: 1
    Last Post: 06-25-2013, 01:53 PM
  4. Nested Subform's control events
    By amrut in forum Forms
    Replies: 3
    Last Post: 01-10-2013, 02:19 PM
  5. Can't Find Subform Events
    By vdanelia in forum Forms
    Replies: 4
    Last Post: 02-28-2011, 02:23 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