Selecting an Item from a list box. Click & Select VS Using Code

[KitaYama]

I have a list box with 3 columns.
I'm trying to get the third column of the selected item.
I CLICK and select an item from the list box, then click "Read Data" button with following code.

Private Sub btnReadData_Click()
    With lstBox
        If .ItemsSelected.Count = 1 Then
            MsgBox .Column(2)
        End If
    End With
End Sub

It works perfect and I have what I was looking for.

Now I use the following code to select an item.

Private Sub btnSelect_Click()
    With lstBox 
        .Selected(3) = True
    End With
End Sub

If I click the "Read Data" button, I receive an error :
Run time error "94" Invalid use of Null.

Why clicking and selecting an item from a list box is different with using code and selecting an item?

A demo file is attached.
1- Click and select an item from the list box, then click "Read Data" button. You receive a msgbox with third column value.
2- Click "Select an Item" button to select a random item , then click "Read Data". You receive the error.

Thanks for any advice.

 

[MarkK]

If you know the row, pass that value to the Access.ListBox.Column property, like...

Private Sub btnReadData_Click()
    With lstBox
        If .ItemsSelected.Count = 1 Then
            ' the .ItemsSelected array contains the selected row numbers, so we call the first element of the array
            MsgBox .Column(2, .ItemsSelected(0))
        End If
    End With
End Sub

 

[theDBguy]

Hi. Can't download your file right now. Are we talking about a single or multi select listbox?

Sent from phone...

 

[KitaYama]

@MarkK thanks for taking your time and looking into this.

I know how to modify the code to work.
My question was why that code works for click & select, but throws an error when the item is selected by code.

thanks again.

 

[KitaYama]

Hi. Can't download your file right now. Are we talking about a single or multi select listbox?

Sent from phone...

Yes, Multi select with Extended option.

Thanks

 

[arnelgp]

try:

 

[theDBguy]

Yes, Multi select with Extended option.

Thanks

Hi. Thanks for the clarification. I'll take a look tomorrow, if no one else does by then.

 

[MarkK]

The row of the Column property is ambiguous when the list is in multi-select mode, because it is possible that the list might have many rows selected. In that case, the row must be specified.

 

[KitaYama]

The row of the Column property is ambiguous when the list is in multi-select mode, because it is possible that the list might have many rows selected. In that case, the row must be specified.

I have a lstBox=Null to be sure nothing is selected, then I select a row.
If .ItemsSelected.Count=1 is passed in both versions (clicking and using code)
It means that up to this point Access understands that only one record is selected. To me it's very strange that listbox.Column(2) returns null in one version but is available in another.

 

[KitaYama]

try:

Thanks for your time. I'm not trying to make it work.
I'm trying to understand why using code to select a row is not the same as clicking the row.

To me, using code to select an item in a list box changes the row color. It's not actually selected.

 

[arnelgp]

It's not actually selected.

it is. you will just need to recursively loop through each rows to check whether it is selected or not (see the modified code on the db).