Populating a Combobox with a ADODB Recordset

Question

I want to query a Database from Excel Vba (using ADODB) and fill a Combobox with the retrieved List of Values. Unfortunately only the last value of the returned List is displayed in the Dropdown field. I assume that the recordset is a complete String of all returned rows, and so only the last one is visible.

I searched about the topic but only can find info regarding Access, which doesn't seems to help.

This should be the important part of the code, I can provide more if needed:

'-- Create database query
SQLStatement = "SELECT Project_Name FROM ressourceplanning.projects"

'-- Execute query
Recordset.Open SQLStatement, Connection

'-- Write report into combobox (dropdown)
RecordsetArray = Recordset.GetRows
UF_Delete_Project.Cb_DeleteProject.List = RecordsetArray
'UF_Delete_Project.Cb_DeleteProject.RowSourceType = RecordsetArray

The Last line leads to umatching types (error 13) when used. But using "RowSourceType" was the best answer i found so far.

The Recordset contains the correct values so the DB Connection and the query is working per se, its just about the populating of the recordset.

This code here did the trick for me:

RecordsetArray = Recordset.GetRows

For i = LBound(RecordsetArray, 2) To UBound(RecordsetArray, 2)
    UF_Delete_Project.Cb_DeleteProject.AddItem RecordsetArray(0, i)
Next i

Thanks to @braX and @FunThomas

Have you tried looping thru the recordset and using the .AddItem method of the combobox? — braX
Had it just stuck at the back of my had. But then i will try it offcourse. Thanks alot :) — G.M

FunThomas FunThomas · Accepted Answer · 2019-11-14T08:10:37

The command Recordset.GetRows returns the data as a 2-dimensional array, however, it returns them in the "wrong order" of the dimension: The first dimension is the FieldIndex, the second is the RowIndex

RecordsetArray(0, 0) gives you the first field of the first row, RecordsetArray(1, 0) gives the 2nd field of the first row while RecordsetArray(0, 1) gives the first field of the 2nd row.

The List-property of a comboBox expects the data as 2-dimensional array, but with the row as first index and the field as second (you can display more than one column in a comboBox). So what you have to do is to Transpose your array:

UF_Delete_Project.Cb_DeleteProject.List = Application.WorksheetFunction.Transpose(pRecordsetArray)

However, the Transpose-method has some limitations (number of fields, max. length of a string, cannot handle Null-values), so if you encounter a Runtime Error 13 (Type mismatch), it's probably easier to loop over either the recordset or the array as braX suggested in his comment.

Populating a Combobox with a ADODB Recordset

1 Answers