ComboBox Filled by Query
 

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

ComboBox Filled by Query
 

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

"DBAL" wrote:

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

ComboBox Filled by Query
 

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



"Alok" wrote:

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

"DBAL" wrote:

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

ComboBox Filled by Query
 

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

"DBAL" wrote:

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



"Alok" wrote:

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

"DBAL" wrote:

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