Populating multicolumn listbox with SQL recordset

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

Answer 1

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

Answer 2

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