Microsoft KB Archive/210232

= ACC2000: How to Simulate Control Arrays in Visual Basic for Applications =

Article ID: 210232

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210232



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
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.



MORE INFORMATION
To simulate an array of controls on a form or a report, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

 Start Microsoft Access and open the sample database Northwind.mdb.  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, Trees(3) As String

Trees(0) = "Birch" Trees(1) = "Maple" Trees(2) = "Chestnut" Trees(3) = "Walnut" For i = 0 To 3 TextControl = "MyField" & Format$(i) ' Create name. Me(TextControl) = Trees(i) Next i End_FillFields_Click: Exit Sub Err_FillFields_Click: MsgBox Error$ Resume End_FillFields_Click End Sub  Save the Test1 form and open it in Form view. Click the FillFields button.

Note that each text box is assigned a corresponding element of the array Trees.</ol>

<div class="references_section">