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?

Solution 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

Solution 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"

Comments

[toolshed]

That's exactly what I'm trying to do. I've attempted to use that solution before, and I have been unable to get it working.

[HansUp]

Can you help us understand anything about why it's not working for you? Does your code compile without error? Do you have Option Explicit in your form's declarations section? Do you get a run time error message?

[toolshed]

I do not understand myself why the code is not working. It should work. This is the only piece of code in the database except for the header "Option Compare Database." No runtime error message.

[John M. Wright]

Instead of posting a link to an image of code, it would be better to post the code directly in your answer. This allows others to comment and improve upon your code over time and prevents the issue where the image becomes unavailable in the future.