Refresh query-driven combo box values when field value changes?

Question

I have a combo box on an form where the values are populated based on the value in a separate field.

To do this, I have created a combo box and set the "Row Source" to run a SQL statement.

The problem I am having is that if the data in the field changes, the combo box values do not update.

How do I get access to re-run the query?

Answer 1

See whether this description is reasonably close to your situation.

My form has a text box, txtFoo, and a combo box, cboBar.

The row source property for cboBar is a query which references txtFoo. And I want the combo's contents updated in response to changes in txtFoo. The solution is to requery cboBar from txtFoo's after update event.

Private Sub txtFoo_AfterUpdate()
    Me.cboBar.Requery
End Sub

Answer 2

I have had issues in the past with Requery not working or even hanging. It's not pretty but you might want to try this:

Me.cboBar.RowSource = ""
Me.cboDemoUnit.RowSource = "your SQL statement"

Answer 3

Here is an example of what I have done recently to do the same exact thing, this should help! Its in an afterupdate sub.Example of dynamic combobox