load data from MS Access
 

I have a function to retrieve data from Access database to my drop
down list using following code which works.

I tried to add another drop down box on the spreadsheet and load the
data, but it fails.

I just wonder can I fill 2 drop down box on the spreadsheet in the
same function?
If Excel does support for this, where I am missing to fill 2 drop
down box on the spreadsheet?

Youe help is great appreciated,


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rcArray As Variant
Dim strSource As String
Dim strSQL As String

strSource = "D:MyDB.mdb"
strSQL = "SELECT tblEmployee.Employee_ID FROM TblEmployee "

Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strSource & ";"

Set rs = New ADODB.Recordset
rs.Open strSQL, cn
rcArray = rs.GetRows


With Sheets("FRONT").cmbEmployee
.Clear
.ColumnCount = 1
.List = Application.Transpose(rcArray)
.ListIndex = -1
End With


rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

load data from MS Access
 

Assume you want the same data then use

With Sheets("FRONT").cmbEmployee2
.Clear
.ColumnCount = 1
.List = Application.Transpose(rcArray)
.ListIndex = -1
End With


--

Regards,
Nigel




"inungh" wrote in message
...
I have a function to retrieve data from Access database to my drop
down list using following code which works.

I tried to add another drop down box on the spreadsheet and load the
data, but it fails.

I just wonder can I fill 2 drop down box on the spreadsheet in the
same function?
If Excel does support for this, where I am missing to fill 2 drop
down box on the spreadsheet?

Youe help is great appreciated,


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rcArray As Variant
Dim strSource As String
Dim strSQL As String

strSource = "D:MyDB.mdb"
strSQL = "SELECT tblEmployee.Employee_ID FROM TblEmployee "

Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strSource & ";"

Set rs = New ADODB.Recordset
rs.Open strSQL, cn
rcArray = rs.GetRows


With Sheets("FRONT").cmbEmployee
.Clear
.ColumnCount = 1
.List = Application.Transpose(rcArray)
.ListIndex = -1
End With


rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing