Events for form/subform

[lefty2cox]

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

[Micron]

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

Forms!MainFormName.TotalFieldOnMainForm = Me.FieldOnSubform+Forms!MainformName.FieldOnMainForm

[lefty2cox]

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.

[Micron]

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.

[lefty2cox]

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

[Micron]

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.

[lefty2cox]

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.

[accesstos]

Also, give a try to the

Me.Parent.Refresh

in the AfterUpdate event of each subform.

[lefty2cox]

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.

[lefty2cox]

Also, give a try to the

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?

[accesstos]

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

[accesstos]

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:

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

[lefty2cox]

Thank you very much