How to populate a ComboBox with a Recordset using VBA

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.

Answer by Tony Toews for How to populate a ComboBox with a Recordset using VBA

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.

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.

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.

Answer by Philippe Grondier for How to populate a ComboBox with a Recordset using VBA

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

Answer by Marcand for How to populate a ComboBox with a Recordset using VBA

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.

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.

Answer by Bitsqueezer for How to populate a ComboBox with a Recordset using VBA

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

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

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

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

Cheers,

Christian

Answer by rexreponte for How to populate a ComboBox with a Recordset using VBA

In MS Access, it's ok, but in VB, you may use something like this using 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  

Answer by Lance S for How to populate a ComboBox with a Recordset using VBA

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.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72