Create single Private Sub Procedure to cover large number of dynamic combo boxes (with variable in name)

[dellehurley]

Sorry my header is probably confusing, I was not sure how to summarise my query.

I have a form in access with a 49 combo boxes which I have named cmbFullName1, cmbFullName2, cmbFullName3, cmbFullName4 etc
I know I can refer to them like this in the code...

        For i = 1 To PplCnt
            .Controls("cmbFullName" & i).Visible = True
            .Controls("cmbRIN" & i).Visible = True
            .Controls("txtRec" & i).Visible = True
            .Controls("lblFullName" & i).Visible = True
            .Controls("lblRIN" & i).Visible = True

But can I create a private sub procedure for the form to cover all of the combo boxes or do I have to create a change event for each one?
instead of this...

Private Sub cmbFullName1_Change()
Private Sub cmbFullName2_Change()
Private Sub cmbFullName3_Change()
Private Sub cmbFullName4_Change()

I want to do something like this

Private Sub cmbFullName & i_Change()

is this possible?
Dannielle

 

[Micron]

Probably more than one way to do something.
You can create a function that does what you want, then in form design view, select all of the controls and for the event you need, enter the name of the function. However, if there is a need to know which control fired the event then that also has to be taken care of. One method is to code the function to accept the name of the control, but that would mean after selecting all and entering (e.g. in the click event on the property sheet) your function name e.g. myFunction() you'd then have to individually enter the control name, as in myFunction(cmbFullNameX) where X is 1, 2, etc. You could also consider having the function refer to Screen.ActiveControl instead, but not if the function is going to do something to a control that doesn't have the focus.

Another possibility would be to read the control Tag property in that function, but that would also require you to put something there for each control.

 

[Micron]

Forgot to mention that the function has to be in a standard module.

 

[dellehurley]

Forgot to mention that the function has to be in a standard module.

Thank you for the help, just what I was looking for.

 

[Micron]

You're welcome. Glad I could help.