Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Simulate Control Arrays in Visual Basic for Applications

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

View products that this article applies to.

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

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Keywords: KB210232, kbusage, kbprogramming, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 210232
Revision : 4
Created on : 10/11/2006
Published on : 10/11/2006
Exists online : False
Views : 340