CODEWITHSUNDEEP
ms-accessms-access-2007vba
Christopher Griffith
Christopher Griffith

Reputation: 31

Microsoft Access: Attempting to detect an insert triggered by a subform within the parent form

Is it at all possible to detect an insert operation performed by a subform while still in the parent form?

To clarify: I have a series of forms for data entry, they each have a button for adding an entry to the appropriate table (using the data provided in the form). I am attempting to set each of them in turn to a subform in a 'wizard' parent form that will cycle through all the data entry forms.

My problem arises when it comes to switching between forms, as it became clear that the AfterInsert event in this parent form was not detecting the insert triggered by the form contained in the subform. I know I could move the trigger for the insert to a button in the parent form; however, to my knowledge, this would require setting the code for the click event for each of the buttons in the data entry forms as public so that they may be called from the parent form's code. I am leery to do this and was thus hoping for other options.

Upvotes: 1

Views: 910

Answers (1)

HansUp
HansUp

Reputation: 97131

Create a public procedure in the parent form.

Public Sub Listener(ByVal pMsg As String)
    MsgBox pMsg
End Sub

Then, in each of your subforms, call that procedure from After Insert.

Private Sub Form_AfterInsert()
    Dim strMsg As String
    strMsg = Me.Name & " inserted row."
    Call Me.Parent.Listener(strMsg)
End Sub

If the subform may also be used stand-alone (without a parent), Me.Parent will throw error #2452, "The expression you entered has an invalid reference to the Parent property." You can create a separate function to check whether the current form has a parent, and base your code on the function's return value.

Private Sub Form_Open(Cancel As Integer)
    Dim strPrompt As String
    If HaveParentForm(Me) = True Then
        strPrompt = "I am a subform to '" & _
            Me.Parent.Name & "'."
    Else
        strPrompt = "I am a top level form."
    End If
    MsgBox strPrompt
End Sub

The function ...

Public Function HaveParentForm(ByRef frm As Form) As Boolean
    Dim blnReturn As Boolean
    Dim strMsg As String

On Error GoTo ErrorHandler

    blnReturn = (Len(frm.Parent.Name) > 0)

ExitHere:
    HaveParentForm = blnReturn
    On Error GoTo 0
    Exit Function

ErrorHandler:
    Select Case Err.Number
    Case 2452 ' The expression you entered has an invalid '
              ' reference to the Parent property. '
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.Description _
            & ") in procedure HaveParentForm"
        MsgBox strMsg
    End Select
    blnReturn = False
    GoTo ExitHere
End Function

Upvotes: 1

Related Questions