Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filled cells dont appear as filled | Excel Discussion (Misc queries) | |||
Combobox query | Excel Discussion (Misc queries) | |||
Tricky ComboBox / Filter query | Excel Discussion (Misc queries) | |||
Combobox Query | Excel Programming | |||
How Do I Load A ComboBox RowSource From The Results Of Another ComboBox | Excel Programming |