How do I fill a combo box dynamically with a list of items drawn from a
specific column in a database?

Assume the name of the database is prototype, and its already defined in
the odbc. Assume also that the column name is "STO_Name".

Any help or insights are appreciated.
:)

Ben

you wont need ODBC...

you must set a reference to
"Microsoft ActiveX Data Objects" (any version..)

else try a querytable in excel and copy the ODBC connectstring
from there.


Option Explicit

Function GetRowsFromDB() As Variant
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

Set con = New Connection
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\MyDBs\Northwind.mdb;"
con.Open
Set rst = con.Execute("Select orderID,orderdate from orders")
GetRowsFromDB = rst.GetRows()
rst.Close
con.Close
Set rst = Nothing
Set con = Nothing
End Function

Private Sub UserForm_Initialize()
'getrows returns a "horizontal" array
'thus use column not list
Me.ComboBox1.Column = GetRowsFromDB

End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ben Adler wrote :

How do I fill a combo box dynamically with a list of items drawn from
a specific column in a database?

Assume the name of the database is prototype, and its already defined
in the odbc. Assume also that the column name is "STO_Name".

Any help or insights are appreciated.
:)

Ben

I'll give it a shot - thanks!

In article om,
says...
you wont need ODBC...

you must set a reference to
"Microsoft ActiveX Data Objects" (any version..)

else try a querytable in excel and copy the ODBC connectstring
from there.


Option Explicit

Function GetRowsFromDB() As Variant
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

Set con = New Connection
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\MyDBs\Northwind.mdb;"
con.Open
Set rst = con.Execute("Select orderID,orderdate from orders")
GetRowsFromDB = rst.GetRows()
rst.Close
con.Close
Set rst = Nothing
Set con = Nothing
End Function

Private Sub UserForm_Initialize()
'getrows returns a "horizontal" array
'thus use column not list
Me.ComboBox1.Column = GetRowsFromDB

End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ben Adler wrote :

How do I fill a combo box dynamically with a list of items drawn from
a specific column in a database?

Assume the name of the database is prototype, and its already defined
in the odbc. Assume also that the column name is "STO_Name".