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.

 

[Pat Hartman]

I rarely use that reference method and so your syntax may be incorrect. Also, you can ONLY do this when the form is open.
Forms!AllocateData!NewVar1.ControlSource = NewVar1 will work as long as the form is open.

 

[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.

 

[Pat Hartman]

You need to address it by its Name property. That may not be its name. Name is the first property on the Other tab.

 

[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....

 

[Pat Hartman]

That's not how you would do it. Try concatenating the name. You may have to use the eval() function.
newVar1 = "Statement2"
Forms!AllocateData! & NewVar1 &.ControlSource = newVar1
or
Eval("Forms!AllocateData!" & NewVar1 & ".ControlSource") = newVar1
or maybe
dim frm as Form
dim ctl as Control
and try to set their values that way.
In any event, if you want to do this permanently, you must open the form in design view and then save it. Hopefully, you're doing this for yourself and not intending it to be part of an app you distribute to users because if you need to use the Access Runtime, you can't open objects in design view with it.
Set frm = Forms!AllocateData

 

[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.

 

[Pat Hartman]

I do that also. There are occassional times in code where you explicitly reference the control and others where you want to reference the field. If they have different names, there is no ambiguity. Starting with the control Name being = to the field name is fine. Many times I've entertained the idea of writing code to go through all the controls of a form and prefixing the Name property depending on what the control type is. Perhaps byTimber can add that feature to his code and post it for us