Use recordset to populate list control

[dhartford]

I try to populate a list control with data in a recordset but it doesn't work. My be the syntex is wrong as I'm new to VBA. Here is my code

 sub form_load
    Dim objConn As ADODB.Connection
    Dim objRs As ADODB.Recordset
    Dim strConString As String
    Dim strSQL As String
 
    Set objConn = New ADODB.Connection
    Set objRs = New ADODB.Recordset
 
    strConString = "my string is here"    'connect to a spreadsheet
 
    objConn.Open strConString
 
    strSQL = "Select distinct Company FROM [Sheet1$]"
 
    objRs.Open strSQL, objConn, adOpenStatic, adLockOptimistic
 
    List11.RowSourceType = "table/query"
    List11.RowSource = objRs  'not sure if this is correct
 
    objRs.Close
    objConn.Close
 
End Sub

When I debug print the objRs recordset, a list of company name is printed. But how do I populate the list to the control.

Thank you very much in advance

[Norie]

Why don't you just use the SQL string for the rowsource?

[dhartford]

Why don't you just use the SQL string for the rowsource?

Because the data in the recordset is from a excel spreadsheet.

Thanks

[CreganTur]

I made a few changes to your code. I tested and it worked for me.

For one thing you didn't provide your connection string, so I wasn't sure if you had the right one for the job or not, so I provided a basic connection string for connecting to an Excel spreadsheet.

One of the issues you had is that your recordset is not the correct data type to work with the RowSource property of your listbox. There's an ADO method called GetString that pulls the selected records as a string value. I saved this value to a variable, and then populate your listbox with this string variable. I had to change your RowSource type to 'Value List' to get it to work correctly (since we're not working with an actual query or table).

[VBA]Dim objConn As ADODB.Connection
Dim objRs As ADODB.Recordset
Dim strConString As String
Dim strSQL As String
Dim strFilepath As String
Dim Records As String

Set objConn = New ADODB.Connection
Set objRs = New ADODB.Recordset
strFilepath = "C:\Employees.xls"
'***Replace strFilepath variable with filepath to your spreadsheet***
strConString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFilepath & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

objConn.Open strConString

strSQL = "Select distinct Company FROM [Sheet1$]"

objRs.Open strSQL, objConn, adOpenStatic, adLockOptimistic

Records = objRs.GetString

Debug.Print Records

List11.RowSourceType = "Value List"
List11.RowSource = Records

'you need to set value of variable to Nothing to release memory after you close
objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing
[/VBA]

[dhartford]

I made a few changes to your code. I tested and it worked for me.

For one thing you didn't provide your connection string, so I wasn't sure if you had the right one for the job or not, so I provided a basic connection string for connecting to an Excel spreadsheet.

One of the issues you had is that your recordset is not the correct data type to work with the RowSource property of your listbox. There's an ADO method called GetString that pulls the selected records as a string value. I saved this value to a variable, and then populate your listbox with this string variable. I had to change your RowSource type to 'Value List' to get it to work correctly (since we're not working with an actual query or table).

[vba]Dim objConn As ADODB.Connection
Dim objRs As ADODB.Recordset
Dim strConString As String
Dim strSQL As String
Dim strFilepath As String
Dim Records As String

Set objConn = New ADODB.Connection
Set objRs = New ADODB.Recordset
strFilepath = "C:\Employees.xls"
'***Replace strFilepath variable with filepath to your spreadsheet***
strConString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFilepath & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

objConn.Open strConString

strSQL = "Select distinct Company FROM [Sheet1$]"

objRs.Open strSQL, objConn, adOpenStatic, adLockOptimistic

Records = objRs.GetString

Debug.Print Records

List11.RowSourceType = "Value List"
List11.RowSource = Records

'you need to set value of variable to Nothing to release memory after you close
objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing
[/vba]

Cregan,

I really appreciate you reply and will try this evening. Later on I'll pull more columns from the spreadsheet, I guess the Records type would be an array. Can GetString method get the more then one column?

Thanks again, I'll let you know how it works.

[CreganTur]

Can GetString method get the more then one column?

yes. It takes the entire recordset returned by your ADO connection and converts it all into a String. I'm not sure how it will interact with the listbox's columns though... it may parse the fields into different columns (in which case you will need to adjust the column number and widths for the list box's properties) or it may contain it all in a single columnn (for each record).

[dhartford]

yes. It takes the entire recordset returned by your ADO connection and converts it all into a String. I'm not sure how it will interact with the listbox's columns though... it may parse the fields into different columns (in which case you will need to adjust the column number and widths for the list box's properties) or it may contain it all in a single columnn (for each record).

I tried and recordset works fine, but the data displayed in box is a row: ABCACEChubbDDDGACGAIC, instead of a list like this:

ABC
ACE
Chubb
DDD
GAC
GAIC

May be I need to save the record to a table, then bound control to it.

Thanks.

[Carl A]

Here is example of transposing an array to a combobox from a recordset
http://www.ozgrid.com/forum/showthread.php?t=23873