Event not Firing in MS Access VBA

Question

I have a form in MS Access which has an image. The image has an Click event which opens a modal form. The modal form has an OK and Cancel button. When you click the OK button, an event is supposed to fire which tells the main form which button was clicked. (This is to simulate the DialogResult functionality in C#). However, the code in the event handler never runs.

The modal form has the following in the general declarations:

Public Event OnDialogBoxClose(NewRecordID As Long, DialogResult As DialogResults)

and the following code where the OK button is clicked:

RaiseEvent OnDialogBoxClose(NewHardwareBaseItemID, dlgresBtnOKClicked)

The main form has the following in the general declarations:

Dim WithEvents RespondQuickAddClose As Form_qckfrmHardwareBaseItemCreate

and the following event handler:

Private Sub RespondQuickAddClose_OnDialogBoxClose(NewRecordID As Long, DialogResult As DialogResults)

    MsgBox "Responding to closing of the dialog box" 'Never happens
    Me.Requery

End Sub

Can someone explain why the event handler is never called? Thanks!

Background:

The purpose of all this is to allow a modal dialog box to add an entry, then return the ID of the entry back to the main form to set the value of controls. For instance, imagine you are filling out an insurance form, and you need to select a brand of car this is not there. You click on an icon which pops up with the modal dialog box to allow you to add the car brand. Then when you click OK, it takes you back to the insurance form and selects the brand of car you just created.

This follows an example I found here: http://database.itags.org/ms-access-database/80292/

Answer 1

You're making your life way too complicated by applying concepts from a different development environment to Access VBA. While VBA does support WithEvents/RaiseEvent, there's no reason to get that complicated here.

The usual way to work with dialogs in Access is that instead of closing them, you hide them. This allows the code after the form was open to run while leaving the values in the form available for use in that code.

Sample code in the OnOpen event of a report that opens a form for collecting values to filter the report:

  Private Sub Report_Open(Cancel As Integer)
    DoCmd.OpenForm "dlgDateRange", , , , , acDialog, "ThisYear"
    If IsLoaded("dlgDateRange") Then
       With Forms!dlgDateRange
         If .Tag = "Cancel" Then
            Cancel = True
         Else
            Me.Filter = "[InvoiceDate] Between #" & !txtStart & "# AND #" & !txtEnd & "#"
            Me.FilterOn = True
            Me!lblDateRange.Caption = StrConv(Trim(("from " + varZLStoNull(Format(!txtStart, "mm/dd/yyyy"))) _
                & (" to " + varZLStoNull(Format(!txtEnd, "mm/dd/yyyy")))), vbProperCase)
         End If
       End With
       DoCmd.Close acForm, "dlgDateRange"
    End If
  End Sub

The dialog form has two command buttons, CONTINUE>> and CANCEL. The CANCEL button sets the form's tag to "Cancel" and sets the form's .Visible property to False. The CONTINUE>> button does nothing but set the form's .Visible property to False. Clicking either of those buttons allows the code to continue on the line after the form is open with the acDialog switch.

My philosophy is to make the dialogs as stupid as possible. The calling code has to know what it's looking for in the forms (i.e., you need to know the names of the controls you're reading data out of), but that could be gotten around by adding customer properties to the form. But then you have to know the property names, so you've just moved the ball.

I have also implemented this kind of thing by wrapping the dailog form in a class module, and then the calling context simply initializes an instance of the class and then pulls the values out of it at the appropriate time. But that's actually more complicated that the approach above.

Answer 2

well I don't agree to

"While VBA does support WithEvents/RaiseEvent, there's no reason to get that complicated here."

I have worked on various VB6 and VBA project. Recently I coded VBA in excel where I raised an event from winform. Few things to be considered when doing so.

1. If you are calling non-modal winform in VBA with withevents/raiseevent. It should work normally as expected. No major workaround is needed
2. If you are calling modal winform in VBA. Withevents/raiseevents may not function as per requirement. A quick workaround is to transfer data using public variables declared in the module file.

You will need to use the workaround and I believe it will work absolutely fine.