I am venturing from an experienced Access/VBA developer to doing some work in
Excel with VBA. I need some help with how to accomplish a specific task.
The objective is that the user needs to enter an employee name and have it
return the Employee Number and Department. This data is in an Access
database table.
I want to use a combo box to make it easy for the user to locate the
employee name and have it fill the cells in the row where the employee data
is.

In looking at how Excel combo boxes work, it says the combo rowsource has to
be a worksheet range. I was hoping a could use a recordset like you do in
Access. But since that does not seem to be available, I came up with this
approach:

Add a new worksheet.
Create the recordset that contains the employee table from the Access database
Use the CopyRecordsetFrom method to copy the row from the recordset into the
new worksheet.
Use the range the data is now in as my rowsource for the combo.
When the user selects a row from the combo, load the other columns' data
into the cells where the data needs to go.

The question is, is this a reasonable approach or is there a better method
to do this?

Ok....you should be able to fill a combo box from an access dataset....first
add a reference to Microsoft ActiveX Data Object (MDAC).

The following code creates...opens data source...returns dataset....and
closes the data source (you will have to make minor corrections to suit your
needs but should point you in the write direction). You can also use the the
same techique to return your look u information and then put it on a form or
in Cells.

Private m_strCnn As String
Private m_ADOConnection As New ADODB.Connection
Private m_ADORecordSet As New ADODB.Recordset
Private m_StoredProcedure As String
Private m_sSql As String


Public Function openConn()
On Error GoTo myErr:

m_strCnn = "provider=Microsoft.Jet.OLEDB.4.0;data source=" &
ThisWorkbook.CB & ";"
If m_ADOConnection.State = 0 Then
m_ADOConnection.Open (m_strCnn)
End If

Exit Function

myErr:

MsgBox (Error$)

End Function

Public Function closeConn() 'This function closes a valid ADO connection

On Error GoTo myErr:


m_ADOConnection.Close

Exit Function

myErr:

End Function

Public Function getRecordSet(ByVal SqlCommand As String) As ADODB.Recordset

'Open Connection
Call openConn

'This function returns a record set from a valid Open Connection
On Error GoTo myErr:
Set getRecordSet = New ADODB.Recordset
Call getRecordSet.Open(SqlCommand, m_ADOConnection, adOpenForwardOnly,
adLockReadOnly)



Exit Function

myErr:

MsgBox (Error$)
End Function


The Following code uses the return data set and applies it to the combo box.

Dim myRecord As New ADODB.Recordset
Set myRecord = Conn.getRecordSet("Exec myQRY")

myRecord.MoveFirst

While myRecord.EOF < True
myForm.ComboBox1.AddItem (myRecord.Fields(0).Value)
myRecord.MoveNext
Wend



"Klatuu" wrote:

I am venturing from an experienced Access/VBA developer to doing some work in
Excel with VBA. I need some help with how to accomplish a specific task.
The objective is that the user needs to enter an employee name and have it
return the Employee Number and Department. This data is in an Access
database table.
I want to use a combo box to make it easy for the user to locate the
employee name and have it fill the cells in the row where the employee data
is.

In looking at how Excel combo boxes work, it says the combo rowsource has to
be a worksheet range. I was hoping a could use a recordset like you do in
Access. But since that does not seem to be available, I came up with this
approach:

Add a new worksheet.
Create the recordset that contains the employee table from the Access database
Use the CopyRecordsetFrom method to copy the row from the recordset into the
new worksheet.
Use the range the data is now in as my rowsource for the combo.
When the user selects a row from the combo, load the other columns' data
into the cells where the data needs to go.

The question is, is this a reasonable approach or is there a better method
to do this?

Thanks for the input, Jim. I do have one question (or 2), First, I am
familiar with adding items to an Access combo, but I did not see that an
Excel combo can accept a value list. The help file says it has to be a
range, so I am a little confused on that.

Second. I see your code for instantiating a recordset, but I wonder what is
the difference between what you propose and the code below that I have
already tested.

Dim wrkJet As Workspace
Dim dbf As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo UserForm_Activate_Error

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wrkJet.OpenDatabase _
("\\rsltx1-bm01\busmgmt\Dev CISCMS\ciscmsdata.mdb")
Set rst = dbs.OpenRecordset("Select * from tbllkemployee;")

Thanks again for your response.
"Jim F" wrote:

Ok....you should be able to fill a combo box from an access dataset....first
add a reference to Microsoft ActiveX Data Object (MDAC).

The following code creates...opens data source...returns dataset....and
closes the data source (you will have to make minor corrections to suit your
needs but should point you in the write direction). You can also use the the
same techique to return your look u information and then put it on a form or
in Cells.

Private m_strCnn As String
Private m_ADOConnection As New ADODB.Connection
Private m_ADORecordSet As New ADODB.Recordset
Private m_StoredProcedure As String
Private m_sSql As String


Public Function openConn()
On Error GoTo myErr:

m_strCnn = "provider=Microsoft.Jet.OLEDB.4.0;data source=" &
ThisWorkbook.CB & ";"
If m_ADOConnection.State = 0 Then
m_ADOConnection.Open (m_strCnn)
End If

Exit Function

myErr:

MsgBox (Error$)

End Function

Public Function closeConn() 'This function closes a valid ADO connection

On Error GoTo myErr:


m_ADOConnection.Close

Exit Function

myErr:

End Function

Public Function getRecordSet(ByVal SqlCommand As String) As ADODB.Recordset

'Open Connection
Call openConn

'This function returns a record set from a valid Open Connection
On Error GoTo myErr:
Set getRecordSet = New ADODB.Recordset
Call getRecordSet.Open(SqlCommand, m_ADOConnection, adOpenForwardOnly,
adLockReadOnly)



Exit Function

myErr:

MsgBox (Error$)
End Function


The Following code uses the return data set and applies it to the combo box.

Dim myRecord As New ADODB.Recordset
Set myRecord = Conn.getRecordSet("Exec myQRY")

myRecord.MoveFirst

While myRecord.EOF < True
myForm.ComboBox1.AddItem (myRecord.Fields(0).Value)
myRecord.MoveNext
Wend



"Klatuu" wrote:

I am venturing from an experienced Access/VBA developer to doing some work in
Excel with VBA. I need some help with how to accomplish a specific task.
The objective is that the user needs to enter an employee name and have it
return the Employee Number and Department. This data is in an Access
database table.
I want to use a combo box to make it easy for the user to locate the
employee name and have it fill the cells in the row where the employee data
is.

In looking at how Excel combo boxes work, it says the combo rowsource has to
be a worksheet range. I was hoping a could use a recordset like you do in
Access. But since that does not seem to be available, I came up with this
approach:

Add a new worksheet.
Create the recordset that contains the employee table from the Access database
Use the CopyRecordsetFrom method to copy the row from the recordset into the
new worksheet.
Use the range the data is now in as my rowsource for the combo.
When the user selects a row from the combo, load the other columns' data
into the cells where the data needs to go.

The question is, is this a reasonable approach or is there a better method
to do this?

rst.MoveFirst

while rst.EOF = false
me.combobox1.additem(rst.Fields(0).Value) rst.MoveNext
Wend

whe 'Zero is the index of the field you want binded to the combobox.

Not Sure if this answers your question?!?!?


"Klatuu" wrote:

Thanks for the input, Jim. I do have one question (or 2), First, I am
familiar with adding items to an Access combo, but I did not see that an
Excel combo can accept a value list. The help file says it has to be a
range, so I am a little confused on that.

Second. I see your code for instantiating a recordset, but I wonder what is
the difference between what you propose and the code below that I have
already tested.

Dim wrkJet As Workspace
Dim dbf As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo UserForm_Activate_Error

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wrkJet.OpenDatabase _
("\\rsltx1-bm01\busmgmt\Dev CISCMS\ciscmsdata.mdb")
Set rst = dbs.OpenRecordset("Select * from tbllkemployee;")

Thanks again for your response.
"Jim F" wrote:

Ok....you should be able to fill a combo box from an access dataset....first
add a reference to Microsoft ActiveX Data Object (MDAC).

The following code creates...opens data source...returns dataset....and
closes the data source (you will have to make minor corrections to suit your
needs but should point you in the write direction). You can also use the the
same techique to return your look u information and then put it on a form or
in Cells.

Private m_strCnn As String
Private m_ADOConnection As New ADODB.Connection
Private m_ADORecordSet As New ADODB.Recordset
Private m_StoredProcedure As String
Private m_sSql As String


Public Function openConn()
On Error GoTo myErr:

m_strCnn = "provider=Microsoft.Jet.OLEDB.4.0;data source=" &
ThisWorkbook.CB & ";"
If m_ADOConnection.State = 0 Then
m_ADOConnection.Open (m_strCnn)
End If

Exit Function

myErr:

MsgBox (Error$)

End Function

Public Function closeConn() 'This function closes a valid ADO connection

On Error GoTo myErr:


m_ADOConnection.Close

Exit Function

myErr:

End Function

Public Function getRecordSet(ByVal SqlCommand As String) As ADODB.Recordset

'Open Connection
Call openConn

'This function returns a record set from a valid Open Connection
On Error GoTo myErr:
Set getRecordSet = New ADODB.Recordset
Call getRecordSet.Open(SqlCommand, m_ADOConnection, adOpenForwardOnly,
adLockReadOnly)



Exit Function

myErr:

MsgBox (Error$)
End Function


The Following code uses the return data set and applies it to the combo box.

Dim myRecord As New ADODB.Recordset
Set myRecord = Conn.getRecordSet("Exec myQRY")

myRecord.MoveFirst

While myRecord.EOF < True
myForm.ComboBox1.AddItem (myRecord.Fields(0).Value)
myRecord.MoveNext
Wend



"Klatuu" wrote:

I am venturing from an experienced Access/VBA developer to doing some work in
Excel with VBA. I need some help with how to accomplish a specific task.
The objective is that the user needs to enter an employee name and have it
return the Employee Number and Department. This data is in an Access
database table.
I want to use a combo box to make it easy for the user to locate the
employee name and have it fill the cells in the row where the employee data
is.

In looking at how Excel combo boxes work, it says the combo rowsource has to
be a worksheet range. I was hoping a could use a recordset like you do in
Access. But since that does not seem to be available, I came up with this
approach:

Add a new worksheet.
Create the recordset that contains the employee table from the Access database
Use the CopyRecordsetFrom method to copy the row from the recordset into the
new worksheet.
Use the range the data is now in as my rowsource for the combo.
When the user selects a row from the combo, load the other columns' data
into the cells where the data needs to go.

The question is, is this a reasonable approach or is there a better method
to do this?

Sorry, Jim, maybe I was not clear. manipulating a recordset is not a problem
for me. My question was, will an Excel combo box accept a value list like an
Access combo? Also, in you code, you are dealing with a one column combo, I
neglected to point out I have 3 columns.

Also, in my previous post, I included code I am currently using to create my
recordset. It is not the same as what you posted. My question on that was
whether my method will work, or is the method you posted neccesary to be able
to populate the combo box?

"Jim F" wrote:

rst.MoveFirst

while rst.EOF = false
me.combobox1.additem(rst.Fields(0).Value) rst.MoveNext
Wend

whe 'Zero is the index of the field you want binded to the combobox.

Not Sure if this answers your question?!?!?


"Klatuu" wrote:

Thanks for the input, Jim. I do have one question (or 2), First, I am
familiar with adding items to an Access combo, but I did not see that an
Excel combo can accept a value list. The help file says it has to be a
range, so I am a little confused on that.

Second. I see your code for instantiating a recordset, but I wonder what is
the difference between what you propose and the code below that I have
already tested.

Dim wrkJet As Workspace
Dim dbf As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo UserForm_Activate_Error

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wrkJet.OpenDatabase _
("\\rsltx1-bm01\busmgmt\Dev CISCMS\ciscmsdata.mdb")
Set rst = dbs.OpenRecordset("Select * from tbllkemployee;")

Thanks again for your response.
"Jim F" wrote:

Ok....you should be able to fill a combo box from an access dataset....first
add a reference to Microsoft ActiveX Data Object (MDAC).

The following code creates...opens data source...returns dataset....and
closes the data source (you will have to make minor corrections to suit your
needs but should point you in the write direction). You can also use the the
same techique to return your look u information and then put it on a form or
in Cells.

Private m_strCnn As String
Private m_ADOConnection As New ADODB.Connection
Private m_ADORecordSet As New ADODB.Recordset
Private m_StoredProcedure As String
Private m_sSql As String


Public Function openConn()
On Error GoTo myErr:

m_strCnn = "provider=Microsoft.Jet.OLEDB.4.0;data source=" &
ThisWorkbook.CB & ";"
If m_ADOConnection.State = 0 Then
m_ADOConnection.Open (m_strCnn)
End If

Exit Function

myErr:

MsgBox (Error$)

End Function

Public Function closeConn() 'This function closes a valid ADO connection

On Error GoTo myErr:


m_ADOConnection.Close

Exit Function

myErr:

End Function

Public Function getRecordSet(ByVal SqlCommand As String) As ADODB.Recordset

'Open Connection
Call openConn

'This function returns a record set from a valid Open Connection
On Error GoTo myErr:
Set getRecordSet = New ADODB.Recordset
Call getRecordSet.Open(SqlCommand, m_ADOConnection, adOpenForwardOnly,
adLockReadOnly)



Exit Function

myErr:

MsgBox (Error$)
End Function


The Following code uses the return data set and applies it to the combo box.

Dim myRecord As New ADODB.Recordset
Set myRecord = Conn.getRecordSet("Exec myQRY")

myRecord.MoveFirst

While myRecord.EOF < True
myForm.ComboBox1.AddItem (myRecord.Fields(0).Value)
myRecord.MoveNext
Wend



"Klatuu" wrote:

I am venturing from an experienced Access/VBA developer to doing some work in
Excel with VBA. I need some help with how to accomplish a specific task.
The objective is that the user needs to enter an employee name and have it
return the Employee Number and Department. This data is in an Access
database table.
I want to use a combo box to make it easy for the user to locate the
employee name and have it fill the cells in the row where the employee data
is.

In looking at how Excel combo boxes work, it says the combo rowsource has to
be a worksheet range. I was hoping a could use a recordset like you do in
Access. But since that does not seem to be available, I came up with this
approach:

Add a new worksheet.
Create the recordset that contains the employee table from the Access database
Use the CopyRecordsetFrom method to copy the row from the recordset into the
new worksheet.
Use the range the data is now in as my rowsource for the combo.
When the user selects a row from the combo, load the other columns' data
into the cells where the data needs to go.

The question is, is this a reasonable approach or is there a better method
to do this?

Ok...I think i understand the question. You want the combobox to display the
employee name but pass a different value like the employee id...correct? if
so...Excel combo boxes do not have (as far as I know) a way to tie a "value"
column to it.

As for the way you get the record set...I think the code you have will work.

"Klatuu" wrote:

Sorry, Jim, maybe I was not clear. manipulating a recordset is not a problem
for me. My question was, will an Excel combo box accept a value list like an
Access combo? Also, in you code, you are dealing with a one column combo, I
neglected to point out I have 3 columns.

Also, in my previous post, I included code I am currently using to create my
recordset. It is not the same as what you posted. My question on that was
whether my method will work, or is the method you posted neccesary to be able
to populate the combo box?

"Jim F" wrote:

rst.MoveFirst

while rst.EOF = false
me.combobox1.additem(rst.Fields(0).Value) rst.MoveNext
Wend

whe 'Zero is the index of the field you want binded to the combobox.

Not Sure if this answers your question?!?!?


"Klatuu" wrote:

Thanks for the input, Jim. I do have one question (or 2), First, I am
familiar with adding items to an Access combo, but I did not see that an
Excel combo can accept a value list. The help file says it has to be a
range, so I am a little confused on that.

Second. I see your code for instantiating a recordset, but I wonder what is
the difference between what you propose and the code below that I have
already tested.

Dim wrkJet As Workspace
Dim dbf As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo UserForm_Activate_Error

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wrkJet.OpenDatabase _
("\\rsltx1-bm01\busmgmt\Dev CISCMS\ciscmsdata.mdb")
Set rst = dbs.OpenRecordset("Select * from tbllkemployee;")

Thanks again for your response.
"Jim F" wrote:

Ok....you should be able to fill a combo box from an access dataset....first
add a reference to Microsoft ActiveX Data Object (MDAC).

The following code creates...opens data source...returns dataset....and
closes the data source (you will have to make minor corrections to suit your
needs but should point you in the write direction). You can also use the the
same techique to return your look u information and then put it on a form or
in Cells.

Private m_strCnn As String
Private m_ADOConnection As New ADODB.Connection
Private m_ADORecordSet As New ADODB.Recordset
Private m_StoredProcedure As String
Private m_sSql As String


Public Function openConn()
On Error GoTo myErr:

m_strCnn = "provider=Microsoft.Jet.OLEDB.4.0;data source=" &
ThisWorkbook.CB & ";"
If m_ADOConnection.State = 0 Then
m_ADOConnection.Open (m_strCnn)
End If

Exit Function

myErr:

MsgBox (Error$)

End Function

Public Function closeConn() 'This function closes a valid ADO connection

On Error GoTo myErr:


m_ADOConnection.Close

Exit Function

myErr:

End Function

Public Function getRecordSet(ByVal SqlCommand As String) As ADODB.Recordset

'Open Connection
Call openConn

'This function returns a record set from a valid Open Connection
On Error GoTo myErr:
Set getRecordSet = New ADODB.Recordset
Call getRecordSet.Open(SqlCommand, m_ADOConnection, adOpenForwardOnly,
adLockReadOnly)



Exit Function

myErr:

MsgBox (Error$)
End Function


The Following code uses the return data set and applies it to the combo box.

Dim myRecord As New ADODB.Recordset
Set myRecord = Conn.getRecordSet("Exec myQRY")

myRecord.MoveFirst

While myRecord.EOF < True
myForm.ComboBox1.AddItem (myRecord.Fields(0).Value)
myRecord.MoveNext
Wend



"Klatuu" wrote:

I am venturing from an experienced Access/VBA developer to doing some work in
Excel with VBA. I need some help with how to accomplish a specific task.
The objective is that the user needs to enter an employee name and have it
return the Employee Number and Department. This data is in an Access
database table.
I want to use a combo box to make it easy for the user to locate the
employee name and have it fill the cells in the row where the employee data
is.

In looking at how Excel combo boxes work, it says the combo rowsource has to
be a worksheet range. I was hoping a could use a recordset like you do in
Access. But since that does not seem to be available, I came up with this
approach:

Add a new worksheet.
Create the recordset that contains the employee table from the Access database
Use the CopyRecordsetFrom method to copy the row from the recordset into the
new worksheet.
Use the range the data is now in as my rowsource for the combo.
When the user selects a row from the combo, load the other columns' data
into the cells where the data needs to go.

The question is, is this a reasonable approach or is there a better method
to do this?