By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Remove selected items from List Box in Microsoft Access

P: 7
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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Dim S As String
  2. S = MyText & ";"
  3. 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.
Apr 13 '10 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. Dim lst As ListBox
  2. Dim intRowCtr As Integer
  3. Dim strBuild As String
  4.  
  5. 'Substitute your own List Box Name
  6. Set lst = Me![lstTest]
  7.  
  8. With lst
  9.   If .ItemsSelected.Count = 0 Then Exit Sub
  10.  
  11.   For intRowCtr = 0 To .ListCount - 1
  12.     If Not .Selected(intRowCtr) Then
  13.       strBuild = strBuild & .ItemData(intRowCtr) & ";"
  14.     End If
  15.   Next
  16.  
  17.   strBuild = Left$(strBuild, Len(strBuild) - 1)
  18.  
  19.   .RowSource = strBuild
  20. End With

Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,263
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!
Apr 13 '10 #2

ADezii
Expert 5K+
P: 8,607
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:
Expand|Select|Wrap|Line Numbers
  1. Dim lst As ListBox
  2. Dim intRowCtr As Integer
  3. Dim strBuild As String
  4.  
  5. 'Substitute your own List Box Name
  6. Set lst = Me![lstTest]
  7.  
  8. With lst
  9.   If .ItemsSelected.Count = 0 Then Exit Sub
  10.  
  11.   For intRowCtr = 0 To .ListCount - 1
  12.     If Not .Selected(intRowCtr) Then
  13.       strBuild = strBuild & .ItemData(intRowCtr) & ";"
  14.     End If
  15.   Next
  16.  
  17.   strBuild = Left$(strBuild, Len(strBuild) - 1)
  18.  
  19.   .RowSource = strBuild
  20. End With
Apr 13 '10 #3

P: 7
@NeoPa
Thanks. The Listbox is populated manually - I'll work on the fundamentals first.
Apr 14 '10 #4

P: 7
@ADezii
Thanks ADezii. I'll give that a try.
Apr 14 '10 #5

P: 7
@errol999
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:

Expand|Select|Wrap|Line Numbers
  1.    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
Apr 14 '10 #6

NeoPa
Expert Mod 15k+
P: 31,263
I would replace lines #12 through #19 with :
Expand|Select|Wrap|Line Numbers
  1.     If Not .Selected(intRowCtr) Then
  2.       strBuild = strBuild & ";" & .ItemData(intRowCtr)
  3.     End If
  4.   Next intRowCtr
  5.  
  6.   If strBuild = "" Then strBuild = ";"
  7.  
  8.   .RowSource = Mid(strBuild, 2)
Apr 14 '10 #7

P: 7
Ok. That did the trick - thanks so much!
Apr 14 '10 #8

NeoPa
Expert Mod 15k+
P: 31,263
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.
Apr 15 '10 #9

P: 7
@NeoPa
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!
Apr 15 '10 #10

NeoPa
Expert Mod 15k+
P: 31,263
That's music to my ears. So glad we could help :)
Apr 15 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.