How to populate a ComboBox with a recordset using VBA

Question

There is some literature in the expert exchange and teck Republic on using the combobox.recordset property to fill in combobox in the Access form.

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

The ability to use a recordset to populate a combobox control will allow the user to directly display server-side data.

Inspired by the two previous examples, I wrote the code as follows:

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: my persistent ADO connection to my database server
• fc (): my current / active form
• controls ("id_Personne"): this is a combobox control to populate the list of company employees
• Access Version 2003

Unfortunately this will not work!

In debug mode, I can verify that the recordset is correctly created, with the requested columns and data, and is correctly connected to the combobox control. Unfortunately, when I show the form, I get an empty combo box, no entries! Any help is appreciated.

EDIT:

This record set property is indeed available for a specific combobox, not a standard control, and I was very surprised to find it a few days ago. I have already tried to use the combobox callback function or populate the list using the "addItem" method for combobox. All this takes a lot of time.

Answer 1

I found a trick ... the "rowSourceType" property of the combobox control should be set to "Table / List". The display is now fine, but now I have another memory problem. Since I use these ADO entries in my forms, Access memory usage increases every time I view the form. The memory is not freed up either by stopping viewing or closing the form, which makes MS Access unstable and freezes regularly. I will open the question if I cannot solve this problem.

Answer 2

As said, you should get a RowSourceType in "Table / List" (or "Table / Requête" if in French) to show the query results in combobox.

Memory problems arise from opening a recordset (rsPersonne) without closing it. You must close them when you close / unload the form (but again, you will have problems with the scope, since the recordset is declared in the function, not in the form).

You can also try to create and save the request using the built-in Access request creator and connect the same request to the RowSource of your field. Thus, the request is checked and compiled in Access.

Answer 3

To install a control that accepts a row source into a recordset, you do the following:

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

Working with DAO Recordsets for sure, I have not tried ADO recordings because I have no real reason to use them.

When this is done, a simple query will not work to update the data; you must do a repeat of the set statement.

Answer 4

nice method using the Recordset property, thanks for this hint!

Patrick, the method that you showed on your page has a big drawback (I also tried it myself): the list of values ​​can be only 32 KB, if you exceed this limit, the function will cause an error. The callback method has the big disadvantage that it is very slow, and it is called once for each record, which makes it unsuitable for a longer list. Using the record set method works very well. I need this because my SQL string was longer than 32 KB (many index values ​​for WHERE ID IN (x, x, x, x, x ...)).

Here is a simple function that uses this idea to set a set of records in a combo box:

 ' 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 fnADOSelectCommon function opens the ADO recordset and returns it. The fnErr function displays an error message box, if any.)

Since this function closes the open recordset, there should be no memory problems. I tested it and did not see an increase in memory that was not released after closing the form with comboboxes.

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

Greetings

Christian

Answer 5

The list control does not have record set properties. It has a RowSource property, but Access expects an SQL string.

You can change the RowSourceType to the name of a custom callback function. Access helps you get more information, including sample code, positioning yourself in a RowSourceType and pressing F1. I use this type of function when I want to provide users with a list of available reports, drive letters, or other data that are not available using an SQL query.

I do not understand what you mean by the third paragraph regarding the use of data directly from the server. Rather, I do not understand what the problem is with using standard queries.

Answer 6

This is fine in MS Access, but in VB you can use something like this with 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