Does anyone know how to mimic the VB ControlArray functionality in Access? I have a form like the following:

cboPayee1, cboPayee2, ...
txtAmount1, txtAmount2, ...

I am looking for a way to reference each control without needing to explicitly name the control.

Any help is greatly appreciated.

Kenny Alligood

__________________
Kenny Alligood

Dim ctrl as Control

For each ctrl....

Regards,

Beth M

To expand on what Beth answered.
   Plus if you want to apply your action to only textboxes or checkboxes, etc. you can use the ControlType property, e.g.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Here's another way to do it, too - I use this frequently within
my applications:

Dim strControlName As String
Dim intIndex As Integer

For intIndex = 1 to 10 Step 1
   strControlName = "txtAmount" & intIndex
   Me(strControlName) = intIndex
Next intIndex

Grant you this is a simple example which will populate the
controls txtAmount1 through txtAmount10 with a sequence number
of 1 through 10 in the actual textboxes themselves. The main
point is that it is possible to create the name of the control
to reference it by using a string variable, rather than always
having to refer to the control by using Me.txtAmount1,
Me.txtAmount2, etc.

While VB allows you to use Me.txtAmount(1), you can simply build
the control name and reference it as needed.

Just to build a bit on the last post, you can set an array of controls in the Open event of the form. For example, if you have a grid of 8 columns by 6 rows of textboxes, you can dimension a form module level array of textboxes:

Dim mtxtCtl as TextBox(7,5)

Then in the form open event:

Dim lngI as Long
Dim lngJ as Long

For lngI = 0 To 7 'or ubound first dimension - 1
    For lngJ = 0 to 5 'or ubound 2nd dimension - 1
        Set Me(mtxtCtl(lngI, lngJ) = Me("textbox" & lngI & lngJ)
    Next
Next

Thereafter, reference the elements of the array of controls like a conventional control array and you have the benefit of intellisense and autocomplete plus as many array dimensions as you may want. You may also redim and set array size dynamically depending on your form needs but that number of controls must actually exist on the form. I like to use subforms to hold larger or smaller maximum numbers of controls and set the approprate subform at runtime. Multidimension capabilities are unavailable in a conventional VB control array and control arrays don't exist in .Net. Accessing the elements of the array of controls is also much faster this as opposed to the manner in which the references are set as each element is a simple long that acts as a pointer to the actual control and these references can be set to sub or subsub forms or to controls on other forms. The (comparatively) slow step of type conversion to string (the index in the string name), concatenation and resolving the control reference is done only one time at the form load. Thereafter you have an extremely fast and efficient direct control reference that iterates much faster and more efficiently.

This technique is useful for the situations where you would like to do things like build a grid of controls to house a calendar grid or build your own calendar grid and users will navigate in such a fashion as the data and data format changes dynamically. Using the Getrows method of a recordset, you can use the resulting data array to fill the controls using a common index mapping and scroll the display with a single offset for row and column. This method can be used to good effect with conditional formatting based on data value and the ability to change the properties of a textbox control through a direct reference to the control is fast and efficient. You can also use a third array dimension to house control attributes such as text, backcolor, bolding, forecolor or add a column to the existing array to hold a bit field with a single numeric value that contains flags that control the attributes.

Bottom line, all the functionality of a fixed upper limit number of controls of a type can be met and exceeded by creating an array of controls.

Ciao

Just another couple of comments:

    Of course

Dim mtxtCtl as TextBox(7,5)

    is incorrect. Proper usage would be:

Dim mtxtCtl(7, 5) As TextBox

The other big advantage of control arrays is in writing event procedures. To avoid having to write an event procedure for each control in an array of controls, Access provides the facility to code an appropriately scoped function procedure. For example, if a grid of textboxes contains a series of file names that you would like to open with a double click on the text box, you can write a private function in the code behind the form and call it by setting a property in the property sheet of the control. In this case, if you write a function named dblClkRun, you can set the On Dbl Click property to =dblClkRun(). When you copy and paste this control to build an array, the property remains set just as the length and width remain set in the copy and the control is hooked to the event procedure without writing additional code. For procedures like mouse over, mouse move, click, double click, got focus, before/after update, you can pass in a reference to the control to be operated on:

=dblClkRun([Screen].[ActiveControl])

which is not subject to any of the ususal issues surrounding using the ActiveControl as those kinds of events only apply to the active control.

Alternatively, you can edit individual control event procedure properties by adding a specific parameter that the function can use to identify the control on which to operate. While not as easy to implement as simply copying and pasting a control, it is a simple matter to edit the property sheet such that each control passes in a name or a number:

txt1 could use: =dblClkRun(1)
txt2 : =dblClkRun(2)
txt3 : =dblClkRun(3)

or some variation therof.

In an application I've written, I have some textboxes that display dates. Given the confustion that often arises from international date formatting issues, I thought it expedient to use a calendar form from which dates could be chosen. In the pursuit of consistency, I chose a textbox size and date format and set the properties and created a single function procedure that is passed a reference to the active control. When I need a date control on a form, all that is necessary is to copy an existing instance of the control to the target form and all the functionality required to pop the calendar and set the date in the control is encapsulated with the control. And that is the case notwithstanding that the control is on a form, a sub form or a subsubform. There is no need to parse form names or level of form as the procedure receives a reference to the actual control regardless of the container.

There are other controls such as numeric calculation fields that benefit from popping a calculator and receiving the calculation value that benefit from this approach but its most useful application is in the case of arrays of controls.

Ciao
Jürgen Welz
Edmonton, Alberta
[email protected]