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.

Sufism

Where did the notion of a temporary datasheet form come from? As I understand it, after .SourceObject = "Table.Table1" the subform shows a table datasheet, and that simply doesn't have these properties. – Purcell

@Purcell As I understand it, it's auto created. subformForm.OnCurrent = "[Event Procedure]" doesn't trigger an error, so that property is present. – Sufism

@Purcell Also note that other form properties, such as .AllowAdditions, are also present, can be changed and work fine. It's just handling events that's causing problems for me. – Sufism

Hmmm This is a good question. The subform has Enter/Exit events and an On Current event that fires for each record in the subform (see here) but not sure its possible to detect from a class module. More discussion also here. This isn't event-related but a handy guide for referring to subforms from elsewhere. – Burgrave

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.

Boutin

You're certainly right. I hadn't realized a form needs a module, even when you're handling the event from another class module. Apparently, a form with HasModule set to false is a lightweight object, doesn't support events, and can't be created using Dim myVar As New Form_Form1. This means I have to rethink my approach, because you can't add modules to these temporary forms on the fly... – Sufism

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.

Sufism