ACC: Simulating a Control Array in Visual and Access Basic

• Microsoft Access version 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

This article describes 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.

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.

EXAMPLE

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.

Keywords          : kbprg kbusage PgmHowTo
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto