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



The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97

This article was previously published under Q137119

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.
  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier).
  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
          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
    						
  4. Save the Test1 form and open it in Form view.
  5. Click the FillFields button. Note that each text box is filled in with its respective name from the Click event code.

Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbhowto kbProgramming kbusage KB137119