Use recordset to populate list control
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
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
Populate list box with data in recordset
Quote:
Originally Posted by Norie
Why don't you just use the SQL string for the rowsource?
Because the data in the recordset is from a excel spreadsheet.
Thanks
opulate list box with data in recordset
Quote:
Originally Posted by 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]
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.
Populate list box with data in recordset
Quote:
Originally Posted by CreganTur
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.
