[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

Change ControlSource and Save Change

I am changing the ControlSource of a TextBox at runtime.

    Me.Controls("userRecurAD" & ADButChng & "txt").ControlSource = "NewSourceName"

Works great but after the form is closed, and reopened the txtbox.ControlSource = "OldSourceName"

Why are the changes to the control source not saved? How can I save the ControlSource change?
  • 5
2 Solutions
runtime changes dont get saved, they are only active during the time the form is open

what you could do is this

1. open form in design mode, make changes then save
2. store controlsource in a temp table or registry - then on form_load, read this temp table or registry setting and apply new control source

If u keep changing the controlsource, I would opt for no 2. Using it this way, u can make it flexible for different users to have different controlsource's.

ID (PrimaryKey)
UserID  (if u dont keep one, u can use the api call to get the user's window logon)
FormName (only needed if u want to use this feature on multiple forms)
CtrlName (only needed if u want to use this feature on multiple controls on a form or u have multiple forms)
Value  (holds the last used controlsource)

On Form_Open, u can then use DLOOLUP to obtain the controlsource. If none found, then use default
On Form_Close, u can write to this table and save the controlsource
You can change the controlsource on the fly for display purposes, but in order to save them the changes must be done in design view.

Dim frm As Access.Form
DoCmd.OpenForm "frm_Test", acDesign

Set frm = Forms("frm_test")

frm.Text0.ControlSource = "='Chuck'"
DoCmd.Close acForm, "frmTest", acSaveYes

DoCmd.OpenForm "frm_Test"
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

If u dont want to use a table, u can use the simple registry settings api calls


Note, this is then dependant on the user's profile on that PC. It is not global like the temp table

Back to form design

once u close this, the caller then has to update the form

Public Function SaveLastCtrlSource()
    DoCmd.OpenForm "Form1", acDesign, , , acFormEdit, acHidden
    Forms!Form1!cboMonths.ControlSource = "NewControlSource"
    DoCmd.Close acForm, "Form1", acSaveYes
End Function

But then remember, if u do this, this makes changes global to all users unlike using a temp table

So look at the 3 options Ive given, if u have other ideas, let us know

buzzcarterAuthor Commented:
This program is networked and may have 3 or more users at one time. So GetSettings SaveSetting will not work. And as each station has its on MDE copy, saving the form on one local computer would not work anyway, so my question is falled.

I am think of checking a related field that would indicate which ContorlSource to use.


Private Sub Form_Current()

For ADn = 1 To 4
    If Me.Controls("AD" & ADn & "type").Value = 1 Then
        'Me.Controls("userRecurAD" & ADButChng & "txt").SetFocus
        Me.Controls("userRecurAD" & ADn & "txt").ControlSource = "userRecurAD" & ADn
        CS = "userRecurAD" & ADButChng & "Hrs"
        Me.Controls("userRecurAD" & ADn & "txt").ControlSource = "userRecurAD" & ADn & "Hrs"
    End If
End Sub

ok, 3 or more users at a time

first of all its an MDE, so I do not think option 1 will work
secondly when the controlsource is modified, do u want this reflected to all users or just that one user?

Now reading your post, it seems you only have one of two rowsources to pick from

Do you share a backend database? or do each user have their own?

whatever the case may be, what u could do is create a config table
and a form to reflect this, give each recordsource a name, users can then play with this form, pick whichever one they want then load that up on form load - much like using the temp table


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now