Combo Box and Recordset

[Bret]

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=Complete"

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

 

[K Dales]

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.Fields(0)
Me.cbo_Rep_CUID.Column(1,ItemNo)=MyRecordset.Fields(1)
ItemNo = ItemNo + 1
WEnd

 

[Raffy]

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