ACC: Simulating a Control Array in Visual and Access Basic (137119)

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you how to simulate an array of controls on a form or report similar to the indexed control array functionality in Microsoft Visual Basic. Control arrays are not directly supported in Visual Basic for Applications, but you can create similar functionality by:

Including a number suffix in the name of your controls.
Setting a string variable to the name of the control.
Using the variable name when referencing the controls.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

To simulate an array of controls on a form or a report, follow these steps.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb in Microsoft Access 7.0 and 97 (or NWIND.MDB in version 2.0 or earlier). You may want to back up the Northwind.mdb (or NWIND.MDB) file or perform these steps on a copy of these databases.

Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier).

Create the following form not based on any table or query with four text boxes and a command button:

      Form: Test1
      --------------------------
      Caption: TestForm
      ControlSource:

      Text Box:
         Name: MyField0
      Text Box:
         Name: MyField1
      Text Box:
         Name: MyField2
      Text Box:
         Name: MyField3
      Command Button:
         Name: FillFields
         Caption: Fill Fields
         OnClick: [Event Procedure]

Create the following event procedure for the OnClick property of the FillFields command button:

      Sub FillFields_Click()
      On Local Error Goto Err_FillFields_Click
      Dim i As Integer, TextControl As String
      For i = 0 to 3
        TextControl = "MyField" & Format$(i) ' Create name.
        Me(TextControl) = TextControl
      Next I
      End_FillFields_Click:
        Exit Sub
      Err_FillFields_Click:
        MsgBox Error$
        Resume End_FillFields_Click

Save the Test1 form and open it in Form view.
Click the FillFields button. Note that each text box is filled in with its respective name from the Click event code.