listbox error

Hi,

I am using a listbox to get my recordset from Oracle.

Using additem i am pulling data in list box

. List(i, 0)=recordset!column1
. List(i, 1)=recordset!column2

Etc...

I am getting the error
Run time error 380
Could not set the list property. Invalid property value at
. List(i, 10)=recordset!column11

Can you please help me with this?

Thanks
Raj DAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
That's what I thought but wasn't sure.

Anyway, as I said you need to get the data into an array.

There are 2 ways to do that:

1 Loop through the recordset.

2 Use ADO's GetRows.

For the latter the code might look something like this.
arrData = recordset.GetRows()

ListBox1.List = arrData ' might need to tranpose, if so use Application.Transpose(arrData)

Open in new window


Looping would me a little more involved, so give the above a shot first and if it doesn't work post back.
1
 
NorieVBA ExpertCommented:
Could it be possible that the data you've pulled from the Oracle database contains Null values?

If it does you could try something like this.
If Not IsNull(recordset!column1) Then
    . List(i, 0)=recordset!column1
End If
If Not IsNull(recordset!column2) Then
    . List(i, 1)=recordset!column2
End If

Open in new window

Note if there nulls it could be possible to eliminate them from the recordset using the query you used on the database.

PS You might be able to use a loop to populate the listbox.
For J = 0 To recordset.Fields.Count-1
    If Not IsNull(recordset.Fields(J) Then
        .List(i, J) = recordset.Fields(J)
    End If
Next J

Open in new window

0
 
Raj DAuthor Commented:
Thank you Norie, I was having the null issue as well, however that got sorter with the isnull though suggested.

However for some reason I get same error at 10th loop. I tried the for method you gave again same error at


Is there a limit set to display only 1, records?

Thanks
Raj
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
NorieVBA ExpertCommented:
Raj

When using AddItem, which is what I assume you are using to add rows to the listbox, there is a limit to the no of columns you can populate and that limit, if I recall correctly, is 10 (might be 9).

The way round this is to use List to populate the listbox and to do that you'll need to populate an array with the data from the recordset.

How are you getting the recordset?

Are you using ADO?
0
 
Raj DAuthor Commented:
Thanks Norie I was under impression my listbox properties are not set right.
. Yes I am using ADO.
0
 
NorieVBA ExpertCommented:
Raj

Where is the listbox located?
0
 
Raj DAuthor Commented:
In a form. Sorry for incomplete info.

I am Open to replace the list box with something else if it's neatly presentable.

Also reduced my fields to 10 and tried to use the for loop suggestion, but only last field got displayed in form may be I messed up.

Thanks
Raj
0
 
NorieVBA ExpertCommented:
Raj

Is the listbox in Word/Excel? Some other application?

Is it on a userform/document/spreadsheet/something else?
0
 
Raj DAuthor Commented:
Hi Norie, morning

This is on excel. I am calling a form which has this listbox which is getting data from DB.
0
 
Raj DAuthor Commented:
Hi Norie, morning

This is on excel. I am calling a form which has this listbox which is getting data from DB.
0
 
Raj DAuthor Commented:
thanks Norie.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.