Help to remove multiple items from the listbox 3

[kkgusta]

Hi, I am trying to create a cmd button to remove the selected record from a lisbox. However the following code i use is only allow me to delete one record. Can someone here know hoe to fix it ?


Sub RemoveFromList(RemoveList As Access.ListBox)

Dim varlistItem As Variant

For Each varlistItem In RemoveList.ItemsSelected

RemoveList.RemoveItem (varlistItem)

Next varlistItem

End Sub

 

[TheAceMan1]

How are ya kkgusta . . .

What you want to do depends on two things:
[ol][li]The listbox is a part of a [blue]menubar, toolbar or form.[/blue][/li]
[li]The [blue]RowSource[/blue] is [blue]Table, Query, or Value List.[/blue][/li][/ol]
[blue] . . . and your magic formula is?[/blue]

See Ya! . . . . . .

 

[kkgusta]

I am good man.

the listbox is part of the form and the rowsource type is value list.
the code above is in a module and the code i use in the form is :

Private Sub cmdRemoveSelected_Click()
Dim msgbox1
If Me.ListBulkResult = -1 Then
msgbox1 = MsgBox("No Item Selected!!", vbCritical, "No Script Selected")
End If

Call RemoveFromList(ListBulkResult)

End Sub

 

[MajP]

I know there is a much smarter way to do this, but this is what I came up with.

There are two problems. Once you remove an item the list refreshes and there are no more items selected. The second problem is after you remove an item from the list they get new indexes because you removed an item. So if you remove item 0 then item 7 becomes item six. If you remove 0 and 1, the original item 7 is now item 5.

So my trick was to first read the items selected collection and store the indices in an array. Then use those indices to remove an item. However, each time I remove an item I need to remember that an item's index is now one less

Private Sub Command11_Click()
  Dim removeItem As Variant
  Dim indexArray() As Variant
  Dim counter As Integer
  Dim intItems As Integer
  intItems = Me.listRemove.ItemsSelected.Count
  
  If Not intItems = 0 Then
    ReDim indexArray(0 To intItems - 1)
    'make a array
    For Each removeItem In Me.listRemove.ItemsSelected
      indexArray(counter) = removeItem
      counter = counter + 1
    Next removeItem
  
  'remove items and each time you remove an item its index
  ' reduces by one.
    For counter = 0 To intItems - 1
    Me.listRemove.removeItem (indexArray(counter) - counter)
    Next counter
  End If

 

[earthandfire]

MajP, you could remove from last to first and therefore not be affected by the reindexing of the list box:

    For counter = intItems - 1 To 0 Step - 1
      Me.listRemove.removeItem (indexArray(counter))
    Next counter

Hope this helps.

 

[TheAceMan1]

kkgusta . . .

In the code module of the form call the following routine:

[blue]Private Sub PurgeSel()
   Dim LBx As ListBox, RowSrc As String, itm
   
   Set LBx = Me![purple][b]ListBoxName[/b][/purple]
   RowSrc = LBx.RowSource
   
   For Each itm In LBx.ItemsSelected
      RowSrc = Replace(RowSrc, LBx.ItemData(itm), "")
   Next
   
   RowSrc = Replace(RowSrc, """" & """", "")
   RowSrc = Replace(RowSrc, ";;", "")
   
   LBx.RowSource = RowSrc
   
   Set LBx = Nothing

End Sub[/blue]

See Ya! . . . . . .

 

[MajP]

AceMan's code will work for a single column listbox, but you will have to use mine or add to his code to cycle through each column for a multi-column. I find it very rare that I have a single column combo or list because usually have a hidden primary key.

 

[kkgusta]

I am agree with you MajP. I am using multiple column listbox and have some hidden primary key. I am appreciate the help from you guys. All the code you guys provided are so cool. Thank a lot.

 

[PHV]

And what about this ?
Sub RemoveFromList(RemoveList As Access.ListBox)
Dim i As Integer
With RemoveList
For i = .ListCount - 1 To 0 Step -1
If .Selected(i) Then .RemoveItem(i)
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

PHV,
I have the same problem with your code. It appears to me that once you remove an item from the list, it unselects the selected items. Therefore it only removes the first (in this case last) item, after that no items are selected. That is why I ended up storing the array.