VBA for Access 2010: Dynamically creating a control and linking event handlers

Question

In Microsoft Access 2010, I am trying to dynamically create a form and then add a commandbutton to it. However, I can't figure out how to assign an event handler to that button's click (or onclick) event?

From reading extracts on the internet, I have created the following vba module code:

Option Compare Database
Option Explicit

Public Sub ProduceForm()
  Dim aForm As Form
  Dim aButton As CustomButton
  Set aForm = CreateAForm("Table1")
  Set aButton = CreateAButton(aForm, "Click me!")
  DoCmd.Restore
End Sub

Private Function CreateAForm(table As String) As Form
  Set CreateAForm = CreateForm(, table)
  With CreateAForm
    .Caption = CreateAForm.Name
  End With
End Function

Private Function CreateAButton(aForm As Form, text As String) As CustomButton
  Set CreateAButton = New CustomButton
  Set CreateAButton.btn = CreateControl(aForm.Name, acCommandButton)
  CreateAButton.SetupButton text
End Function

In accordance with the internet advice, I have then added the following class module (that I've called "CustomButton" and is referred to above):

Option Compare Database

Public WithEvents btn As CommandButton

Public Sub SetupButton(text As String)
 If IsNull(btn) = False Then
    With btn
        .Caption = text
        .OnClick = "[Event Procedure]"
    End With
 End If
End Sub

Public Sub btn_OnClick() ' or should this method just be called btn_Click()?
  MsgBox "Happy days"
End Sub

However, when I run this code and then click the button (when in form view) nothing happens?

I notice that explanations given for similar problems, but for excel 2010, give an alternative solution by writing code as a string in a "CodeModule", which I think is linked to the "vbComponents" object. This solution should work for me but I can't find this functionality in Access 2010?

Anyway, any help on this issue would be greatly appreciated.

Thanks

Tim Williams · Accepted Answer · 2015-11-06 01:39:00Z

2

EDIT - my original answer had assumed that Access behaved something like excel, which doesn't seem to be the case...

You don't need a custom class to handle events - you just pass a string to the OnClick property of your button.

This works for me - maybe needs tidying up a bit though.

Public Sub ProduceForm()
  Dim aForm As Form, strName As String
  Dim btn As CommandButton

  Set aForm = CreateAForm("Table1")

  Set btn = CreateAButton(aForm, "Click me!", "=SayHello()")
  btn.Top = 100
  btn.Left = 100

  Set btn = CreateAButton(aForm, "Click me too!", "=SayHello('World')")
  btn.Top = 1000
  btn.Left = 100

  DoCmd.OpenForm aForm.Name, , , , , acDialog

  DoCmd.Restore
End Sub

Private Function CreateAForm(table As String) As Form
  Set CreateAForm = CreateForm(, table)
  With CreateAForm
    .Caption = CreateAForm.Name
  End With
End Function

Private Function CreateAButton(aForm As Form, txt As String, proc As String) As CommandButton
  Dim btn As CommandButton
  Set btn = CreateControl(aForm.Name, acCommandButton)
  btn.OnClick = proc
  btn.Caption = txt
  Set CreateAButton = btn
End Function

'has to be a Function not a Sub
Public Function SayHello(Optional arg As String = "")
    MsgBox "Hello " & arg
End Function

edited Nov 6, 2015 at 1:39

answered Nov 5, 2015 at 22:33

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

greenbeast Over a year ago

Hi Tim and thanks for the response. I moved the aButton declaration so it was directly below "Option Explicit" (and therefore global) but the event handler would still not fire when the code was run and the button then pushed? or if i need to move it to the form code module, then how do I do that when the form (aForm) is dynamically created too?

Collectives™ on Stack Overflow

VBA for Access 2010: Dynamically creating a control and linking event handlers

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related