Textbox Control Source

[asmores]

How can I change a textbox control source with vba based on a concate formula?
I have a database that loops through user defined criteria and exports to excel. The user can fill in up to 100 different textboxes, but rather than assign a different query to each text box, I'd rather use 1 hidden text box that pivots through each textbox. Can anyone see the error in my code below. I feel its something simple but I cant place my finger on it.

Thanks

Private Sub Command2_Click()
ct = 1
Do Until ct = 101
Me.tbloop.ControlSource = """=[Forms]![fTop]![tb" & Format(ct, "000") & "]"""
Me.tbLoopName.ControlSource = """=[Forms]![fTop]![tb" & Format(ct, "000") & "Name]"""


MsgBox ct
MsgBox Me.tbloop.ControlSource
Call ExporttoExcel
ct = ct + 1
Loop
End Sub

[June7]

Do you want the value of each textbox or the textbox name?

To set ControlSource to an expression that references textbox name:

Me.tbloop.ControlSource = "=[tb" & Format(ct, "000") & "]"
Me.tbLoopName.ControlSource = "=[Forms]![fTop]![tb" & Format(ct, "000") & "Name]"

The [Forms]![fTop] prefix is not needed if all controls are on the same form.

[asmores]

I would need the value of the textbox
i tried this below

Me.tbloop.Value = "=[tb" & Format(ct, "000") & "]"
Me.tbLoopName.Value = "=[Forms]![fTop]![tb" & Format(ct, "000") & "Name]"

its only showing the formula and not the user prompt.

thank you,

[asmores]

This works below but id rather loop if possible, i dont understand why the concatenate formula is not understood
Me.tbloop = Me.tb001
Me.tbLoopName = Me.tb001Name
Call ExporttoExcel
Me.tbloop = Me.tb002
Me.tbLoopName = Me.tb002Name
Call ExporttoExcel
Me.tbloop = Me.tb003
Me.tbLoopName = Me.tb003Name
Call ExporttoExcel
Me.tbloop = Me.tb004
Me.tbLoopName = Me.tb004Name
Call ExporttoExcel
Me.tbloop = Me.tb005
Me.tbLoopName = Me.tb005Name
Call ExporttoExcel
...

[June7]

What user prompt?

Why are you now setting the Value property and before you were setting the ControlSource property?


If you want the value of textbox, try:

Me.tbloop = Me.Controls("tb" & Format(ct, "000"))

[asmores]

Works thank you, still new to this so 70% of what i do is just guess and check, i appreciate it