Question Change controlsource in vba

[byTimber]

Can I change the control source property of a control using a variable eg

Forms("AllocateData")(newVar1).ControlSource = newVar1

where AllocateData is the table name and newVar1 is the name both of the control and the field in the table ?

I get 'Object variable with block variable not set' I have declared all the variables.

I'm not sure that I am addressing the field name for the new control source correctly.

 

[byTimber]

Thanks Pat but it reports that it can't find the field newVar1 (the one to be changed) . Is this the correct way to address a field (as a variable). I need it to change the datasource permanently?

I do this as a test:
newVar1 = "Statement2" ' this is a field in the table as well as the name of textbox.

MsgBox newVar1 ' shows "Statement2"

Forms!AllocateData!newVar1.ControlSource = newVar1

Cannot find field newVar1 on error message.

 

[byTimber]

Crossed posts Pat! - I have to do this in VB - as you see on previous post, I have passed the name to variable newVar....

 

[byTimber]

Thanks Pat - I'll give your suggestions a run in the morning. Much appreciated - I'll tell you if it is OK.

This is, incidentally only to help with creating my forms. 20 groups of controls all duplicated but hopefully to automate change of controlsources for each group. and possily more forms of a similar nature. No runtime or user involvement in these changes.
I hope to be able to put form into design mode with VB and let the code follow (if I can ever get it right!!)
Roger..

 

[byTimber]

The following code seems to have done the job:


Forms("allocatedata").Controls(newVar).ControlSource = newVar1

Thanks Pat...

 

[RainLover]

The name of the Control should be different to the name of the Field.

Usually txtFieldName and FieldName. Similar but different.