using variables in Forms!frmName.controlname etc

[AOSB]

Solved: Using variables in Forms!frmName.controlname etc

I use this vba in a form to set a textbox background on another open form like this;

Forms!frmsummary.[time line wills].Controls![txtdGuard].BackColor = vbYellow

This works fine of course.

Now the challenge is to use variables for the subform and control names.

dim subformname as string
dim controlname as string
subformname = "time line wills"
controlname =   "txtdGuard"
Forms!frmsummary.(subformname).Controls!(controlname).BackColor = vbYellow

This last line throws a 2465 error - app or object defind error

Any ideas please?

 

[Cronk]

Firstly you don't need to refer to the controls collection ie you can omit it.

Forms!frmsummary.[time line wills]![txtdGuard].BackColor = vbYellow

On the subform, you need to refer to its Form collection.

Forms!frmsummary.(subformname).Form!(controlname).BackColor = vbYellow

 

[AOSB]

Thanks for the quick reply. Your syntax for the subform is correct but only if I replace the two string variables with the actual subform and textbox names

Dim subformname As String
Dim controlname As String
subformname = "time line wills"
controlname = "txtdGuard"

Forms!frmsummary.[time line wills].Form![txtdGuard].BackColor = vbYellow ' this does not cause a syntax error and works BUT 
      'Forms.frmsummary.(subformname).Form!(controlname).BackColor = vbYellow ' this causes a syntax error in the IDE

So I still have the question - how do I use variables in the above commands. I need to do this so I can loop through all the controls on subforms in a tabbed control looking for empty text boxes, however for now I have simplified the above just to see if it is working with variables.

 

[Cronk]

My mistake. Leave out the exlclamation mark and dot when referring to control names held in variables.
Forms.frmsummary(subformname).Form(controlname).BackColor = vbYellow

 

[AOSB]

Thank you so much, it now works perfectly. I have marked it SOLVED