在Excel VBA中用SQL记录集填充多列列表框

Question

我有一个用户窗体，其中包含5列的列表框。当您单击搜索按钮时，我希望使用SQL表中的搜索结果填充列表框。

使用rs时，我不断收到“类型声明字符与声明的数据类型不匹配”的提示！适用于.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

您可以在数组中获取记录集，然后像这样填充ListBox。并且由于您是动态填充ListBox的，因此Headers在这里不起作用，但是您可以在ListBox上方添加带有标题的Labels（如果适用）。

Dim arr
arr = rs.GetRows
.List = arr