CODEWITHSUNDEEP
vbams-access
veryBadProgrammer
veryBadProgrammer

Reputation: 115

How to populate a ComboBox with query results

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.

Upvotes: 2

Views: 4649

Answers (2)

veryBadProgrammer
veryBadProgrammer

Reputation: 115

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

Upvotes: 1

Erik A
Erik A

Reputation: 32632

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

Upvotes: 2

Related Questions