Use String As Control

[DB505050]

I have about 500 controls I need to loop through, each named according to a specific number/letter combination for easy reference.

I want to decide WHICH control to update based on a string variable, but I have looked around and cannot seem to find any way to use/convert a string variable to an actual control name, so that I may use it.

Something like this:

Public Function MapTableFields(PASSrs As DAO.Recordset)
Dim fldNUM As Integer
Dim sfldNUM As String
Dim fldLET As String
Dim strtblField As String
Dim ctltblField As Control

Do
fldNUM = fldNUM + 1
sfldNUM = Str(fldNUM)
fldLET = "a"
strtblField = "cbOField" & sfldNUM & fldLET
ctltblField = strtblField (????)
ctltblField.Value = PASSrs(0).Value
Loop Until fldNUM = 10

THANKS for any insight!

 

[nanscombe]

On a form you can use the Controls collection.

You can refer to Me.Controls("yourControlName").

In the past I have used code like..

For lngIndex = 1 to 10
  Me.Controls("cboParameters" & Format(lngIndex,"00)).Enabled = TRUE
Next lngIndex

That would enable controls cboParameters01 to cboParameters10.

So using your code ...

Public Function MapTableFields(PASSrs As DAO.Recordset)
Dim fldNUM As Integer
Dim strtblField As String

' I tend to use the For .. Next construct rather than Do .. Loop

For fldNum =  1 to 10
 strtblField = "cboField" & fldNum & "a" ' Access will happily treat the number as a string
 Me.Controls(strtblField) = PASSrs(0).Value
Next fldNUM

 

[DB505050]

This working! Thanks so much!

 

[mdlueck]

The ability to use a string value to dynamically refer to a control would be slick indeed.

The EVAL BIF looks like as close as VBA comes to such. The EVAL execution environment, however, is protected from dynamically accessing classes - including Forms.

I have used this work-around in order to develop dynamic code:

Fun/side project: Build dynamic SELECT query using ADODB.Command / .Parameters objs
http://www.access-programmers.co.uk/forums/showthread.php?t=230838

Other places I have used a VBA SELECT CASE block to rattle through the multiple choices in order to operate on the correct thing the dynamic code needs to operate on that given time.

But yes, in your case do look into the suggestion to use the controls collection of the form.