Using ADO Query Results to Fill ComboBox

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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. ;)
 
Upvote 0
if your recordset is called rs

Code:
with rs
  do while not .eof
    mylistbox.additem !myfield
    .movenext
  loop
end with
(not tested)
 
Upvote 0
How many fields are in the recordset?

If there's only one you might be able to use GetRows.
Code:
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)
 
Upvote 0
You can use GetRows regardless of how many fields there are. :)
 
Upvote 0
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.
Code:
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
 
Upvote 0
Of course it works - it's mine. ;)
 
Upvote 0
Well I knew it wasn't mine, wasn't disorganised enough.:)
 
Upvote 0
How many fields are in the recordset?

If there's only one you might be able to use GetRows.
Code:
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?
 
Upvote 0

Forum statistics

Threads
1,224,540
Messages
6,179,417
Members
452,912
Latest member
alicemil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top