update combobox's control source dynamically on bound form

Posted on 2009-02-17
Last Modified: 2012-05-06
Is there a way to dynamically update an unbound combobox's control source?

I have a bound form but unbound combobox....
Question by:scbdpm
    LVL 84
    You can set the .ControlSource as needed from code, but it won't "stick". If you want to permanently change it, you'd have to open the form in Design view, make the change, then save the form.
    LVL 61

    Expert Comment

    In VBA,

    Me.cboYourComboName.Controlsource = "[YourFieldName]"


    Me.cboYourComboName.Controlsource = "txtTextBox1 + txtTextBox2"


    Author Comment

    LSMConsulting: after posting I found '.ControlSource' but am certainly finding that your words are correct; No sticky no update....
    Realize this can be done at design time but really need to do it at run-time.
    would you suggest 'INSERT"????
    LVL 61

    Expert Comment


    INSERT is a SQL Keyword that comits data to a table.

    The ControlSource property specifies what field your control is bound to, or alternatively an expression that determines the data that is seen in your control.

    Can you be more specific about what you are trying to do or provide an example?
    LVL 84
    You can do it like this:

    Dim frm As Form
    DoCmd.OpenForm "YourForm",acDesign

    Set frm = Forms("YourForm")
    frm.Controls("YourControl").ControlSource = "YourNewControlSource"
    DoCmd.Close acForm, "YourForm", acSaveYes

    But as mariam said: Why do you need to permanently change the value of your ControlSource at runtime?

    Accepted Solution

    Here's why I need to do this:
    I have a subform on my main form. It is a continuous form. On it, I've had a textbox for date and a combobx for 'Intervention'.
    I've had lots of problems so have changed the combobox into a textbox as well and made them bound.
    I need a way to have the user make their choice and so put an unbound textbox and combobox into the form footer. They can then type in the date and use the dropdown. My plan was to then have them click a button, have a new record created and have the two controls bound.
    As an aside, becausse I was pressed for time. I was successful in having this work using the attached code.

       Dim strGroupID As String
        Dim intTopicMeasureID As Integer
        strGroupID = [Forms]![frmGroupDataEntry]![GroupID]
        intTopicMeasureID = [Forms]![frmGroupDataEntry]![subGroupTopicMeasure].[Form]![cmbTopicMeasure]
        '& "(GroupID, InterventionDate, TopicMeasureID, InterventionID) VALUES " _
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO [tblGroupIntervention] " _
        & "(GroupID, InterventionDate, InterventionID, TopicMeasureID) VALUES " _
        & "('" & strGroupID & "', #" & txtNewInterventionDate & "#, " & cmbNewIntervention & ", " _
        & intTopicMeasureID & ")"
        DoCmd.SetWarnings True
        txtNewInterventionDate = ""
        cmbNewIntervention = ""

    Open in new window

    LVL 84
    What was the answer you found?

    Author Comment

    see my last posting of 02.19.2009  

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    734 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

    20 Experts available now in Live!

    Get 1:1 Help Now