vba 创建一个类来处理访问表单控件事件

Creating a Class to Handle Access Form Control Events

提问by Jiminy Cricket

I'm trying to create a Class which will handle multiple Control Events in Access. This is to save the repetition of typing out many lines of identical code.

我正在尝试创建一个类，它将处理 Access 中的多个控制事件。这是为了避免重复输入多行相同的代码。

I've followed the answer located on the following page, but with a few adjustments to tailor it to Access rahter than Excel.

我遵循了位于下一页的答案，但进行了一些调整以使其更适合 Access 而不是 Excel。

How to assign a common procedure for multiple buttons?

如何为多个按钮分配一个通用程序？

My Class code below:

我的课程代码如下：

Option Compare Database

Public WithEvents ct As Access.CommandButton 'Changed object type to something recognised by Access

Public Sub ct_Click()
    MsgBox ct.Name & " clicked!"
End Sub

My Form code below:

我的表单代码如下：

Option Compare Database
Private listenerCollection As New Collection
Private Sub Form_Load()
Dim ctItem
Dim listener As clListener

For Each ctItem In Me.Controls
    If ctItem.ControlType = acCommandButton Then 'Changed to test whether control is a Command Button
        Set listener = New clListener
        Set listener.ct = ctItem
        listenerCollection.Add listener
    End If
Next

End Sub

I have noted with comments where I have made changes to the (working) Excel code. I think the problem comes with the object declaration in the Class. Note: no errors are thrown during this procedure; it simply doesn't trigger the event.

我在评论中注意到我对（工作）Excel 代码进行了更改。我认为问题出在类中的对象声明上。注意：在此过程中不会抛出任何错误；它根本不会触发事件。

Thanks in advance!

提前致谢！

Edit:

编辑：

I've since narrowed the problem down to there being no '[Event Procedure]' in the 'On Click' Event. If I add it manually, the Class works as expected. Obviously, I don't want to have to add these manually - it defeats the object. Any ideas how I would go about this?

我已经将问题缩小到“点击”事件中没有“[事件过程]”。如果我手动添加它，类会按预期工作。显然，我不想手动添加这些 - 它会破坏对象。任何想法我会怎么做？

采纳答案by Brad

In your OnLoad event you can add this line

在您的 OnLoad 事件中，您可以添加这一行

Dim ctItem
Dim listener As clListener

For Each ctItem In Me.Controls
    If ctItem.ControlType = acCommandButton Then 'Changed to test whether control is a Command Button
        Set listener = New clListener
        Set listener.ct = ctItem
        listener.ct.OnClick = "[Event Procedure]"  '<------- Assigned the event handler
        listenerCollection.Add listener
    End If
Next

Although I'm not sure if this is more is less code than just double clicking in the OnClick in the designer and pasting in a method call. It's cool regardless.

尽管我不确定这是否比在设计器中双击 OnClick 并粘贴到方法调用中的代码更少。不管怎样都爽。

Edit:You could change your class like this

编辑：你可以像这样改变你的班级

Public WithEvents ct As Access.CommandButton 'Changed object type to something recognised by Access

Public Function AddControl(ctrl as Access.CommandButton) as Access.CommandButton
    set ct = ctrl
    ct.OnClick = "[Event Procedure]"
    Set AddControl = ct
End Function

Public Sub ct_Click()
    MsgBox ct.Name & " clicked!"
End Sub

Then in your form you can add a ct like this

然后在您的表单中，您可以添加这样的 ct

For Each ctItem In Me.Controls
    If ctItem.ControlType = acCommandButton Then 'Changed to test whether control is a Command Button
        Set listener = New clListener
        listener.AddControl ctItem
        listenerCollection.Add listener
    End If
Next

Now the event handler is added in the class.

现在事件处理程序被添加到类中。

回答by phillfri

A Generic Approach to handling Access Form Controls input with a class module:

使用类模块处理访问表单控件输入的通用方法：

This code was crafted to handle an application written within a popup window. The Main Form contains a tab control where each tab contains its own subform to either a linked child table or an independent table. The use or non-use of a tab control shouldn't make any difference to the class module processing.

此代码旨在处理在弹出窗口中编写的应用程序。主窗体包含一个选项卡控件，其中每个选项卡都包含其自己的子窗体，链接子表或独立表。选项卡控件的使用或不使用不应对类模块处理产生任何影响。

The code can be trimmed to meet your application's needs. For example one could remove controls that one is not using from the class module. Likewise, the controls collection subroutine can be selective by using the TypeName(Ctl) statement to filter the controls that get added to the collection.

可以修剪代码以满足您的应用程序的需要。例如，可以从类模块中删除未使用的控件。同样，通过使用 TypeName(Ctl) 语句过滤添加到集合中的控件，控件集合子例程可以是有选择性的。

In a class module called clsMultipleControls put the following code.

在名为 clsMultipleControls 的类模块中放置以下代码。

Option Compare Database
Option Explicit

Private m_PassedControl As Control

Private WithEvents atch As Attachment
Private WithEvents bfrm As BoundObjectFrame
Private WithEvents chk As CheckBox
Private WithEvents cbo As ComboBox
Private WithEvents btn As CommandButton
Private WithEvents cctl As CustomControl
Private WithEvents img As Image
Private WithEvents lbl As Label
Private WithEvents lin As Line
Private WithEvents Lst As ListBox
Private WithEvents frm As ObjectFrame
Private WithEvents optb As OptionButton
Private WithEvents optg As OptionGroup
Private WithEvents pg As Page
Private WithEvents pgb As PageBreak
Private WithEvents Rec As Rectangle
Private WithEvents sfm As SubForm
Private WithEvents tctl As TabControl
Private WithEvents txt As TextBox
Private WithEvents tgl As ToggleButton

Property Set ctl(PassedControl As Control)
Set m_PassedControl = PassedControl

Select Case TypeName(PassedControl)
Case "Attachment"
    Set atch = PassedControl
Case "BoundObjectFrame"
    Set bfrm = PassedControl
Case "CheckBox"
    Set chk = PassedControl
Case "ComboBox"
    Set cbo = PassedControl
Case "CommandButton"
    Set btn = PassedControl
Case "CustomControl"
    Set cctl = PassedControl
Case "Image"
    Set img = PassedControl
Case "Label"
    Set lbl = PassedControl
Case "Line"
    Set lin = PassedControl
Case "ListBox"
    Set Lst = PassedControl
Case "ObjectFrame"
    Set frm = PassedControl
Case "OptionButton"
    Set optb = PassedControl
Case "OptionGroup"
    Set optg = PassedControl
Case "Page"
    Set pg = PassedControl
Case "PageBreak"
    Set pgb = PassedControl
Case "Rectangle"
    Set Rec = PassedControl
Case "SubForm"
    Set sfm = PassedControl
Case "TabControl"
    Set tctl = PassedControl
Case "TextBox"
    Set txt = PassedControl
Case "ToggleButton"
    Set tgl = PassedControl
End Select

End Property

At the top of the Main Form module place the following code.

在主窗体模块的顶部放置以下代码。

Public collControls As Collection
Public cMultipleControls As clsMultipleControls

In the Load event of the Main Form place the following code.

在主窗体的加载事件中放置以下代码。

GetCollection Me

At the bottom of the Main Form code place the following recursive public subroutine:

在主窗体代码的底部放置以下递归公共子程序：

Public Sub GetCollection(frm As Form)
    Dim ctl As Control

    On Error Resume Next
    Set collControls = collControls
    On Error GoTo 0

    If collControls Is Nothing Then
        Set collControls = New Collection
    End If

    For Each ctl In frm.Controls
        If ctl.ControlType = acSubform Then
            GetCollection ctl.Form
        Else
            Set cMultipleControls = New clsMultipleControls
            Set cMultipleControls.ctl = ctl
            collControls.Add cMultipleControls
        End If
    Next ctl

end sub

I'd advise giving each control in the form and its subforms a unique name so you can easily utilize the Select statement based on the control name to effectuate processing control in each class module event. For example, each textbox change event will be sent to the txt_change event in the class module where you can use the m_PassedControl.name property in a select statement to direct which code will be executed on the passed control.

我建议给表单中的每个控件及其子表单一个唯一的名称，以便您可以轻松地利用基于控件名称的 Select 语句来实现每个类模块事件中的处理控制。例如，每个文本框更改事件都将发送到类模​​块中的 txt_change 事件，您可以在其中使用 select 语句中的 m_PassedControl.name 属性来指示将在传递的控件上执行哪些代码。

The select event is quite useful if you have multiple controls that will receive the same post entry processing.

如果您有多个控件将接收相同的输入后处理，则选择事件非常有用。

I use the Main Form Load event rather than the Activate event because a popup form (and its subforms) do not fire the Activate or Deactivate events.

我使用 Main Form Load 事件而不是 Activate 事件，因为弹出窗体（及其子窗体）不会触发 Activate 或 Deactivate 事件。

One can also pass the m_PassedControl on to a subroutine in a regular module if you have you have some lengthy processing to accommodate.

如果您有一些冗长的处理需要适应，您还可以将 m_PassedControl 传递给常规模块中的子例程。

Unfortunately, Access does not automatically fire VBA events unless you actually set the event up in the VBA module. So if you want to use a textbox change event you have to make sure the textbox change event is actually set up in applicable vba module. You don't need to add any code to the event, but the empty event must be there or the event and its class module equivalent will not fire. If anyone knows of a work around for this I'd be glad to hear about it.

不幸的是，除非您在 VBA 模块中实际设置了事件，否则 Access 不会自动触发 VBA 事件。因此，如果您想使用文本框更改事件，则必须确保在适用的 vba 模块中实际设置了文本框更改事件。您不需要向事件添加任何代码，但空事件必须存在，否则事件及其等效类模块将不会触发。如果有人知道解决此问题的方法，我会很高兴听到它。

I found this basic class module structure in an Excel userform code example at http://yoursumbuddy.com/userform-event-class-multiple-control-types/. It's a flexible structure. I have created versions that work with Excel userforms, Excel worksheets with activex controls, and now for Access forms.

我在http://yoursumbuddy.com/userform-event-class-multiple-control-types/的 Excel 用户表单代码示例中找到了这个基本类模块结构。这是一个灵活的结构。我已经创建了可用于 Excel 用户表单、带有 activex 控件的 Excel 工作表以及现在用于 Access 表单的版本。

Followup Note: The above code works fine with 64 bit Access 2013 on 64 bit Windows 10. But it fails on 64 bit Access 2013 on 64 bit Windows 7 when you try to close the main form. The solution is to move the following code from the main form to a VBA module.

后续注意：上述代码在 64 位 Windows 10 上的 64 位 Access 2013 上运行良好。但是当您尝试关闭主窗体时，它在 64 位 Windows 7 上的 64 位 Access 2013 上失败。解决方案是将以下代码从主窗体移动到 VBA 模块。

Public collControls As Collection
Public cMultipleControls As clsMultipleControls