run-time error '-214735251 (80020005) type mismatch on a listbox

[dasrocko1]

I am pulling my data for my listbox from SQL Oracle. SELECT * FROM TABLE WHERE THIS= THAT is giving me a recordset count over 8700. for testing I queried only 25 records and my listbox worked but when I do the whole recordset I get 'run-time error '-214735251 (80020005)': Could not set the List property. Type mismatch'.

Private Sub UserForm_Initialize()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim i As Integer
i = 0
Me.lb1.Clear
Me.lb1.ColumnWidths = "70;65;250;125;60;50;50;15"
Set cn = New ADODB.Connection
cn.Open ("PROVIDER=ORAOLEDB.ORACLE;DATA SOURCE=ICVP;USER ID=*****;PASSWORD=*****")
​

Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM TABLE WHERE THIS= THAT"
rs.Open strSQL, cn, adOpenKeyset, adLockBatchOptimistic
​

rs.MoveFirst
Do Until rs.EOF
​

With Me.lb1
​

.AddItem
.List(i, 0) = rs("A")
.List(i, 1) = rs("B")
.List(i, 2) = rs("C")
.List(i, 3) = rs("D")
.List(i, 4) = rs("E")
.List(i, 5) = rs("F")
.List(i, 6) = rs("G")
.List(i, 7) = rs("H")
i = i + 1
​

End With
rs.MoveNext
​

Loop
​

rs.Close
Set rs = Nothing
​

cn.Close
Set cn = Nothing
​

End Sub

I tried to do .List(i, 0) = Val(rs("A")) around all the fields but got 'Run-time error'94': Invalid use of Null'

Any Idea's

Thanks
Rich A

 

[Norie]

Are there Null values in some of the fields?

 

[dasrocko1]

I did find some so I changed my SQL up using nvl() and changed all null values to zero and still got the same error

 

[Norie]

What happens if you dump the recordset to a worksheet using CopyFromRecordset and then populate the listbox using either RowSource or List?