Setting ComboBox RowSource property to query in “GotFocus()” method leaves blank values in ComboBox Access VBA

1
votes

I want to populate my ComboBox in an Access form using VBA based on data from another table. Previously to do this I did the following:

  • In the Property Sheet -> Data tab I filled out Row Source and Row Source Type fields with this information:

enter image description here

Now whenever I clicked on the dropdown for my combobox, it would populate the dropdown list with all of the names from t_people table.

This limited me however to when data changed in the t_people's name column. In order to get an updated list, I must close the form and re-open it so that the query runs again. I have limited the access to this Access file so that the user is only presented with x number of forms, and cannot close/open them or others.

My solution is to remove the query on the form load, and instead run the query every time the combobox gains focus, has a click event or something of the same sorts. I did this with the following event in VBA:

'Run when the "name" combobox gains focus
Private Sub nameCb_GotFocus()
    [nameCb].RowSource = "SELECT name FROM t_people"
End Sub

I have set breakpoints and this code does run. However, the the combobox is not populated after it does. How can I get the combobox to populate with the values from a query for each time the combobox gains focus?

1
vbacombobox
You can put it back to where it was (through the form property) and add .Requery but on Enter() event. – Kostas K.
Can you make that an answer so I can accept it? It worked – Michael

1 Answers

2
votes

Set the RowSource in design and add a .Requery when entering the control.

Private Sub nameCb_Enter()
    nameCb.Requery
End Sub