Remove selected items from List Box in Microsoft Access

[errol999]

I'm using Access 2003.

I have a List Box which I populate by selecting items from a drop down Combo Box. I now want to delete from the List Box only the selected, but not necessarily sequencial items (either one at a time or several).

I found some code at this website:

http://www.599cd.com/tips/access/listbox-additem-2000/

The author suggests creating a Text Box, then first temporarily storing the text that is selected in the List Box as follows:

MyText = MyList.ItemData(MyList.ListIndex)

Then, by use of the following code, the text present in the Text Box is used to search the List Box, and the corresponding text is deleted:

Dim S As String
S = MyText & ";"
MyList.RowSource = Replace(MyList.RowSource, S, "")

The first bit works fine, i.e. the text selected in the List Box appears in the Text Box, but I have no luck when I choose to delete this text from the List Box.

My understanding is that this method would also only work for one line of text, and not multiple selections.

Any suggestions would be helpful.

[NeoPa]

This approach will only work if the ListBox is populated manually. If RowSource is a query then this will not work.

Multiple selections can be made to work, but I suggest you get the fundamentals working correctly first.

Welcome to Bytes!

[ADezii]

If the Row Source of your List Box is a Value List, you can use the following code which does not require the use of a Text Box:

Dim lst As ListBox
Dim intRowCtr As Integer
Dim strBuild As String

'Substitute your own List Box Name
Set lst = Me![lstTest]

With lst
  If .ItemsSelected.Count = 0 Then Exit Sub

  For intRowCtr = 0 To .ListCount - 1
    If Not .Selected(intRowCtr) Then
      strBuild = strBuild & .ItemData(intRowCtr) & ";"
    End If
  Next

  strBuild = Left$(strBuild, Len(strBuild) - 1)

  .RowSource = strBuild
End With

[errol999]

This approach will only work if the ListBox is populated manually. If RowSource is a query then this will not work.

Multiple selections can be made to work, but I suggest you get the fundamentals working correctly first.

Welcome to Bytes!

Thanks. The Listbox is populated manually - I'll work on the fundamentals first.

[errol999]

If the Row Source of your List Box is a Value List, you can use the following code which does not require the use of a Text Box:

Dim lst As ListBox
Dim intRowCtr As Integer
Dim strBuild As String

'Substitute your own List Box Name
Set lst = Me![lstTest]

With lst
  If .ItemsSelected.Count = 0 Then Exit Sub

  For intRowCtr = 0 To .ListCount - 1
    If Not .Selected(intRowCtr) Then
      strBuild = strBuild & .ItemData(intRowCtr) & ";"
    End If
  Next

  strBuild = Left$(strBuild, Len(strBuild) - 1)

  .RowSource = strBuild
End With

Thanks ADezii. I'll give that a try.

[errol999]

Thanks ADezii. I'll give that a try.

Ok, this worked perfectly, until I had just one item left if in the List Box. If I try and delete the last item I get run-time error 5 for this line:

   strBuild = Left$(strBuild, Len(strBuild) - 1)

which makes sense I think . I've not figured out yet how to get round this but might try putting in an If Then line to deal with this last item. If you have a suggestion that would be great. Thanks

[NeoPa]

I would replace lines #12 through #19 with :

    If Not .Selected(intRowCtr) Then
      strBuild = strBuild & ";" & .ItemData(intRowCtr)
    End If
  Next intRowCtr

  If strBuild = "" Then strBuild = ";"

  .RowSource = Mid(strBuild, 2)

[errol999]

Ok. That did the trick - thanks so much!

[NeoPa]

Pleased to hear it Errol :)

Basically, each element was attached to a separator character when found. If none was found, then the code that stripped out the separator character would fail as it was expecting one, but we had none there. Adding that to the situation where nothing was found allowed the code to work and pick up the empty string. All fine & dandy.

[errol999]

Pleased to hear it Errol :)

Basically, each element was attached to a separator character when found. If none was found, then the code that stripped out the separator character would fail as it was expecting one, but we had none there. Adding that to the situation where nothing was found allowed the code to work and pick up the empty string. All fine & dandy.

Thanks for the explanation which has helped me make more sense of it. I can see I still have a lot to learn, but have picked up some really useful tips on this question. I'll now persevere with getting the rest of the dbase functional!

[NeoPa]

That's music to my ears. So glad we could help :)