In Access, SQL query that populates a Bound Combo Box does not update

Question

In my form, I have a combobox that lists all the users in the table using a select distinct query. People can also type in a new name in the combobox if that name is not in the list.

If you type in a new name, when you move to the next record, the query that populates the combobox dropdown list does not update unless save record is run. I have a button for save record and a button for next/previous record. If the next or previous button is clicked the combobox dropdown is not updated.

Typing in a new name in the combobox does not run the event Form_Dirty. However the new name is saved because I can move to the next and then back and the new name is there. But a requery does not update the dropdown for the combobox.

I have tried to requery the combobox in the after update event and the form current event, it saved sql statement is requeried but the combobox dropdown is not updated unless I click on the save record button.

Answer 1

I made it work by following an answer from another question. I cannot find the question right now to link it and give the person the credit they deserve.

The solution for me was during the after update event to empty the combobox recordsource then repopulate it with the query.

Me.combo_box.recordsource = ""
Me.combo_box.recordsource = "sqlQuery"

Answer 2

If the combo box is bound to an underlying column in that form? Then indeed the underlying record will and should become dirty, and will be automatic saved on navigation to next/previous records, or that of closing the form. This suggests and hints that the combo box is un-bound. Remember, the combo box has a data source to feed the options, and ALSO has a bound property.

If that bound property is set, then any change in the combo (including typing in values) will and should save to the form.

The issue then becomes when you go back to that page, the combo box does not and will not display correctly since the save value IS NOT in the list that drives the combo box. Thus, this suggests that when a user types in a value to the combo box, you should use the not in list event, and prompt/ask the user if they want to add this "new" item to the combo box list.

So, if you don't add the new item (that the user just typed in) to the list, then the combo box can't update correctly, and when you navigate back to that record, the combo box will not display anything, since the current value does not exist in the list.

What you can do is turn off limit to list. This will THEN allow you to type in values, ones not in the list, and thus when you navigate, then the new value will be saved, and when you navigate back to that page, the value that is NOT in the dropdown list should display.

Assuming your combo box is bound? Then turn off the limit to list, and all should work fine. That setting will allow the user to type in values that don't exist in the combo box list, and that value will save, and should display correctly when you navigate back to that reocrd.