How to populate a ComboBox with a Recordset using VBA

ms-access vba combobox adodb recordset

83,771

Solution 1

I found the trick ... the "rowSourceType" property of the combobox control has to be set to "Table/Query". 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

Solution 2

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.

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

Solution 3

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

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).

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.

83,771

Author by

carveone

We developed a professional Information System for a contracting company using MS SQL Server as a database and ... MS Access as a user interface. It includes more than 300 transactional screens, 175 reports, does fantastic things like multi-countries payroll management, and it works pretty well, thank you! That gave us the opportunity to build a set of development tools (down to a basic but efficient source control tool) that allowed us to bring to the final users normalized, bullet-proof screens and transactions in a matter of hours. Incidentally, we had to acquire an extensive knowledge around subjects such as VBA, Office Integration, MS-SQL, database synchronization, database migration, reporting tools, ActiveX, etc. Depending on the moment, I do have other professional activities, where I have been hired on consultancies as an agriculture policy and development expert (my original academic training), or recruited as an independent board member for a mid-sized international company.

Updated on July 09, 2022

Comments

carveone almost 2 years
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.

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
```
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
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.
carveone almost 15 years

Thanks Tony. This recordset property is indeed available for the specific combobox object, not for the standard control object. I am also using this callback function for situations similar to yours. My problem is to find a way to populate combo boxes on the client's side with data from the server side. Until now I was creating local temporary tables to do so, but it is really time consuming. I was hoping that using a recordset will be more efficient.
David-W-Fenton almost 15 years

Why do you think that assigning a recordset to the combo box is going to be more efficient that letting Access/Jet manage the data retrieval of a SQL string? Do you mean you're using a disconnected recordset? I can't imagine why anyone would ever need what you're asking for -- it makes no sense to me whatsoever.
carveone almost 15 years

I'll check your proposal about the memory issue and get back to you asap.
carveone almost 15 years

Yes, teh ADO recordset is disconnected
Caltor almost 12 years

@David-W-Fenton If the source recordset is ADO then assigning it to the RecordSource property of the combobox looks appealing so you don't have to copy the content to a ValueList or Table. Especially as the MSDN documentation for RecordSource shows that you can assign ADO or DAO recordsets to this property. What it doesn't say is that you should only do it for Forms. Doing it on a combobox is very flaky if it works at all and I have found that once working situations will start throwing an error after a WindowsUpdate.
Quentin T. about 11 years

Don't work for me :/ Have an Error 91 : Bloc With not present
carveone about 11 years

If you want an advice you should give the faulty code and identify the line throwing the error.
Quentin T. about 11 years

Description of the error here : stackoverflow.com/questions/16231456/…
carveone about 11 years

Tou are setting the cursor type, lock type, cursor location of the recordset to their default value. Could you apply the values used in my demo code? You could also identify precisely the faulty line by numbering your lines of code and use some error management. Please check this possibility here: stackoverflow.com/a/357882/11436
carveone about 11 years

But I think the faulty line is "Set rs = cnn.execute ...". Could you confirm this? If I were you I would add a 'Set rs = New ADODB.recordset' somewhere in my code ...
Matt Browne almost 11 years

If you're having issues getting this to work, it may be easier to use the AddItem method. You can specify values for multiple columns by separating them with semicolons (AddItem only accepts a single string).
David Alan Condit over 8 years

Personally, I forgot to use the Set word for assigning a recordset to my control. Thanks!
Alan Fisher over 8 years

I always use Set rs = Nothing once it is done being consumed. I think that should stop the memory leak.