Updating subform VBA

[lefty2cox]

I have myForm with mySubForm1. MySubForm1 is based on a select query (used the ellipsis in the record source property) which is based on myTable.

I also have another subform, mySubForm2, which is based on myQuery (created in query design). MyQuery is based myTable as well and performs a sum for me.



mySubForm2 is simply used to display summary data. When there is a change in mySubform1, I'd like mySubform2 to update and display the new data. I know I'm going to be dealing with after_update but am not sure how to do it. I've tried several things but no luck.

Thanks.

[accesstos]

Add the code below into the module of the main form and test the behavior:

Option Compare Database
Option Explicit

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Code for the main form.
'Sets an event listener to be able to catch the events of the subform 1.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private WithEvents mSF1 As Form 'Event listener of subform 1.
'

Private Sub Form_Load()
    Set mSF1 = Me.mySubForm1.Form                'Point the listener to the form of subform 1.
    mSF1.AfterUpdate = "[Event Procedure]"       'Make the event AfterUpdate enabled.
    mSF1.AfterDelConfirm = "[Event Procedure]"   'Make the event AfterDelConfirm enabled.
End Sub 

Private Sub Form_Unload(Cancel As Integer)
    Set mSF1 = Nothing 'Release the event listener.
End Sub

Private Sub mSF1_AfterDelConfirm(Status As Integer)
    If Status = acDeleteOK Then
        'A record in subform 1 has been deleted.
        'Requery the subform 2.
        Me.mySubForm2.Requery
    End If
End Sub

Private Sub mSF1_AfterUpdate()
    'A record in subform 1 has been updated.
    'Requery the subform 2.
    Me.mySubForm2.Requery
End Sub

I hope it helps,
John

[lefty2cox]

Add the code below into the module of the main form and test the behavior:

Option Compare Database
Option Explicit

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Code for the main form.
'Sets an event listener to be able to catch the events of the subform 1.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private WithEvents mSF1 As Form 'Event listener of subform 1.
'

Private Sub Form_Load()
    Set mSF1 = Me.mySubForm1.Form                'Point the listener to the form of subform 1.
    mSF1.AfterUpdate = "[Event Procedure]"       'Make the event AfterUpdate enabled.
    mSF1.AfterDelConfirm = "[Event Procedure]"   'Make the event AfterDelConfirm enabled.
End Sub 

Private Sub Form_Unload(Cancel As Integer)
    Set mSF1 = Nothing 'Release the event listener.
End Sub

Private Sub mSF1_AfterDelConfirm(Status As Integer)
    If Status = acDeleteOK Then
        'A record in subform 1 has been deleted.
        'Requery the subform 2.
        Me.mySubForm2.Requery
    End If
End Sub

Private Sub mSF1_AfterUpdate()
    'A record in subform 1 has been updated.
    'Requery the subform 2.
    Me.mySubForm2.Requery
End Sub

I hope it helps,
John

Worked perfectly. Thank you so much. I was adding code to the module for mySubForm1. Makes much more sense now.

[accesstos]

Worked perfectly. Thank you so much. I was adding code to the module for mySubForm1. Makes much more sense now.

You are welcome.

As you see, with this way, you have the absolute control of a specific loaded form in one variable.
The variable mSF1 could point to any loaded Form object, and, you have access to any property or method of that form, and in addition, the keyword "Withevents" makes available all its events.

Cheers,
John

[lefty2cox]

I don't fully understand it yet but I'll read about it some more. Works flawlessly though. Thanks again.

[lefty2cox]

This code has been working really well for a while now but I'm getting an error lately and was wondering if it makes sense. It's a weird behavior. Occasionally, it will throw the following error when loading and unloading:

The expression on Unload you entered as the event property setting produced the following error: Object class does not support the set of events.

My guess is it's my fault, because it usually is, but I can't figure it out. This problem can be fixed by opening VBA. Once I open it to the code, the problem goes away and stays away for the session.

I'm puzzled.
Thanks

Edit: Sorry about that...I thought I replied with quote but I'm referring to the bit just above that accesstos posted for the event listener.

[accesstos]

This code has been working really well for a while now but I'm getting an error lately and was wondering if it makes sense. It's a weird behavior. Occasionally, it will throw the following error when loading and unloading:

The expression on Unload you entered as the event property setting produced the following error: Object class does not support the set of events.

My guess is it's my fault, because it usually is, but I can't figure it out. This problem can be fixed by opening VBA. Once I open it to the code, the problem goes away and stays away for the session.

I'm puzzled.
Thanks

Edit: Sorry about that...I thought I replied with quote but I'm referring to the bit just above that accesstos posted for the event listener.

Hi!

If you didn't change something in the code or the objects of the form, try at first a Compact and Repair. Usually, this resolves issues such this you are mention.

[lefty2cox]

Compact / Repair didn't work. I did change stuff on the form... added a button or two...maybe another subform.

I found that I have to open the actual class object in VBA. Opening VBA alone doesn't solve it. I have to open the class object for the specific form. Then I can close it and all is well until I restart the program again.

I'll try to remove things one at a time to pin it down and report back.

Thanks

[accesstos]

Did you Compile your code before Compact and Repair?
Try to keep compiled and saved your project before the Compact and Repair.

Sorry, I can't help further with provided info.

[lefty2cox]

I did not the first time. But after reading your post, I went ahead and repeated with compiling. That worked. Thank you.

[accesstos]

I did not the first time. But after reading your post, I went ahead and repeated with compiling. That worked. Thank you.

I apologize, I had to mention it at first.

Glad you have it resolved.

Cheers,
John