Remove selected items from multiselect listbox

Yusuf

Active Member
Joined
Jun 1, 2004
Messages
337
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
 
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-com:eek:ffice:eek:ffice" /><o:p> </o:p>
End Sub
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
Code:
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

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

Code:
        DoCmd.RunSQL "DELETE * FROM [tblItems] WHERE [ItemName]='" & lstText.Column(0, varItem) & "'"
Denis
 
Upvote 0
Excellent!!!

Thanks guys. Both methods work.

Hope you have a good weekend

Cheers
Yusuf
 
Upvote 0
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 !
 
Upvote 0
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.
 
Last edited:
Upvote 0
>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:

Code:
    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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top