I had a problem populating a ComboBox from query results in access vba. My goal was to query for one column of a table and use every record from the result as an option for the combobox.
There are some things about the properties of ComboBoxes you need to be aware of and assign properly to make this work. Here's my code that seemed to hold the correct information but did not display anything in the dropdown list:
Dim RS As DAO.Recordset
Dim SQL As String
'Clean-up. not sure if it's needed but I'm "clearing" the old data (if there's any) before putting the new in
combox.RowSourceType = "Table/Query"
combox.RowSource = ""
SQL = "SELECT [some_value] FROM [a_table] WHERE [another_value] = '" & argv(0) & "'"
combox.RowSource = SQL
This was part of another question I asked but for clearer structure I'm moving this question (and it's answer) to this thread.
Your first attempt is fine, you just need to requery the combo box to actually load in the results of the query.
Dim SQL As String
combox.RowSourceType = "Table/Query"
combox.RowSource = ""
SQL = "SELECT [some_value] FROM [a_table] WHERE [another_value] = '" & argv(0) & "'"
combox.RowSource = SQL
combox.requery 'Clears old data, loads new data
After some refactoring and fixing I came to this result which makes the ComboBox behave as I intend:
combox.RowSourceType = "Value List"
combox.RowSource = ""
SQL = "SELECT [some_value] FROM [a_table] WHERE [another_value] = '" & argv(0) & "'"
With combox
.RowSource = kombSQL
.ColumnCount = 2
.ColumnWidth = 1
.ColumnWidths = "1.5in."
End With
Set RS = CurrentDb.OpenRecordset(SQL)
RS.MoveLast
RS.MoveFirst
combox.RemoveItem 0
Do Until RS.EOF
Me.combox.AddItem RS.Fields("some_value")
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Me.combox.BoundColumn = 0
Me.combox.ListIndex = 0
