Using a Button Class in Access

[Ed Ferrero]

Hi, using Access 2003 (and 2000, and 97 when asked to:^)

I am trying to automate the behaviour of many buttons in Access at once.
Something like a control array in VB. I can do this in Excel, but can't
figure out if there is an Access equivalent. Has anyone any pointers for me?

Firstly I built a class module called BtnClass with the following code;

Option Compare Database

Public WithEvents ButtonGroup As CommandButton


Private Sub ButtonGroup_Click()
MsgBox "You clicked - " & ButtonGroup.Name
End Sub

Then I built Form1, placed two button on it, and added the following code;

Dim Buttons() As New BtnClass

Private Sub Form_Load()
Dim ButtonCount As Integer
Dim ctl As Control

ButtonCount = 0

For Each ctl In Me.Controls
If TypeName(ctl) = "CommandButton" Then
ButtonCount = ButtonCount + 1
ReDim Preserve Buttons(1 To ButtonCount)
Set Buttons(ButtonCount).ButtonGroup = ctl
End If
Next ctl

End Sub

If I run the form, the Form_Load procedure works fine, but the button events
do not fire.

Now, this works in Excel except that in Excel a CommandButton is a member of
MSForms, whereas in Access CommandButtons are members of Access.

Is it possible to make this work in Access?

Ed Ferrero
http://edferrero.m6.net