Search code examples
ms-accessvbacomboboxadodbrecordset

How to populate a ComboBox with a Recordset using VBA

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.

Solution

  • 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