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
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