How to populate a ComboBox with query results

user_11316927 0
Viewed 2020

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.

ms-access vba
2 Answers

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
user_72968930

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
user_113169270
Related