After update Event from datasheet subform to requery parent form

[MattSC]

Hi- I have a form (InvoiceCTDAccrualEntry) with a datasheet subform (AccrualEntrySubform) which has a datasheet subform (AccuralEntrySubform1). I'd like to requery the AccrualEntrySubform form whenever any records are updated or added/deleted on the subform (AccrualEntrySubform1). I've tried adding to the after update event of the subform as well as the exit event and the database locks up and shuts down. There is a before update event running on AccrualEntrySubform1 that pops up a msg box to confirm changes and save or undo changes, when I disable this code, the after update seems to working properly.



Here's the code that I'm using in the after update event of the subform (AccrualEntrySubform1):

Forms![InvoiceCTDAccrualEntry]![AccrualEntrySubform].Requery

Here's the code for the before update event also:

'Provide the user with the option to save/undo changes made to the record in the form

    If MsgBox("Changes have been made to this record." _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then
            DoCmd.Save
     Else
            DoCmd.RunCommand acCmdUndo
    End If

Any idea how I should update my code or maybe a different event type to fix this problem? Thank you in advance for your help.

[ItsMe]

Usually, the field that is Master link to Child is the only control that would have an effect on the subform or what the subform should display.

You can place the following in the afterupdate of a control that is being updated by the user. The before update event would not be the place for a .Requery
Forms![InvoiceCTDAccrualEntry]![AccrualEntrySubform].Requery

[Ronc]

Access custom event notification between subform to mainform is another approach to solve this. This is really not that tough to do and gives you way more control over what you want to do. The undocumented thing to know is you CANNOT put the event declaration in the subform because the mainform can't attach to the event during runtime (at least not that I can find). You CAN use an intermediate class object through which the subform is able to send events to the mainform.

BTW: This model works going both directions and even to other forms, and it is also highly reusable if a subform appears in multiple forms and you don't want to hardcode how to access parent controls (I hate that). And because custom events can have multiple event "sinks", you can have your event triggering code notify multiple forms or classes that act as event sinks for this event at one time. At one level custom events can decouple your subforms a bit so you're not so constrained by the rules Access imposes through its basic mechanisms between subforms and mainforms. You can still use bindings that cause the subform to update based on linked fields from the mainform, but you're not restricted to kludging up the BeforeUpdate Requery code you do to try and signal the mainform of something (maybe something important other than changed data, or when you don't want the mainform to requery but need it to do something else).

The steps are:
1. Create a class module for an object used to communicate between the subform and the mainform (e.g. SubFormAlert). This can be anything you want it to be. The important part is the class needs to declare the event at the beginning of the module before properties and methods (e.g. Public Event SubformChanged(TheMessage As String) ) and a method or property in the class needs to raise the event (e.g. raiseevent SubformChanged(MyMessageToTheMainform) ).

2. Somewhere in your initialization process for the application, create the class object that is public and in an appropriate context available to the mainform and subform (e.g. in mainform Public MyAlert as SubFormAlert then Set MyAlert = New SubformAlert)

3. In the subform, when you want to alert the mainform it did something, have the subform invoke the method or property in the shared object that fires the alert (e.g. MyAlert.SendAlert = MyMessage where MyMessage is anything you decide you want to send based on the definition of the property in the class.

4. In the mainform, bind to the custom alert as a sink using the WithEvents statement at the top of the form module before any procedures (e.g. Private GetAlert as SubFormAlert)

5. In the mainform, add the event handler. Look in the combobox at the top of the source edit window on the left and find GetAlert. In the right combobox you'll see SubFormAlert, click that and it puts a code stub in your source.

6. In the code stub, put in the code to process your alert.

Here is an example of this passing a simple string from the subform to the mainform.
================================================== ==
Class Object Source for SubformAlert
Option Explicit
Public Event SubformChanged(TheMessage As String) ' I could have declared this as receiving a complex/comprehensive object vs. a string, including the class I'm defining here

Dim local_MyMessage as String ' I could have created a complex/comprehensive object in the SubFormAlert that stores lots of stuff

Public Property Get Message() As String

Message = local_MyMessage

End Property

Public Property Let Message(TheMessage As String)

Local_MyMessage = TheMessage

Raiseevent SubformChanged(Local_MyMessage)

' If I had declared the event as receiving an object of this class (my SubformChanged class)
' I could pass myself to the event sink code by doing
' raiseevent SubformChanged( Me )
' This could allow you do do all kinds of sophisticated prep work and save
' all kinds of information inside this eventing class before the event gets raised
' and the receivers can then use all the info through the properties you create in the class.

End Property

================================================== ====================

Mainform Code Module:
Option Explicit
Public MyAlert As SubformChanged

Private Sub MainformInitializationCode()

Set MyAlert = New SubformChanged

End Sub

‘ The following procedure gets inserted when you use the Access source editing screen drop-down to add the event handler

Private Sub MyAlert_SubformChanged(TheMessage As String)

MsgBox "The message from the subform is:” & vbCrLf & TheMessage

End Sub

================================================== ====================
Subform Code Module:
Option Explicit

‘ Because the mainform declared the MyAlert object as public during its initialization, just use it.

Private Sub MySubformEventFiringRoutine()

‘ Under conditions I decide do the following

MyAlert.Message = “The message I want to send the subscribers”

End Sub