Modify ControlSource with code 2

[RickBerem]

Hello,

I would like to modify the ControlSource of a textbox with code. My problem is that I change the ControlSource of controls from another form opened in design view. I want these changes to be saved and permanent.

It works fine to change the ControlSource expression but for the expression to work Access needs it to start by a =
sign. If I add the = sign by code I get an error of bad expression. If I make the change by hand using the same Expression( that way adding the = sign by hand ) it works.

Please help!

Salvatore Grassagliata

 

[beetee]

That sounds kinda dangerous.

An alternative would be to create overlapping controls, with only one visible at a time.

 

[RickBerem]

Hi beetee,

Thanks for your help but it's for an update.
It will run only once and it's for multiple lookalike forms

that's why I need to modify the existing controls

any other toughts? Salvatore Grassagliata

 

[beetee]

You might want to try something like this:

Private Sub ChangeControlSource(FormName As String, ControlName As String, ControlSource As String)
Dim TheForm As Form
DoCmd.Close acForm, FormName
DoCmd.OpenForm FormName, acDesign
Set TheForm = Forms(FormName)

Dim ControlToChange As Control
Set ControlToChange = TheForm.Controls(ControlName)
ControlToChange.ControlSource = ControlSource
DoCmd.Save acForm, FormName
DoCmd.Close acForm, FormName
DoCmd.OpenForm FormName, acNormal
Set TheForm = Forms(FormName)
TheForm.Requery
End Sub

Of course, it needs error checking and testing

 

[RickBerem]

Thanks again beetee,

I already use a code similar to yours to do what I try to accomplish, However I get this error message when I run my code with my built expression with an = sign at the beginning :

The expression you entered contains invalid syntax

but if I copy the same expression in the same control by hand and add the = sign myself, It works just fine. an no error occurs.

Maybe I'm clearer now. Salvatore Grassagliata

 

[beetee]

You may be running into trouble because the control name and the field name are the same.

E.g. if you are changing the control source of control
intManHours
to be
= 2 * [intManHours]

you may need to change the control name to something else.

This code works:

Private Sub ChangeControlSource(FormName As String, ControlName As String, ControlSource As String)
Dim TheForm As Form
DoCmd.Close acForm, FormName
DoCmd.OpenForm FormName, acDesign
Set TheForm = Forms(FormName)

Dim ControlToChange As Control
Set ControlToChange = TheForm.Controls(ControlName)
ControlToChange.ControlSource = ControlSource
ControlToChange.Name = "Calc_" & ControlName
DoCmd.Save acForm, FormName
DoCmd.Close acForm, FormName
DoCmd.OpenForm FormName, acNormal
Set TheForm = Forms(FormName)
TheForm.Requery
End Sub