Change ControlSource and Save Change

Posted on 2006-06-05
Last Modified: 2012-08-14
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?
Question by:buzzcarter
    LVL 65

    Assisted Solution

    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

    LVL 65

    Expert Comment

    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
    LVL 4

    Accepted Solution

    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"
    LVL 65

    Expert Comment

    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

    LVL 65

    Expert Comment

    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


    Author Comment

    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

    LVL 65

    Expert Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now