ComboBox Filled by Query

[Guest]

Hello,
I was wondering if someone knows if I can fill a combobox directly with a
query recordset Or do I have to dump the query onto a sheet and use the
ListFillRange property to populate the combobox?

Anyone know the syntax to paste the recordset into a combo box when the user
clicks the drop down arrrow?

Much appreciation and respect for the one who can pull this off!
Thanks,
DBAL

 

[Guest]

Hi DBAL
Yes you can fill the combobox with a query. To do this you need to set
reference to ADO or DAO etc. I give below the piece of code that will get the
data from an access database

Dim db as Database
Dim rs as Recordset
Set db=OpenDatabase(...Path and name of the database..)
set rs = db.OpenRecordset(...any select query..)
do while not rs.eof
cbo1.AddItem rs(0).Value
rs.movenext
Loop
rs.close
set rs=Nothing
db.close
Set db=Nothing


Hope this helps.

Alok Joshi

 

[Guest]

Alok,
Thanks for the post. I need to load the Excel Combo Box with a Recordset
that I am retrieving from a Microsoft SQL Server. Creating the connection
and the recordset is no problem, but I wasn't sure how to bind the combo box
to the recordset.

I imagine that your code:

do while not rs.eof
cbo1.AddItem rs(0).Value
rs.movenext
Loop

Will work the same even though I am not using Access, but a SQL Server
recordset.

Also,do you know which Excel event I can use to do this? Should I use the
_DropButtonClick() event.... or is there another event that will run this
code when the file is first opened? That would be great. Thanks again.

DBAL

 

[Guest]

Hi DBAL,

Yes this should work whether you get the data from Access or SQL Server. I
normally use Workbook_Open event to populate the Combo box if it is placed on
a worksheet. However, if it is on a form then I use the Form_Initalize event.

Alok