Using ADO Query Results to Fill ComboBox

[cstimart]

When using ADO to extract data to excel, is it possible to use 'CopyFromRecordset' to fill a Combo/Listbox?

Or is it easiest to extract the data to a worksheet and fill the box from there?

 

[Weaver]

I'd say the latter, although if it was me, I'd loop through the recordset and apply it to the listbox one item at a time using .additem.

My reason for this is I like userforms to be independant of the workbook, but if you wanted to go the route of pasting to a worksheet, I'd suggest creating a new sheet which you can delete once the form is finished with it.

 

[cstimart]

I'd say the latter, although if it was me, I'd loop through the recordset and apply it to the listbox one item at a time using .additem.

My reason for this is I like userforms to be independant of the workbook, but if you wanted to go the route of pasting to a worksheet, I'd suggest creating a new sheet which you can delete once the form is finished with it.

Therein lies my problem. I don't know how to loop through the recordset, independent of the worksheet fill.

 

[Weaver]

if your recordset is called rs

with rs
  do while not .eof
    mylistbox.additem !myfield
    .movenext
  loop
end with

(not tested)

 

[Norie]

How many fields are in the recordset?

If there's only one you might be able to use GetRows.

arrValues = rst.GetRows() ' change rst to your variable
 
' not sure if you need to transpose or not
 
Listbox1.List = arrValues
 
ListBox1.List = Application.Transpose(arrValues)

 

[RoryA]

You can use GetRows regardless of how many fields there are.

 

[Norie]

I've never used it with more than one column

ActuallY just found some code that includes this function to transpose the results from GetRows.

No idea where it came from but it works.

Function TransposeGetRows(varData) As Variant
   Dim lngRow As Long, lngCol As Long
   Dim varOut()
   ReDim varOut(1 To UBound(varData, 2) + 1, 1 To UBound(varData, 1) + 1)
   For lngRow = LBound(varData, 2) To UBound(varData, 2)
      For lngCol = LBound(varData, 1) To UBound(varData, 1)
         varOut(lngRow + 1, lngCol + 1) = varData(lngCol, lngRow)
      Next lngCol
   Next lngRow
  TransposeGetRows = varOut
End Function

 

[RoryA]

Of course it works - it's mine.

 

[Norie]

Well I knew it wasn't mine, wasn't disorganised enough.

 

[cstimart]

How many fields are in the recordset?

If there's only one you might be able to use GetRows.

arrValues = rst.GetRows() ' change rst to your variable
 
' not sure if you need to transpose or not
 
Listbox1.List = arrValues
 
ListBox1.List = Application.Transpose(arrValues)

is arrValues a Variant?