如何使用 VBA 用 Recordset 填充 ComboBox

How to populate a ComboBox with a Recordset using VBA

提问by Philippe Grondier

There is some literature available at expert's exchangeand at teck republicabout using the combobox.recordset property to populate a combobox in an Access form.

在专家交流中心和teck Republic 上有一些 关于使用 combobox.recordset 属性在 Access 表单中填充组合框的文献。

These controls are usually populated with a "SELECT *" string in the 'rowsource' properties of the control, referencing a table or query available on the client's side of the app. When I need to display server's side data in a combobox, I create a temporary local table and import requested records. This is time consuming, specially with large tables.

这些控件通常在控件的“rowsource”属性中填充“SELECT *”字符串，引用应用程序客户端上可用的表或查询。当我需要在组合框中显示服务器端数据时，我创建一个临时本地表并导入请求的记录。这很耗时，特别是对于大桌子。

Being able to use a recordset to populate a combobox control would allow the user to directly display data from the server's side.

能够使用记录集来填充组合框控件将允许用户直接从服务器端显示数据。

Inspired by the 2 previous examples, I wrote some code as follow:

受前面两个例子的启发，我写了一些代码如下：

Dim rsPersonne as ADODB.recordset
Set rsPersonne = New ADODB.Recordset

Set rsPersonne.ActiveConnection = connexionActive
rsPersonne.CursorType = adOpenDynamic
rsPersonne.LockType = adLockPessimistic
rsPersonne.CursorLocation = adUseClient

rsPersonne.Open "SELECT id_Personne, nomPersonne FROM Tbl_Personne"

fc().Controls("id_Personne").Recordset = rsPersonne

Where:

在哪里：

• connexionActive: is my permanent ADO connection to my database server
• fc(): is my current/active form
• controls("id_Personne"): is the combobox control to populate with company's staff list
• Access version in 2003

• connexionActive：是我到我的数据库服务器的永久 ADO 连接
• fc(): 是我当前/活动的形式
• 控件（“id_Personne”）：是用于填充公司员工列表的组合框控件
• 2003年接入版

Unfortunately, it doesn't work!

不幸的是，它不起作用！

In debug mode, I am able to check that the recordset is properly created, with requested columns and data, and properly associated to the combobox control. Unfortunately, when I display the form, I keep getting an empty combobox, with no records in it! Any help is highly appreciated.

在调试模式下，我能够检查记录集是否已正确创建、具有请求的列和数据，以及是否与组合框控件正确关联。不幸的是，当我显示表单时，我总是得到一个空的组合框，里面没有任何记录！任何帮助都受到高度赞赏。

EDIT:

编辑：

This recordset property is indeed available for the specific combobox object, not for the standard control object, and I was very surprised to discover it a few days ago. I have already tried to use combobox's callback function, or to populate a list with the "addItem" method of the combobox,. All of these are time consuming.

这个记录集属性确实对特定的组合框对象可用，而不是对标准控件对象可用，几天前我很惊讶地发现它。我已经尝试使用组合框的回调函数，或者使用组合框的“addItem”方法填充列表。所有这些都非常耗时。

采纳答案by Philippe Grondier

I found the trick ... the "rowSourceType" property of the combobox control has to be set to "Table/List". Display is now ok, but I have now another issue with memory. Since I use these ADO recordsets on my forms, memory usage of Access is increasing each time I browse a form. Memory is not freed either by stopping the browsing or closing the form, making MS Access unstable and regularly freezing. I will open a question if I cannot solve this issue

我找到了诀窍……组合框控件的“rowSourceType”属性必须设置为“表格/列表”。显示现在可以了，但我现在有另一个内存问题。由于我在表单上使用这些 ADO 记录集，因此每次浏览表单时 Access 的内存使用量都会增加。停止浏览或关闭表单都不会释放内存，这会使 MS Access 不稳定并经常冻结。如果我不能解决这个问题，我会提出一个问题

回答by Marcand

As was said, you have to get the RowSourceType to "Table/List" (or "Table/Requête" if in french) in order to show query results in the combobox.

如前所述，为了在组合框中显示查询结果，您必须将 RowSourceType 设置为“Table/List”（如果是法语，则为“Table/Requête”）。

Your memory problems arise from opening the recordset (rsPersonne) without closing it. You should close them when closing/unloading the form (but then again you would have scope problems since the recordset is declared in the function and not in the form).

您的内存问题是由于打开记录集 (rsPersonne) 而不关闭它而引起的。您应该在关闭/卸载表单时关闭它们（但由于记录集是在函数中声明的，而不是在表单中声明的​​，因此您会再次遇到范围问题）。

You could also try to create and save a query with Access's built-in query creator and plug that same query in the RowSource of your combobox. That way the query is validated and compiled within Access.

您还可以尝试使用 Access 的内置查询创建器创建和保存查询，并将相同的查询插入到组合框的 RowSource 中。这样查询在 Access 中得到验证和编译。

回答by Lance S

To set a control that accepts a rowsource to a recordset you do the following:

要将接受行源的控件设置为记录集，请执行以下操作：

Set recordset = currentDb.OpenRecordset("SELECT * FROM TABLE", dbOpenSnapshot)
Set control.recordset = recordset

Works with DAO Recordsets for sure, I haven't tried ADO recordsets because I don't have any real reason to use them.

可以肯定地使用 DAO 记录集，我还没有尝试过 ADO 记录集，因为我没有任何真正的理由使用它们。

When done this way, a simple requery will not work to refresh the data, you must do a repeat of the set statement.

以这种方式完成后，简单的重新查询将无法刷新数据，您必须重复设置语句。

回答by Philippe Grondier

good method with using the Recordset property, thanks for that hint!

使用 Recordset 属性的好方法，感谢您的提示！

Patrick, the method you shown on your page has a big disadvantage (I tried that too on my own): The value list can only be 32 KB, if you exceed this limit the function will throw an error. The callback method has the big disadvantage that it is very slow and it is called once for every entry which makes it unuseable for a longer list. Using the recordset method works very well. I needed this because my SQL string was longer than 32 KB (lot of index values for WHERE ID IN(x,x,x,x,x...)).

帕特里克，你在页面上显示的方法有一个很大的缺点（我自己也试过）：值列表只能是 32 KB，如果超过这个限制，函数会抛出错误。回调方法有一个很大的缺点，它非常慢，并且为每个条目调用一次，这使得它无法用于更长的列表。使用记录集方法效果很好。我需要这个是因为我的 SQL 字符串长于 32 KB（WHERE ID IN(x,x,x,x,x...) 的很多索引值）。

Here's a simple function which uses this idea to set a recordset to the combobox:

这是一个简单的函数，它使用这个想法将记录集设置到组合框：

' Fills a combobox with the result of a recordset.
'
' Works with any length of recordset results (up to 10000 in ADP)
' Useful if strSQL is longer than 32767 characters
'
' Author: Christian Coppes
' Date: 16.09.2009
'
Public Sub fnADOComboboxSetRS(cmb As ComboBox, strSQL As String)
    Dim rs As ADODB.Recordset
    Dim lngCount As Long

   On Error GoTo fnADOComboboxSetRS_Error

    Set rs = fnADOSelectCommon(strSQL, adLockReadOnly, adOpenForwardOnly)

    If Not rs Is Nothing Then
        If Not (rs.EOF And rs.BOF) Then
            Set cmb.Recordset = rs
            ' enforces the combobox to load completely
            lngCount = cmb.ListCount
        End If
    End If

fnADOComboboxSetRS_Exit:
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then rs.Close
        Set rs = Nothing
    End If
    Exit Sub

fnADOComboboxSetRS_Error:
    Select Case Err
        Case Else
            fnErr "modODBC->fnADOComboboxSetRS", True
            Resume fnADOComboboxSetRS_Exit
    End Select
End Sub

(The function fnADOSelectCommon opens an ADO recordset and gives it back. The function fnErr shows a message box with the error, if there was one.)

（函数 fnADOSelectCommon 打开一个 ADO 记录集并将其返回。函数 fnErr 显示一个带有错误的消息框，如果有的话。）

As this function closes the opened recordset there should be no problem with the memory. I tested it and didn't saw any increasing of memory which wasn't released after closing the form with the comboboxes.

当这个函数关闭打开的记录集时，内存应该没有问题。我对其进行了测试，并没有看到在使用组合框关闭表单后没有释放的任何内存增加。

In the Unload Event of the form you can additionaly use a "Set rs=Me.Comboboxname.Recordset" and then close it. This should not be necessary regarding memory, but it may be better to free up open connections (if used with a backend database server).

在窗体的卸载事件中，您还可以使用“Set rs=Me.Comboboxname.Recordset”然后关闭它。这对于内存来说不是必需的，但最好释放打开的连接（如果与后端数据库服务器一起使用）。

Cheers,

干杯，

Christian

基督教

回答by rexreponte

In MS Access, it's ok, but in VB, you may use something like this using adodc (Jet 4.0):

在 MS Access 中，没问题，但在 VB 中，您可以使用 adodc (Jet 4.0) 使用类似的东西：

Private sub Form1_Load()
   with Adodc1
     .commandtype = adcmdtext
     .recordsource = "Select * from courses"
     .refresh

     while not .recordset.eof
           combo1.additem = .recordset.coursecode
           .recordset.movenext
     wend
   end with
End Sub

回答by Tony Toews

A combo box control does not have a recordset property. It does have a RowSource property but Access is expecting a SQL string in there.

组合框控件没有记录集属性。它确实有一个 RowSource 属性，但 Access 期望在那里有一个 SQL 字符串。

You can change the RowSourceType to the name of a user defined "callback" function. Access help will give you more information including sample code by positioning yourself on the RowSourceType and pressing F1. I use this type of function when I want to give the users a list of available reports, drive letters, or other data that is not available via a SQL query.

您可以将 RowSourceType 更改为用户定义的“回调”函数的名称。通过将自己定位在 RowSourceType 上并按 F1，访问帮助将为您提供更多信息，包括示例代码。当我想向用户提供可用报告、驱动器号或其他无法通过 SQL 查询获得的数据的列表时，我会使用这种类型的函数。

I don't understand what you mean by your third paragraph with respect to using data directly from the server side. Or rather I don't understand what the problem is with using standard queries.

我不明白你的第三段关于直接从服务器端使用数据的意思。或者更确切地说，我不明白使用标准查询的问题是什么。