Combo Box and Recordset
 

I have a combo box displaying on my spreadsheet. The source for this is a SQL
view of just 2 columns. How do I have the recordset populate this combo box
WITHOUT having to paste the recordset in a spreadsheet column and then put
that range in the "listfillrange" property. I'd rather it be a clean feed
from SQL, is this possible? Here is my code: (the connection and view does
execute)

Dim strSQL As String
Dim myConnection As Object
Dim myCommand As Object
Dim myRecordSet As Object

Set myConnection = New ADODB.Connection
Set myCommand = New ADODB.Command
Set myRecordSet = New ADODB.Recordset

myConnection.ConnectionString = "Provider=SQLOLEDB;Data
Source=devserver;" "Database=ABC;Trusted_Connection=Yes;Prompt=Comple te"

strSQL = "Select * from vw_Rep_CUID"

myConnection.Open
Set myCommand.ActiveConnection = myConnection
Set myRecordSet.ActiveConnection = myConnection
myCommand.CommandText = strSQL
myCommand.CommandType = adCmdText
myCommand.Execute
myRecordSet.Open myCommand

'THIS IS WHERE ITS WRONG I believe.
Me.cbo_Rep_CUID.ListFillRange = myRecordSet

Combo Box and Recordset
 

Yes, that is where it goes wrong. A combo listfillrange cannot be a
recordset object. You will need to process it into the combobox, but you
don't have to put it in a spreadsheet range:
Dim ItemNo as Integer
ItemNo = 0
Me.cbo_Rep_CUID.Clear
While Not MyRecordset.EOF
Me.cbo_Rep_CUID.AddItem
Me.cbo_Rep_CUID.Column(0,ItemNo)=MyRecordset.Field s(0)
Me.cbo_Rep_CUID.Column(1,ItemNo)=MyRecordset.Field s(1)
ItemNo = ItemNo + 1
WEnd

--
- K Dales


"Bret" wrote:

I have a combo box displaying on my spreadsheet. The source for this is a SQL
view of just 2 columns. How do I have the recordset populate this combo box
WITHOUT having to paste the recordset in a spreadsheet column and then put
that range in the "listfillrange" property. I'd rather it be a clean feed
from SQL, is this possible? Here is my code: (the connection and view does
execute)

Dim strSQL As String
Dim myConnection As Object
Dim myCommand As Object
Dim myRecordSet As Object

Set myConnection = New ADODB.Connection
Set myCommand = New ADODB.Command
Set myRecordSet = New ADODB.Recordset

myConnection.ConnectionString = "Provider=SQLOLEDB;Data
Source=devserver;" "Database=ABC;Trusted_Connection=Yes;Prompt=Comple te"

strSQL = "Select * from vw_Rep_CUID"

myConnection.Open
Set myCommand.ActiveConnection = myConnection
Set myRecordSet.ActiveConnection = myConnection
myCommand.CommandText = strSQL
myCommand.CommandType = adCmdText
myCommand.Execute
myRecordSet.Open myCommand

'THIS IS WHERE ITS WRONG I believe.
Me.cbo_Rep_CUID.ListFillRange = myRecordSet

Combo Box and Recordset
 

Hi Bret,

Instead of doing this:

'THIS IS WHERE ITS WRONG I believe.
Me.cbo_Rep_CUID.ListFillRange = myRecordSet

you can do this:

Me.cbo_Rep_CUID.Column = myRecordSet.GetRows


Hope this helps
Raff

--
Raff
-----------------------------------------------------------------------
Raffy's Profile: http://www.excelforum.com/member.php...fo&userid=2760
View this thread: http://www.excelforum.com/showthread.php?threadid=47114