Wanted: An array of combo boxes (1 Viewer)

[FUBAR]

Hello all,

I have some code that I want to run on a few combo boxes located on the same form. Instead of copying the code for as many combo boxes I have, I wanted to load all of the combo boxes into an array, and then iterate through that array while running the code.

Problem is I can't figure out how to do this programmatically (I'm hoping its just syntax errors)

This is the current test that I'm kicking around...

*** This Works ***
Dim myArray(0) as Variant
Dim fubar as Integer

myArray(0) = 10
fubar = myArray(0)
msgbox fubar
***

*** This Doesn't Work ***
Dim myArray(0) as Variant
Dim fubar as Access.ComboBox

myArray(0) = Me.cmbYear 'cmbYear exists and contains data
fubar = myArray(0)
msgbox fubar.value
***

Any help would be appreciated.

Thanks in advance,
FUBAR

 

[llkhoutx]

"Run a combo box" has no meaning in Access? What are you trying to do.

 

[FUBAR]

Well if you look at my second chunk of code....

Say I had 20 combo boxes and I wanted to create a msgbox for each combo box value when the user clicks a button (not what I want to do but is an example)

I would want to use the array as an easier way to reference the combo boxes on the form

For x = 0 to 19
MsgBox myArray(x).Value
Next x

or

For x = 0 to 19
fubar = myArray(x)
MsgBox fubar.Value
Next x

Understand?

 

[David R]

Search the Forms forum or Modules forum for 'loop controls'. There are ways to check for controls with a certain Tag value and work on them.

 

[FUBAR]

Thanks for the tip...

Read and tested some of the Control/Tag stuff and know how I could use it to do what I want...but it's still not exactly what I want (I know I'm being picky)

Is it even possible to make an Array of references that reference Combo Boxes (or for that matter Text Boxes, Labels etc.)

If not I'll give in and use the Control/Tag stuff.

Thanks for all the help,
FUBAR

"If something is hard to do then it's not worth doing" -Homer Simpson

"I want to be using Java!" - FUBAR

 

[David R]

Pat Hartman has said previously that Access does not have an ability to form "an array of controls". I trust her judgement in the matter since I've never tried personally.

 

[FUBAR]

Victory is Mine!!!

Well after I saw the post that said it wasn't possible to create an array of combo boxes I gave up on the idea.

However, today I stumbled upon some code that DOES allow for this and I'm kicking myself because it was a small error on my part that was preventing me from achieving my goal.

To anyone that's actually interested, here's a small example of how to put 4 combo boxes into an array, and then cycle through the array using a for loop to output the combo box values as message boxes.

Dim cmbBoxArray(3) as ComboBox

Set cmbBoxArray(0) = Me!cmbBox0
Set cmbBoxArray(1) = Me!cmbBox1
Set cmbBoxArray(2) = Me!cmbBox2
Set cmbBoxArray(3) = Me!cmbBox3

for x = LBound(cmbBoxArray) to UBound(cmbBoxArray)
msgbox cmbBoxArray(x).Value
next x

 

[Alexandre]

To my knowledge the most ususal solution to that problem does not involve arrays at all but uses the systematic labelling of controls you've set up.
Ex:
for i=1 to 10
Debug.Print Me.Controls("CmbBox" & i)
next

 

[The_Doc_Man]

From a pragmatist's standpoint, I'm glad you solved your problem. From a purist's standpoint, however, I would suggest that you take a different view if you ever have to do it again. The setup I will suggest is trivial and the execution is really not worse than having a definite-execution loop, For I = 1 to n/Next I.

Where you had your loop, consider this approach in its place...

First, name the control boxes that you want to be treated this way with something unique, like CBoxAry001, CBoxAry002, etc. This is easy enough when you are building the form, just go into the control's property sheets to assert the names.

Next, replace your loop with something like this:

Sub MyCode(...)

Dim ctCBox as Control

For Each ctCBox in Me.Controls

If ctCBox.Type = acComboBox Then

If Left$(ctCBox.Name, 7) = "CBoxAry" then

{{{do your thing}}}

End If

End If

Next ctCBox

The reason you would wish to do it this way is simple: This code is portable and compact. You can use this on ANY form for which the particular treatment is desired, and with minimum setup actions. In fact, a subroutine to do this could be put into a general module and called from your class modules on your forms. This method uses no arrays and no extra local memory; it also avoids the need to hard-code the actual number of combo boxes to be affected. In fact, if you change the design of the form to add or remove a combo box, you don't have to change this code at all. And you can add or remove a combo box from the list to be affected just by changing its name.

Using an array surely works, don't get me wrong about that, but it is a short-sighted approach that is not cheaply reusable.

By the way, the above approach works for any type of control that you might wish to affect this way, just by changing the constant for the control type. You can use the object browser for the Access library, module AcControlTypes, to see the Access enumerated control-type constants.

 

[FUBAR]

I fully understand the lack of reusability with using arrays, but really I was just using an array as a simple example.

My combo boxes are not actually named cmbBox0 (I practice good naming conventions) and the array can easily be turned into a collection or another collection-type object that I could program myself (I'm primarily a Java programmer.)

I understand that the use of the Controls type is the normal answer to do what I have accomplished, I just thought that it was a waste to cycle through all of the control types when really I just want a few combo boxes. This might go against the VB/Access purists...but hey like I said...I'm a Java programmer