Is it possible to raise events on a subform's form, when that subform is bound to a table?

Question

I've got a form with a subform which is bound to a temporary table. The temporary table contains the result of a pivot, but I'd like users to be able to edit that pivot and then push the changes to the source table.

To do this, I'd like to fire events on AfterInsert, AfterUpdate and Delete so I can act on changes. As I understand it, the subform's form property refers to a temporary datasheet form when the subform is bound to a table. However, I can't get this temporary form to raise any events.

MCVE:

I have a database with a single table, Table1, a single form, Form1, and that form has a single subform control which is unbound.

I have a class, Class1, with the following code:

Private WithEvents subformForm As Access.Form

Public Sub Init(subformControl As Access.SubForm)
    Set subformForm = subformControl.Form
    subformForm.OnCurrent = "[Event Procedure]"
End Sub

Private Sub subformForm_Current()
    MsgBox "Current!"
End Sub

The form, Form1, has the following code:

Private c1 As Class1

Private Sub Form_Load()
    sub1.SourceObject = "Table.Table1"
    Set c1 = New Class1
    c1.Init sub1
End Sub

However, when I move about records, add records, and do whatever in that subform, the subformForm_Current event never fires.

Answer 1

It's because the subform object isn't a form having a code module. Thus, nowhere is the event procedure to run.

So, create a form in datasheetview using the table as source, having no code module, and use that as the subform:

• Your code is ignored.

Now, adjust the form to have a code module:

• Your code runs as expected.

Anyway, that's how it works for me in Access 2016.

Answer 2

As Gustav pointed out to me, a form needs a module to raise events.

This means you can't use the auto-created temporary datasheet form. But you can create your own form to take its place.

To work around the limitation, I created a form named frmDynDS, and set it's default view to datasheet view. Then, I opened the form in design view and added 255 text boxes to the form using the following code:

Public Sub DynDsPopulateControls()
    Dim i As Long
    Dim myCtl As Control
    For i = 0 To 254
        Set myCtl = Application.CreateControl("frmDynDS", acTextBox, acDetail)
        myCtl.Name = "Text" & i
    Next i
End Sub

I added a module, and added the following code to dynamically load a table into the form:

Public Sub LoadTable(TableName As String)
    Dim fld As DAO.Field
    Dim l As Long
    Me.RecordSource = TableName
    For Each fld In Me.Recordset.Fields
        With Me.Controls("Text" & l)
            .Properties("DatasheetCaption").Value = fld.Name
            .ControlSource = fld.Name
            .ColumnHidden = False
            .columnWidth = -2
        End With
        l = l + 1
    Next
    For l = l To 254
        Me.Controls("Text" & l).ColumnHidden = True
    Next
End Sub

Then, I could adjust Class1 to the following:

Private WithEvents subformForm As Access.Form

Public Sub Init(subformControl As Access.SubForm, TableName As String)
   subformControl.SourceObject = "Form.frmDynDS"
    Set subformForm = subformControl.Form
    subformForm.LoadTable TableName
    subformForm.OnCurrent = "[Event Procedure]"
End Sub

Private Sub subformForm_Current()
    MsgBox "Current!"
End Sub

And Form1 to the following:

Private c1 As Class1

Private Sub Form_Load()
    Set c1 = New Class1
    c1.Init sub1, "Table1"
End Sub

Using this approach, you can have a subform that can display tables created on the fly in datasheet view, and handle events for that subform.

You can have multiple subforms bound to frmDynDS displaying different tables, and handle events in different event handlers, on a single form.