asked on 3/21/2018

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

VBA

Last Comment

Raj D

3/27/2018

Norie

3/21/2018

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

Raj D

3/21/2018

ASKER

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

Norie

3/21/2018

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?

Raj D

3/21/2018

ASKER

Thanks Norie I was under impression my listbox properties are not set right.
. Yes I am using ADO.

Norie

3/21/2018

Raj

Where is the listbox located?

Raj D

3/21/2018

ASKER

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

Norie

3/21/2018

Raj

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

Is it on a userform/document/spreadsheet/something else?

Raj D

3/22/2018

ASKER

Hi Norie, morning

This is on excel. I am calling a form which has this listbox which is getting data from DB.

Raj D

3/22/2018

ASKER

Hi Norie, morning

This is on excel. I am calling a form which has this listbox which is getting data from DB.

ASKER CERTIFIED SOLUTION