Remove selected items from multiselect listbox

[Yusuf]

Hi

Is there a quick way of doing this.
I have quite a few listboxes that operate on one screen. The user can select items from any of the listboxes and a Refresh command button will trigger the items to be removed.

If it's possible, are you able to advise me on a method or code for only one listbox.
I would simply mirror this code for the other listboxes

Many thanks
Y

 

[Trevor G]

Denis
I have this feeling that I'll be kicking myself as this is probably something so simple that I'm just not getting!

Please indicate where I'm going wrong.
Field1 is Text and when items are selected, I do not get an error but nothing happens and both the table and the listbox contain selected items

Dim i As Integer

DoCmd.SetWarnings False
For i = List2.ListCount - 1 To 0 Step -1
If List2.Selected(i) Then
DoCmd.RunSQL "DELETE * FROM [table2] WHERE [field1]='" & List2.Selected(i) & "'"
' List2.RemoveItem i
End If
Next i

List2.Requery
DoCmd.SetWarnings True

End Sub

Regards
Y

Y,

Try this, replace the things in red with your table, field and list box names:

Private Sub cmdNewDelete_Click()
On Error GoTo Err_cmdNewDelete_Click
Dim strSQL As String

DoCmd.SetWarnings False
strSQL = "DELETE * " & _
"FROM [tblAdvisor] " & _
"WHERE [tblAdvisor].[ID]=" & Me![lstDelete1].Column(0)
DoCmd.RunSQL strSQL
Me![lstDelete1].Requery ' requery the list

DoCmd.SetWarnings True

Exit_cmdNewDelete_Click:

Err_cmdNewDelete_Click:
Exit Sub
MsgBox Err.Description
Resume Exit_cmdNewDelete_Click
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
End Sub

 

[SydneyGeek]

I haven't looped through listbox items for a while and it shows, because I had the syntax wrong. Use this instead -- modify to suit control, table and field names.

Private Sub Command5_Click()
    Dim varItem
    
    DoCmd.SetWarnings False
    For Each varItem In lstItems.ItemsSelected
        DoCmd.RunSQL "DELETE * FROM [tblItems] WHERE [ItemName]='" & lstText.Column(1, varItem) & "'"
    Next varItem
    DoCmd.SetWarnings True
    
    lstText.Requery
End Sub

Points to note: In this case the text (bound column) is the *second* column of the list. Column counts start from 0. So, if you only have 1 column, or the first column is the bound column, change this

        DoCmd.RunSQL "DELETE * FROM [tblItems] WHERE [ItemName]='" & lstText.Column(1, varItem) & "'"

to this

        DoCmd.RunSQL "DELETE * FROM [tblItems] WHERE [ItemName]='" & lstText.Column(0, varItem) & "'"

Denis

 

[Yusuf]

Excellent!!!

Thanks guys. Both methods work.

Hope you have a good weekend

Cheers
Yusuf

 

[keldsor]

There MUST be an fatal error in this listbox **** - it can't be true you have to mount SQL for removing selected items in a listbox - it's just total nuts !

 

[Micron]

Why? If the listbox row source is a table or query, you can't remove an item from the listbox without removing it from the source, then requerying the listbox. The only way to do that is a) go to the table and remove the record; b) run sql to remove it on the fly. From what I can see, that's the situation here. If the row source is a list, that's a different thing. As for the attempt to iterate through the list and removed the row by it's index, what happens when you have n rows and remove one? You now have n-1, so not only is your counter out of wack, every item in the list now has a new index number. Totally out of whack. Can only be done with an array AFAIC.

 

[keldsor]

>Micron
"By design" it SHOULD have been mode so that there is a method like this:

myListview.SelectedItems.Delete

As it is now NOTHING is made by design concerning deleting items.

And yes, it could be solved by using an array - but why should WE bother ?

Here it is:

    Dim i As Integer
    Dim intCount As Integer
    Dim aryValues() As Variant
    For i = 0 To lstMangler.ListCount - 1
        If lstMangler.Selected(i) Then
            ReDim Preserve aryValues(intCount)
            aryValues(intCount) = i
            DeleteFile (lstMangler.ItemData(i))
            intCount = intCount + 1
        End If
    Next i
    For i = UBound(aryValues) To 0 Step -1
        lstMangler.RemoveItem aryValues(i)
    Next i