Multideleting in listbox problem help ! :)

[prometro]

Hi,

in appendix is .mdb file with this thema.

Inside you can find one form with listbox (with multi selecting ability).

I use this code :

    Dim strSQL      As String
    Dim i           As Variant

With Me.se1
For Each i In .ItemsSelected
    SQL = "DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(i) & " ;"
    CurrentDb.Execute (SQL)
Next
End With
Me.se1.Requery

Result is this one :
1) If I delete only one item in list, it is ok. I can do it again and again and
it is working fine.

BUT

2) If I delete more items in list at once, it is ok - but if I will try to
do it again then there is an error because .ItemData(i) value is Null.
I dont understand why???

Can anyone hlep me with it? Thank you
Jiri

 

[BlueIshDan]

.ItemData(.ItemsSelected(i))
I think this might be the problem.

 

[prometro]

.ItemData(.ItemsSelected(i))
I think this might be the problem.

Thak you for reply!
Do you mean replace :

SQL = "DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(i) & " ;"

with

"DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(.ItemsSelected(i)) & " ;"

?

I tryied it and it is not working ... did I some mistake?

 

[BlueIshDan]

Sorry, I didn't notice you were looping with a for each.

 

[BlueIshDan]

If .ItemsSelect.Count > 0 Then

End If
Wrap it around your code in your with statement

 

[prometro]

If .ItemsSelect.Count > 0 Then

End If
Wrap it around your code in your with statement

I did it, but problem i still the same. Please, look at the code below.
I recognize that problem appears only in one situation :
When I mark the last and the last but one item in the listbox and then
run delete process. Both items are deleted without problem. But when I mark the last item in the listbox again and run delete >>> error appears!
Interesting is that MsgBox (.ItemData(i)) shows me right value, but then error appears ... better to use file in appendix I posted.

Dim strSQL As String
Dim i As Variant

With Me.se1

If .ItemsSelected.Count > 0 Then

For Each i In .ItemsSelected
    MsgBox (.ItemData(i))
    SQL = "DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(i) & " ;"
    CurrentDb.Execute (SQL)
Next

End If

End With
Me.se1.Requery

 

[BlueIshDan]

Did you allow the code to run all the way trough and display all message boxes? None showed as null?

For Fun Try:

    Dim strSQL As String
    
    With Me.se1
    
        If .ItemsSelected.Count > 0 Then
            
            SQL = "DELETE '*' FROM [t1] WHERE "
            
            For i = 0 To .ItemsSelected.Count - 1
            
                MsgBox (.ItemData(.ItemsSelected(i)))
                SQL = SQL & " id = " & .ItemData(.ItemsSelected(i)) & " OR "
                
            Next
            
            SQL = Left(SQL, Len(SQL) - 4) & ";"

            CurrentDb.Execute SQL

        End If
        
    End With
    
    Me.se1.Requery

 

[prometro]

Dear BlueIshDan,

Yes I tried it, deleting is working fine, but as I said, error appears only in one situation.
Maybe I didnt explain it correctly because my english ...
So I prepared pdf document with explanation ... in appendix.

Please, look at it. Thank you

Jiri

 

[Fran Lombard]

I was able to recreate your issue - but only if I had the listbox set to MultiSelect - Simple

If I changed the MultiSelect to Extended it did not display this strange behavior.

Based on what I tested it looks like ItemsSelected is not being properly maintained when multiselect is set to simple. It was even showing the wrong count of selected items.

If you can change the MultiSelect property of the ListBox to Extended you should be ok.

 

[prometro]

I was able to recreate your issue - but only if I had the listbox set to MultiSelect - Simple

If I changed the MultiSelect to Extended it did not display this strange behavior.

Based on what I tested it looks like ItemsSelected is not being properly maintained when multiselect is set to simple. It was even showing the wrong count of selected items.

If you can change the MultiSelect property of the ListBox to Extended you should be ok.

Hi Fran Lombard,

thank you for reply, yes I tested MultiSelect - Simple and also Extended. Error appears in both choices ... your test was ok?

But I did change - at the end of the procedure I added reload of source :

Me.se1.RowSource = "SELECT t1.id, t1.polozka FROM t1"
Me.se1.Requery

... and part of problem is solved ... althought it is strange step

But of course this results in other problem. When I reload source like this,
then position of listbox view skip to the beggining ....
... and this is not good when you have many items and you use scrollbar and
after deleting it skips to the begiining of list.

There must be better solution ...

Jiri

 

[Fran Lombard]

Glad to hear you got a resolution.