ComboBox rowSource from Access Query - How to

[timarndt@core.com]

I'm setting up a UserForm where one of the controls will be a combo box listing specific data returned from a query of an Access Database. I have successfully connected to the database and know how to make a recordset, but I don't know how to make the RowSource of the ComboBox be the RecordSet I returned.

I spoke with somebody who mentioned something about Datasets and Binding, but I don't really know what they are talking about.

Any help would be great! Thanks,
Tim.

 

[dk]

Hello,

The MS forms combobox doesn't have a propery where you can bind to a recordset - you need to populate it through code. The following example opens an ADO connection to a the sample Northwind database and then populates a combobox with category names from the category table.

Hope it helps you. Post back if you get stuck.

<pre><FONT COLOR="#00007F">Private Sub</FONT> UserForm_Initialize()
PopulateCombo
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Sub</FONT>



<FONT COLOR="#00007F">Private</FONT> <FONT COLOR="#00007F">Sub</FONT> PopulateCombo()
<FONT COLOR="#00007F">Dim</FONT> adoCN <FONT COLOR="#00007F">As</FONT> ADODB.Connection, adoRS <FONT COLOR="#00007F">As</FONT> ADODB.Recordset
<FONT COLOR="#00007F">Dim</FONT> strSQL <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">String</FONT>


<FONT COLOR="#007F00">'Open connection to the database - using Northwind in this example</FONT>
<FONT COLOR="#00007F">Set</FONT> adoCN = <FONT COLOR="#00007F">New</FONT> ADODB.Connection
adoCN.<FONT COLOR="#00007F">Open</FONT> "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\program files\microsoft office\office\samples\northwind.mdb;" & _
"User Id=admin;" & _
"Password="



<FONT COLOR="#007F00">'Open a recordset from the categories tables</FONT>
strSQL = "SELECT CategoryName FROM Categories"


<FONT COLOR="#00007F">Set</FONT> adoRS = <FONT COLOR="#00007F">New</FONT> ADODB.Recordset
adoRS.<FONT COLOR="#00007F">Open</FONT> strSQL, adoCN, adOpenForwardOnly, adLockReadOnly


<FONT COLOR="#007F00">'This is the part which populates the combobox</FONT>


<FONT COLOR="#00007F">With</FONT> Me.ComboBox1


.Clear


<FONT COLOR="#00007F">While</FONT> <FONT COLOR="#00007F">Not</FONT> adoRS.EOF
.AddItem adoRS.Fields("CategoryName").Value
adoRS.MoveNext
<FONT COLOR="#00007F">Wend</FONT>


<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">With</FONT>


adoRS.<FONT COLOR="#00007F">Close</FONT>
adoCN.<FONT COLOR="#00007F">Close</FONT>


<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Sub</FONT>
</pre>