We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

update combobox's control source dynamically on bound form

scbdpm
scbdpm asked
on
Medium Priority
462 Views
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....
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
In VBA,

Me.cboYourComboName.Controlsource = "[YourFieldName]"

or

Me.cboYourComboName.Controlsource = "txtTextBox1 + txtTextBox2"

etc

Author

Commented:
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"????
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
scbdpm,

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?
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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?
Commented:
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 & ")"
    
 
    Form_subGroupTopicMeasure_Intervention.Requery
 
    DoCmd.SetWarnings True
    
    txtNewInterventionDate = ""
    cmbNewIntervention = ""

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
What was the answer you found?

Author

Commented:
see my last posting of 02.19.2009  
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.