Populating multicolumn listbox with SQL recordset in Excel VBA

Question

I have a userform that contains a listbox with 5 columns. When you click a search button I want the listbox to be populated with the results of that search from a SQL table.

I keep getting "Type-declaration character does not match declared data type" when I use rs! for .RowSource.

Sub searchall()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim list As Object
Set list = SearchForm.Results
Server_Name = "SDL02-VM25"
Database_Name = "PIA"
SQLStr = "select [Agentname],[position],[employeegroup],[supervisor],[manager] from dbo.[HistoricalMasterStaffing] Where [FirstName] ='" & SearchForm.firstname.value & "' or [LastName] ='" & SearchForm.lastname.value & "' or [Date] = '" & SearchForm.DateSearch.value & "' or [year] = '" & SearchForm.Year.value & "' or [employeegroup] = '" & SearchForm.EmployGroup.value & "' or [position] = '" & SearchForm.Position.value & "' or [ftpt] = '" & SearchForm.PTFT.value & "' or [Contractagency] = '" & SearchForm.Agency.value & "' or [termcode] = '" & SearchForm.TermCode.value & "' or [location] = '" & SearchForm.Location.value & "'"
Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & vbNullString
rs.Open SQLStr, Cn, adOpenStatic
With list
.Top = 252
.Left = 36
.Width = 573
.Height = 188.3
.ColumnHeads = True
.ColumnCount = 5
.ColumnWidths = "100;100;100;100;100;"
.MultiSelect = fmMultiSelectExtended
.RowSource = rs!
End With
rs.Close
Cn.Close
Set rs = Nothing
Set Cn = Nothing

End Sub

The excel listbox is expecting a range of cells, not a list of records. I have an example of doing the same thing from an Access database if you want to see it. — Wookies-Will-Code
– Wookies-Will-Code, Commented Jun 25, 2018 at 18:30
Always hard to pull pieces out of code, @sktneer is correct in this approach, I also attempt to show two methods 1) .addItem and 2) rowSource assignment to a range. I try and provide context or else code that is written to be readable makes no sense without sharing it all. — Wookies-Will-Code
– Wookies-Will-Code, Commented Jun 25, 2018 at 20:09

Subodh Tiwari sktneer · Accepted Answer · 2018-06-25 19:36:37Z

1

You can get the recordset in an Array and then populate the ListBox like this. And since you are populating the ListBox dynamically, the Headers will not work here but you can add the Labels with headers just above the ListBox if that works for you.

Dim arr
arr = rs.GetRows
.List = arr

answered Jun 25, 2018 at 19:36

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jb83 Over a year ago

Where in my code should I be putting this? I've put it in where I think it should go but I'm getting Object required errors.

Subodh Tiwari sktneer Over a year ago

Replace the line .RowSource = rs! with the proposed three lines.

Jb83 Over a year ago

This works great. I just need to figure out how to get the results to display across five columns instead of one

Stavros Jon · Accepted Answer · 2019-05-30 14:03:12Z

0

This is how I load multiple column listboxes, from a worksheet. I am sure that it can be done with arrays but you need 5 of them, or a multi-D array.

With Me.ListBox2
    .Clear 'clear to set up the list
    .ColumnCount = 4
    .ColumnWidths = "60;70;65;150"
    For i = 0 To LastRow - 2 'data starts in Row2
        If LastRow >= 2 Then
            .AddItem
            .List(i, 0) = DATAsheet.Cells(i + 2, 1) 'Column of the ID, data starts in row 2
            .List(i, 1) = DATAsheet.Cells(i + 2, 4) 'Column of the Date, data starts in row 2
            .List(i, 2) = DATAsheet.Cells(i + 2, 2) ' Column of motor Size, data starts in row 2
            .List(i, 3) = DATAsheet.Cells(i + 2, 3) ' Column of motor SN, data starts in row 2
        Else ' Do nothing
        End If
    Next i
End With

edited May 30, 2019 at 14:03

Stavros Jon

1,7072 gold badges10 silver badges18 bronze badges

answered Jun 27, 2018 at 18:42

Wookies-Will-Code

7359 silver badges24 bronze badges

Collectives™ on Stack Overflow

Populating multicolumn listbox with SQL recordset in Excel VBA

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related