PDA

View Full Version : Solved: Problem with ListBox RowSource



Edoloto
10-27-2008, 01:31 PM
Greetings!

In my excel workbook I have I click a button that calls a form. This form has a ListBox that it is getting the data from a recordset. Now, when I'm debugging I see the data, however, as soon as I try to assign my TempList to a ListBox.RowSource, I get an error saying "Could not set the RowSource Property. Invalid Property Value"

I have tried so many things already such as adding each record to an array, and also as an item and nothing works.

The following is my code:


Private Sub UserForm_Initialize()
'MyTemplate
'MyTemplate2
' Create a connection object.
Dim rsCount As Integer
Dim f As Integer, r As Long, c As Long
Dim Test As Integer
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
'Now open the connection.
cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=TYUPO;INITIAL CATALOG=POLAND;INTEGRATED SECURITY=sspi;Connect Timeout=300"
cn.CommandTimeout = 300
' Create a recordset object.

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

' Extract the required records.
rs.Open "SELECT ACCOUNTNUM, NAME FROM VENDTABLE ORDER BY NAME", cn

Dim tempList As String

If Not rs.EOF Then rs.MoveFirst
tempList = lboxVendors.RowSource "I DON'T GET ERROR HERE
Do While Not rs.EOF
tempList = tempList & ";" & rs!ACCOUNTNUM & ";" & rs!Name
rs.MoveNext
Loop

lboxVendors.RowSource = tempList ' HERE I GET THE RROR
End Sub

I would greatly appreciate some help on this issue.

Thanks,

Eduardo

GTO
10-27-2008, 02:08 PM
Greetings Eduardo,

While very very inexperienced at ADO, I am confident that .RowSource is meant to accept a worksheet range, wherein Range is specified as a string, like:

ListBox1.RowSource = "a1:e4"

You might want to try lboxVendors.AddItem in your do...loop

Hope this helps,

Mark

mdmackillop
10-27-2008, 03:18 PM
tempList is a string, so can't be used as your rowsource. You can add a data to a listbox using List as in

Private Sub UserForm_Initialize()
Dim Arr
Arr = Range("a1:a10")
ListBox1.List() = Arr
End Sub


Try something like

Set Arr = rs.Open("SELECT ACCOUNTNUM, NAME FROM VENDTABLE ORDER BY NAME", cn)
ListBox1.List() = Arr

Edoloto
10-28-2008, 02:01 PM
Thank you very much, your code worked really good.

Miziel
11-20-2008, 10:19 AM
Set Arr = rs.Open("SELECT ACCOUNTNUM, NAME FROM VENDTABLE ORDER BY NAME", cn)


Hi!
I tried this but I cannot assign the Array to the recordset object. Excell encounters an error with rs.Open statement (Complie error: "Expected function or variable") ....

Bob Phillips
11-20-2008, 11:58 AM
rs.Open "SELECT ACCOUNTNUM, NAME FROM VENDTABLE ORDER BY NAME", cn
Arr = rs.GetRows

Miziel
11-21-2008, 02:50 AM
Thanks xId :)
That is better now, but the GetRows returns the values of the current record only. The code

Set Arr = rs.Open("SELECT ACCOUNTNUM, NAME FROM VENDTABLE ORDER BY NAME", cn)

worked fine for Edoloto but it did not work for me. Maybe it's a problem of my ActiveX reference?

Bob Phillips
11-21-2008, 03:33 AM
I don't know how that works, it doesn't work for you nor me, and I can see why. Arr is a variant not an object, so you don't set it.

The Getrows method I showed you is what I use in hundreds of projects, it returns an array of ALL the matching records, not just the one.