如何使用VBA使用记录集填充ComboBox [英] How to populate a ComboBox with a Recordset using VBA
问题描述
专家交流并在关于使用combobox.recordset属性在Access表单中填充组合框的问题.
There is some literature available at expert's exchange and at teck republic about using the combobox.recordset property to populate a combobox in an Access form.
这些控件通常在控件的行来源"属性中填充"SELECT *"字符串，引用应用程序客户端可用的表或查询.当需要在组合框中显示服务器端数据时，我将创建一个临时本地表并导入请求的记录.这非常耗时，特别是对于大型表.
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.
能够使用记录集填充组合框控件将允许用户直接从服务器端显示数据.
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
位置:
- connexionActive:是我到数据库服务器的永久ADO连接
- fc():是我当前/有效的表单
- controls("id_Personne"):是 组合框控件填充 公司员工名单
- 2003年的访问版本
- 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
不幸的是，它不起作用！
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.
此记录集属性确实可用于特定的组合框对象，而不适用于标准控件对象，而几天前发现它令我感到非常惊讶. 我已经尝试使用combobox的回调函数，或使用combobox的"addItem"方法填充列表.所有这些都很耗时.
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.
推荐答案
我发现了窍门...组合框控件的"rowSourceType"属性必须设置为"Table/List".显示现在可以，但是我现在有另一个内存问题.由于我在表单上使用了这些ADO记录集，因此每次浏览表单时，Access的内存使用都在增加.不能通过停止浏览或关闭表单来释放内存，这会使MS Access不稳定并定期冻结.我不能解决这个问题.
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
这篇关于如何使用VBA使用记录集填充ComboBox的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
