Simulating a control array on a form in Access97

Question

Is it possible to create a control array on a form in A97? How do you do this? In VB if you copy and then paste a control, you are prompted with a dialog box that asks if you want to create a control array? Is there anything like this in access?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: Access (E-mail) <accessd@mtgroup.com>
Cc: AccessL (E-mail) <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: [accessd] Can you create Control Arrays on Forms in A97 like in VB???
Date: 27 March 1999 2:53

Tom,

You cannot create control arrays in MS Acc97. But if your goal is to use the same code for a group of controls then I think you can *simulate* something like control arrays in MS Access 97 using custom classes and WithEvents feature. Enclosed you'll find a code which shows how it can be done...

HTH,
Shamil

'+ *** CMyCombo ***
Private Const cstrModuleName As String = "CMyCombo"

Private WithEvents mcbo As Access.ComboBox

Public Sub Init(ByRef rcbo As Access.ComboBox)
  Set mcbo = rcbo
  rcbo.OnEnter = "[Event procedure]"
End Sub

Public Sub Terminate()
  Set mcbo = Nothing
End Sub

Private Sub mcbo_Enter()
  mcbo.Dropdown
End Sub
'- *** CMyCombo ***

'+ *** Code Behind Form ***
Dim mcolControlArray As New Collection

Private Sub Form_Load()
  Dim ctl As Access.Control
  Dim obj As CMyCombo

  For Each ctl In Me.Form.Controls
    'If TypeName(ctl) = "ComboBox" Then
    If ctl.ControlType = Access.acComboBox Then
      Set obj = New CMyCombo
      obj.Init ctl
      mcolControlArray.Add obj, ctl.Name
    End If
  Next
  Set obj = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
  Dim obj As CMyCombo
  For Each obj In mcolControlArray
    obj.Terminate
  Next
  Set obj = Nothing
  Set mcolControlArray = Nothing
End Sub
'- *** Code Behind Form ***