Loop - Set properties of controls (textbox, combobox, etc)

[Cosmos75]

I have a form with four sets of 15 comboboxes.
cboSetA1, cboSetA2, ..., cboSetA15
cboSetB1, cboSetB2, ..., cboSetB15
cboSetC1, cboSetC2, ..., cboSetC15
cboSetD1, cboSetD2, ..., cboSetD15

I need to set the properties for three of the sets based on the values in the first set.

so instead of having code like this in the forms After Update or Dirty event

Select Case me.cboSetA1.columns(1)

Case = 1
me.cboSetB1.visible = True
me.cboSetC1.visible = False
me.cboSetD1.visible = False

Case = 2
me.cboSetB1.visible = False
me.cboSetC1.visible = True
me.cboSetD1.visible = False

Case = 3
me.cboSetB1.visible = False
me.cboSetC1.visible = False
me.cboSetD1.visible = True

End Select

but I'd have to repeat that 15 times. Or have the code repeated for each comboxes event (instead of the form's event).


Is there a way to do this with a loop? Something like

For i = 1 to 15

Select Case me.cboSetA & i.columns(1)

Case = 1
me.cboSetB & i.visible = True
me.cboSetC & i.visible = False
me.cboSetD & i.visible = False

Case = 2
me.cboSetB & i.visible = False
me.cboSetC & i.visible = True
me.cboSetD & i.visible = False

Case = 3
me.cboSetB & i.visible = False
me.cboSetC & i.visible = False
me.cboSetD & i.visible = True

End Select

Next i

I have tried this and it doesn't work...


What I really need to happen is for the code to fire each time a combobox in SetA is changed, that the properties for the corresponding comboxes in Set B, C and D to change.

Any ideas? Is this even possible?

 

[Cosmos75]

Me.Controls.Item("cbopw" & i) seems to work but I can't tell it which column to use.

 

[Cosmos75]

Someone suggested I look into the use of collections. Here's what I come up with. I put this code in the form module.

Public Sub HandlecboBoxes(i As Integer)

Dim myFrm As Form
Set myFrm = Me

Dim cboBox As Control
Dim SCVisible, AlphaVisible, NumberVisible  As Boolean

For Each cboBox In myFrm
    If cboBox.Name = "cboSetA" & i Then
    
    cboValue = cboBox.Column(1)
    
    Select Case cboValue
    
    Case Is = "1"
    SetBVisible = True
    SetCVisible = False
    SetDVisible = False
    
    Case Is = "2"
    SetBVisible = False
    SetCVisible = True
    SetDVisible = False

    Case Is = "3"
    SetBVisible = False
    SetCVisible = False
    SetDVisible = True

    End Select
    
        myFrm.Controls.Item("cboSetB" & i).Visible = SetBVisible
        myFrm.Controls.Item("cboSetC" & i).Visible = SetCVisible
        myFrm.Controls.Item("cboSetD" & i).Visible = SetDVisible
    
    End If
Next

End Sub

And for each cboSetA1 I have

HandleSpecialRulescboBoxes (1)

and so on.

I haven't figure out how to get it to do without the loop. I can just replace the whole loop that uses

For each cboBox
If cboBox.Name = "cboSetA" & i Then
...

with

Me.Controls.Item("cbopw" & i)

BUT, I won't be able to use the value column(1) in the combobox. The combobox has three columns (CodeID - Autonumber, ShortCode - Text, CodeDescription - Text). And I need to be able to use the value of ShortCode rather than an autonumber.

 

[WayneRyan]

Hi Cosmos,

Maybe this:

SomeString = Me.Controls("cboYourCombo" & i).Column(2)

Wayne