Combo box help

[Mandy]

Please can someone tell me how to populate a combo box from a SQL query against SQL2000
I’m using XL2000 and W2K. And should I be using activex or userform controls – is there a difference
Thanks for any hel

 

[Bob Phillips]

Use ADO to query SQL2000, and populate an array with the recordset, and then
load the combo from that array.

As to whether activex or userform, it depends what else you want. If it is
only a combobox, activex should be enough, if you want more controls related
to the combo, you would probably be better with a userform.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Please can someone tell me how to populate a combo box from a SQL query against SQL2000?
I'm using XL2000 and W2K. And should I be using activex or userform

controls - is there a difference?

 

[Mandy]

Sorry I’m a bit dense. (At the moment I am testing against Access and will change to SQL later). I have managed to run the query against SQL2000 and I have populated a recordset. I have used the code below to make sure I am pulling data back from the database. This code copies the recordset into a spreadsheet, but I can’t see how to populate a combo box. Please could you tell me what I am missing? Many Thanks

Sub Import_AccessData(

Dim cnt As ADODB.Connectio

Dim rst1 As ADODB.Recordse
Dim stDB As String, stSQL1 As String, stSQL2 As Strin
Dim stConn As Strin
Dim wbBook As Workboo
Dim wsSheet1 As Workshee
Dim lnField As Long, lnCount As Lon

Set cnt = New ADODB.Connectio
Set rst1 = New ADODB.Recordse
Set rst2 = New ADODB.Recordse

Set wbBook = ThisWorkboo
Set wsSheet1 = wbBook.Worksheets(1

stDB = "c:\ExcelTest.mdb

stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source=" & stDB & ";

stSQL1 = "SELECT DeptNo FROM NewTable

wsSheet1.Range("A1").CurrentRegion.Clea

With cn
..Open (stConn)
..CursorLocation = adUseClien
End Wit

With rst
..Open stSQL1, cnt
Set .ActiveConnection = Nothing
End Wit

With wsSheet
..Cells(2, 1).CopyFromRecordset rst
End Wit

rst1.Clos
Set rst1 = Nothin
cnt.Clos
Set cnt = Nothin

End Su


----- Bob Phillips wrote: ----

Use ADO to query SQL2000, and populate an array with the recordset, and the
load the combo from that array

As to whether activex or userform, it depends what else you want. If it i
only a combobox, activex should be enough, if you want more controls relate
to the combo, you would probably be better with a userform

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

Please can someone tell me how to populate a combo box from a SQL quer against SQL2000
I'm using XL2000 and W2K. And should I be using activex or userfor

controls - is there a difference

 

[Bob Phillips]

You are doing most of what I was suggesting and as you are copying the
recordset to a worksheet range, you could pick it up from there.

Just change this

With wsSheet1
..Cells(2, 1).CopyFromRecordset rst1
End With

to

With wsSheet1
..Cells(2, 1).CopyFromRecordset rst1
..ComboBox1.ListFillRange = .Range("A2:A100").Address
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Sorry I'm a bit dense. (At the moment I am testing against Access and

will change to SQL later). I have managed to run the query against SQL2000
and I have populated a recordset. I have used the code below to make sure I
am pulling data back from the database. This code copies the recordset into
a spreadsheet, but I can't see how to populate a combo box. Please could
you tell me what I am missing? Many Thanks.

 

[Mandy]

Bob
I get a compile error: Method or data member not foun
and the word '.ComboBox1' is highlighted
Am I able to just add your line of code to mine, or do I need to make some other changes
Mand
ps can I get the data to go straight to the combo box list and not so the sheet first?

 

[Bob Phillips]

That probably means that your combobox is not named ComboBox1.

Is it an ActiveX combo or a forms combo?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob,
I get a compile error: Method or data member not found
and the word '.ComboBox1' is highlighted.
Am I able to just add your line of code to mine, or do I need to make some other changes?
Mandy
ps can I get the data to go straight to the combo box list and not so the

sheet first?

 

[Jake Marx]

Hi Mandy,

Bob,
I get a compile error: Method or data member not found
and the word '.ComboBox1' is highlighted.
Am I able to just add your line of code to mine, or do I need to make
some other changes?

Instead of ComboBox1, you need to use the name of the ComboBox on your
worksheet.

ps can I get the data to go straight to the combo box list and not so
the sheet first?

Yes, you can do it directly. Here's one way (make sure you replace
"cboDeptNos" with the actual name of your ComboBox):

Sub test()
Dim cn As ADODB.Connection
Dim cd As ADODB.Command
Dim rst As ADODB.Recordset
Dim stDB As String

stDB = "c:\ExcelTest.mdb"
Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=Microsoft.Jet" & _
".OLEDB.4.0;Data Source=" & stDB & ";"
.CursorLocation = adUseClient
.Open
End With

Set cd = New ADODB.Command
With cd
Set .ActiveConnection = cn
'CommandText = "SELECT DeptNo FROM NewTable"
.CommandType = adCmdText
Set rst = .Execute
End With

With rst
If .State = adStateOpen Then
If Not (.BOF And .EOF) Then
Sheet1.cboDeptNos.List = _
Application.Transpose(.GetRows)
End If
.Close
End If
End With

Set rst = Nothing
Set cd = Nothing
Set cn = Nothing
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Mandy]

Thank you both so much
With your help I finally worked out what to do, and found some other errors in my thinking, which should make the rest of the programming easier

Thanks again
Mand

----- Mandy wrote: ----

Please can someone tell me how to populate a combo box from a SQL query against SQL2000
I’m using XL2000 and W2K. And should I be using activex or userform controls – is there a difference
Thanks for any hel