traversing items in a collection in msacces 2002

[Ryobi]

Hi To All,

I have quick question in MS Access 2002. I writing a program that has combo boxes that I want to setup with same information using VBA code. My combo boxes are label combo1, combo2, Etc up 90. I want to do somthing like

Dim Counter as integer
Counter = 0
For Each Item in Combo ' Combo is are the combo boxes
Combo & Counter = "Sample
Counter = Counter + 1 ' Combo Box Counter
[Combo] & Counter.Rowsource = "Sample"
If Counter > 90
Exit
End if
Next Item

Know the above code does not work. How can I get it to work ?
Thanks

[Hack]

You have 90 Comboboxes???????????????????? I'm glad I don't have to deal with that. What are you doing with that many of any control?

Is this conglomoration on a UserForm or on a Excel Spreadsheet? If userform try

Dim ctrl As Control
For Each ctrl In Me.Controls
   If TypeOf ctrl Is ComboBox Then
      ctrl.Additem "Whatever"
   End If
Next

If on a excel spreadsheet try

 Dim wks As Worksheet
 Dim OLEObj As OLEObject
 Set wks = Worksheets("Sheet1")

 For Each OLEObj In wks.OLEObjects
     If TypeOf OLEObj.Object Is MSForms.ComboBox Then
            OLEObj.Object.Additem "Whatever"
     End If
 Next

[Ryobi]

Thanks for the code, I made the following modifications, but I still can not get the Cboname to Equal to the ctl.Name. I think the problem is with the
CboCount being an integer. I have converted the Cbocount to a string but I Think that I need to strip the Leading spaces. Any Ideas. I am close to what I need, but I can see to get it.


Dim Ctl As Control
Dim Cboname As String
Dim CboCount As Integer
CboCount = 0
For Each Ctl In Me.Controls
If Ctl.ControlType = acComboBox Then
CboCount = CboCount + 1
Cboname = "Combo" & CboCount
If Ctl.Name = Cboname Then
Stop
Ctl.RowSource = "Test"
End If
On Error Resume Next
End If
Next

Thanks

[westconn1]

maybe try like for combo 1 to combo 90

for i = 1 to 90 'number of combos
    me.controls("combo" & i).text = "combo" & i ' do something
next

take out the on error resume next

[Ryobi]

The code
1.
for i = 1 to 90 'number of combos
2.
me.controls("combo" & i).text = "combo" & i ' do something
3.
next

will not work because the code is looking at the "text" of the control, I need the
"name" of the control. Even if the code worked the problem is that The I is an integer and the leading spaces need to be striped. Something the mid(str(I,2,2)
might work, but some reason it is not.

[Ryobi]

I Have found what the problem is in my code. My code the Len of the ctl.name is not the same as the length of the cboname which means that I need to strip the spaces for the ctl.name or use some function to make them equal. The function that I used is Mid(Ctl.Name,1,len(Cboname)) to extract the len of CboName and to search of only for the number of characters on the
CboName which I what I need. Below is the code that I used and is working


For Each Ctl In Me.Controls
If Ctl.ControlType = acComboBox Then
CboCount = CboCount + 1
Cboname = "Combo" & CboCount

If Mid(Ctl.Name, 1, Len(Cboname)) = Cboname Then
Stop
Ctl.RowSource = "Test"
End If
On Error Resume Next
End If
Next

[Ryobi]

The code works, but It not assigning the values to the combo boxes.
For some reason Ctl.RowSource = "Test" is not working. To test what the program is doing, create a form in msaccess, create three combo (any number that you want and name the combo boxes combo1, comb2, etc and the copy the on the when open event on the form. Anybody has any ideas ?

Thanks

[Ryobi]

I am glad to report after doing research and brain storming I have found the following to work. The code will work for all combo boxes, and labels as long as the name of the combo boxes and labels have a common pre-fix.

Dim CtlCombo As Control
Dim VarData As Variant
Dim I As Integer

For I = 1 To 90 ' Any size that you need the make
VarData = "Combo" & I
Set CtlCombo = Me(VarData)
CtlCombo.RowSource = "What ever value you want"

Next I

Thanks to all who replied.

[westconn1]

me.controls("combo" & i).rowsource = "test" & i

should also have worked, i only used the text property as an example