Using a variable to refer to a control on a form.

[lewism]

I want to refer to a combo box using a variable as its name. My form
has several combo boxes (30+) that I want to refer to in a procedure.
I want to build the desired name, assign it to a string variable and
then use the variable to refer to the appropriate combo box.
Example:
My procedure constructs the string variable "cboTm1_M_1".
Dim sCtl as string
sCtl = "cboTm1_M_1"
......
......
Forms("frmWeeklyWorkSch").controls(sCtl) = .....

When I use the reference above I get this error
Run-Time error '2465'
Application can't find the field 'cboTm1_M_1' referred to in your
expression.

If I hard code
Forms("frmWeeklyWorkSch").controls("cboTm1_M_1") = .....
It works as advertised.

I have tried
Forms("frmWeeklyWorkSch")(sCtl) =

Dim Ctl as access.combobox

Set Ctl = Forms("frmWeeklyWorkSch")(sCtl)

All with the same result. What do I need to do to make this work?

 

[Allen Browne]

Your idea of:
Forms("frmWeeklyWorkSch").controls(sCtl)
should work unless frmWeeklyWorkSch is a subform.

To avoid the problem, could you pass the control to your function instead of
its name? You can then do whatever you like to its value or properties,
e.g.:

Function DoSomething(cbo As ComboBox)
cbo = Null
cbo.Visible = True
cbo.BackColor = vbRed
End Function