Inserting variable value into Form reference

[EonsTimE]

Hi,
I'm trying to reference to a control on a form or subform, that has a part of it's name variable, ie it is named txtBox1, txtBox2, or on a subform control subForm1, subForm2, subForm3...

I have trouble injecting the variable part of the address into the string.

For example:

Sub DoSomething()

Dim i As Integer
Dim counter1 As String

    For i = 1 To 5
    counter1 = "txtBox_" & CStr(i)
        Debug.Print Forms!Form1.[& counter1 &].Value
    Next

End Sub

If I use Debug.Print Forms!Form1.txtBox_1.Value, the code resolves properly, but if I use Debug.Print Forms!Form1.[& counter1 &].Value I get error message 2465 "Microsoft Access can't find the field '|1' referred to in your expression."

I've tried placing "" everywhere and even building a string (it looks proper, as in Debug.Print Forms!Form1.txtBox_1.Value) and passing it to DoCmd., but it never resolves.

I've also tried passing entire Access.Controls object references to the subroutine, but can't manage to extract form/control reference back to the source unless the address is hard-coded.

Anyone able to advise on how to build form/control references programatically in VBA?

 

[Galaxiom]

Assuming you want to use the value of txtbox_1 on the current form as the name of the control you are referring to:
Forms!Form1.Controls(Me.txtbox_1)

The part in the parentheses is a string that is the name of the control.
The string is the named index of the collection you are seeking the member for.

Similarly with forms:
Forms("formname")

 

[EonsTimE]

Assuming you want to use the value of txtbox_1 on the current form as the name of the control you are referring to:
Forms!Form1.Controls(Me.txtbox_1)

The part in the parentheses is a string that is the name of the control.
The string is the named index of the collection you are seeking the member for.

Similarly with forms:
Forms("formname")

Ahh, thank you, placing the variable in brackets is one thing I've forgotten to try.
The code resolves if cycling through textbox names as follows:

Sub DoSomething()  
Dim i As Integer 
Dim counter1 As String      
For i = 1 To 5     
counter1 = "txtBox_" & CStr(i)         
Debug.Print Forms.Form1.Controls(counter1).Value     
Next  
End Sub

 

[Galaxiom]

BTW Value is the default property of controls in Access so you can omit it and just refer to the control object. Saves a bit of typing.

Also Controls is the default collection of the Form so it can be dropped though I prefer to include it.

Forms!formname("controlname")