vba 用 SQL 查询的结果填充列表框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22774738/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fill Listbox with Results of SQL Query
提问by 1337Atreyu
Alright, I might be way off if what I've tried, but here goes. I am trying to execute a SQL query to return some records and then populate a listbox. Essentially, I want every record to display in a separate line. I have this in a separate module referencing the main form. I have heard some things about filling a datatable with the SQL results and then linking the listbox to that as well, but I'm not 100% sure what to do there either. I've just been trying to manually update the listbox with each result, but it doesn't seem to like it. I am using a recordset instead of a datatable, but like I said, it's because I'm unsure of how to use a datatable (haven't done it before but I am willing to learn)
好吧，如果我尝试过的话，我可能会离开，但这里是。我正在尝试执行 SQL 查询以返回一些记录，然后填充一个列表框。本质上，我希望每条记录都显示在单独的行中。我在一个单独的模块中引用了主表单。我听说过一些关于用 SQL 结果填充数据表然后将列表框链接到它的事情，但我也不是 100% 确定在那里做什么。我一直在尝试使用每个结果手动更新列表框，但它似乎并不喜欢它。我使用的是记录集而不是数据表，但就像我说的，这是因为我不确定如何使用数据表（之前没有使用过，但我愿意学习）
Public Sub addCases()
'Uses windows login credentials to determine and return CSP's Manager's Name
'C
Dim i As Integer
Dim intX As Integer
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim strSQL As String, strManager As String
Set c = New ADODB.Connection
c.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Commit Tracker.accdb; Persist Security Info=False;"
strSQL = "SELECT "
strSQL = strSQL & "* "
strSQL = strSQL & "FROM "
strSQL = strSQL & "CommitTrk "
strSQL = strSQL & "WHERE "
strSQL = strSQL & "EMP_ID = '" & Username() & "'"
Set r = c.Execute(strSQL)
If r.EOF = False Then
intX = 0
vararray = r.GetRows()
For i = LBound(vararray) To UBound(vararray)
With frmCommitViewer.lstCases
.AddItem
.List(intX, 0) = r.Index(i)
End With
intX = intX + 1
Next i
End If
r.Close
c.Close
Set r = Nothing
Set c = Nothing
End Sub
回答by 1337Atreyu
I figured it out. Instead of the stupid array conversion, I'm still using the recordset directly.
我想到了。而不是愚蠢的数组转换，我仍然直接使用记录集。
If r.EOF = False Then
With frmCommitViewer.lstCases
.Clear
Do
.AddItem r![CASE_ID_NBR]
r.MoveNext
Loop Until r.EOF
End With
End If
Super simple. Thanks to http://www.fontstuff.com/vba/vbatut10pfv.htm.
