ACC2000: How to Simulate Control Arrays in Visual Basic for Applications (210232)


The information in this article applies to:

  • Microsoft Access 2000
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.

  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. 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]
  3. 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
  4. Save the Test1 form and open it in Form view.
  5. Click the FillFields button.

    Note that each text box is assigned a corresponding element of the array Trees.

REFERENCES

For more information about working with arrays, click Microsoft Visual Basic Help on the Help menu, type using arrays in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbhowto kbinfo kbProgramming kbusage KB210232
©2004 Microsoft Corporation. All rights reserved.