Databases
StackOverflow2013
Postsdbo
POHow to populate a ComboBox with a Recordset using VBA
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to populate a ComboBox with a Recordset using VBA
    text
    copied!<p>There is some literature available at <a href="http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_22462900.html" rel="nofollow noreferrer">expert's exchange</a> and at <a href="https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/howdoi/?p=192" rel="nofollow noreferrer">teck republic</a> about using the combobox.recordset property to populate a combobox in an Access form. </p> <p>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.</p> <p>Being able to use a recordset to populate a combobox control would allow the user to directly display data from the server's side. </p> <p>Inspired by the 2 previous examples, I wrote some code as follow:</p> <pre><code>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 </code></pre> <p>Where:</p> <ul> <li>connexionActive: is my permanent ADO connection to my database server</li> <li>fc(): is my current/active form</li> <li>controls("id_Personne"): is the combobox control to populate with company's staff list</li> <li>Access version in 2003</li> </ul> <p>Unfortunately, it doesn't work!</p> <p>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.</p> <p>EDIT:</p> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload