Removing selected items from a listbox

[cherman]

Hi there. I want to remove the selected items from a listbox. I'm using the
following code I got the from another post, but it doesn't work correctly.

Dim varItem As Variant, lngSelected() As Long, lngLoop As Long

If Me.lstAll_Filter_Values.ItemsSelected.Count > 0 Then

ReDim lngSelected(Me.lstAll_Filter_Values.ItemsSelected.Count - 1)

lngLoop = 0

For Each varItem In Me.lstAll_Filter_Values.ItemsSelected

lngSelected(lngLoop) = varItem

lngLoop = lngLoop + 1

Next varItem

For lngLoop = UBound(lngSelected) To 0 Step -1

Me.lstAll_Filter_Values.RemoveItem Index:=lngLoop

Next lngLoop

End If

If, for example, I have 5 items and the 1st 3 are selected then it works
like a charm, but if I have the last 2 selected or 1, 3 & 5 then it doesn't
work right. It seems to remove the right # of items, but not the right items.

Can someone help me modify this to work properly or give me another solution?

Thanks!

 

[Jeff Boyce]

You're asking a "how" question (how do I remove items from a listbox), but
not really filling us in on "what".

What will having a listbox with fewer items allow you to do?

I ask because there may be ways to accomplish "what" you want to do that
make better use of Access' features/functions.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[cherman]

Thanks for your reply! I don't really understand the nature of your question.
What I want to do is to allow my user to pick 1 or more items in the listbox
and click a button to remove the selected items. The code I provided is in
the click event of the button.

See my original post for a description of the problem. I assumed the
solution would be fairly easy, as it it's common to give users the ability to
select multiple items from a listbox and have them removed.

Thanks!

 

[Jeff Boyce]

Why would I, as a user, want to remove items? My question goes to the
underlying business need, rather than the action (i.e., a "how").

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

Hi there. I want to remove the selected items from a listbox. I'm using the
following code I got the from another post, but it doesn't work correctly.

What is the RowSource and RowSourceType of the listbox? Do you want the items
deleted from the table, or just not displayed in the listbox if they're
selected?

 

[cherman]

Hi there all again! Still waiting for some help. Someone here has to
understand what I need and be able to help.

Thanks!

 

[cherman]

Ah, yes, good question!

The RowSource Type is Value List and the RowSource is a set of items added
to the listbox from another list box. The items get added to the listbox in
question by my user, who is essentially building a list of items he wants. In
the process, he may decide he doesn't want some of them, and that is where my
problem lies.

Thanks!

 

[John W. Vinson]

Ah, yes, good question!

The RowSource Type is Value List and the RowSource is a set of items added
to the listbox from another list box. The items get added to the listbox in
question by my user, who is essentially building a list of items he wants. In
the process, he may decide he doesn't want some of them, and that is where my
problem lies.

The basis of the problem is that you're evidently storing data in a listbox
(in its text string RowSource value list to be precise). That's the wrong
place to store data!!! It should be in a Table.

I would GUESS that the user wants the items for some purpose, which I'll call
for want of any knowledge of your database, a "project". If there's a standard
list of items, which can be used in many Projects, I'd suggest that you have
two additional tables: a table of Items, and a table of ItemsChosen.

The listbox (or combo box if you prefer) could be based on an Unmatched Query
selecting those Items which are *not* in ItemsChosen for this project. You'ld
just requery the listbox after making a selection.

If I'm way off base please explain the context - what the items are, and for
what purpose they are being chosen.

 

[David W. Fenton]

The basis of the problem is that you're evidently storing data in
a listbox (in its text string RowSource value list to be precise).
That's the wrong place to store data!!! It should be in a Table.

I would GUESS that the user wants the items for some purpose,
which I'll call for want of any knowledge of your database, a
"project". If there's a standard list of items, which can be used
in many Projects, I'd suggest that you have two additional tables:
a table of Items, and a table of ItemsChosen.

The listbox (or combo box if you prefer) could be based on an
Unmatched Query selecting those Items which are *not* in
ItemsChosen for this project. You'ld just requery the listbox
after making a selection.

If I'm way off base please explain the context - what the items
are, and for what purpose they are being chosen.

I think you're ignoring the fact that recent versions of Access
allow .AddItem and .RemoveItem for combo-/listboxes.

So, you could have this:

Me!lstMyListbox.AddItem "Item 1"
Me!lstMyListbox.AddItem "Item 2"
Me!lstMyListbox.AddItem "Item 3"
Me!lstMyListbox.AddItem "Item 4"

....and then do this:

Me!lstMyListbox.RemoveItem "Item 2"

The listbox has to have it's RowSourceType set to Value List, but
otherwise it's pretty straightforward.

 

[John W. Vinson]

Me!lstMyListbox.AddItem "Item 1"
Me!lstMyListbox.AddItem "Item 2"
Me!lstMyListbox.AddItem "Item 3"
Me!lstMyListbox.AddItem "Item 4"

...and then do this:

Me!lstMyListbox.RemoveItem "Item 2"

The listbox has to have it's RowSourceType set to Value List, but
otherwise it's pretty straightforward.

Ah. Haven't played with 2007 or 10 enough to see that! It's at least more
consistant with VB6.

I still feel that a listbox's rowsource is NOT a good data storage medium nor
is it good practice to keep redefining what is essentially a form property.

 

[Jeff Boyce]

It would appear you are making a "tell me which button to push" request.
The responses you've been receiving are based on some degree of experience
with both the product and the process.

If you want someone to "just tell me how", you'll probably find someone.
But if you didn't want folks to use their brains and offer their experience
and perspective, and are not interested in considering alternate approaches,
you probably needed to be clearer about that.

Good luck on your project!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[David W. Fenton]

Ah. Haven't played with 2007 or 10 enough to see that! It's at
least more consistant with VB6.

It's been there since A2003 at least.

I still feel that a listbox's rowsource is NOT a good data storage
medium nor is it good practice to keep redefining what is
essentially a form property.

Certainly not a good "data storage" medium, but OK for storing
information temporarily. I created a quick-and-dirty utility last
night after I replied to you, as I needed it for the project I'm
working on, and I used AddItem with the listboxes I used:

http://dfenton.com/DFA/download/Access/DataRename.zip

Here's the description from my Access downloads page:

Utility to synchronize table names and field names between two
data files of similar structure. Also can rename fields in a
batch removing data-type prefixes (e.g., "txt" for text fields,
etc.), and can delete all lookup tables.

It's really quick-and-dirty, so there's no error handling for all
sorts of things that can go wrong, but it's going to make it
possible for me to update the client's back end very quickly. I now
have to write some code to check that the results are identical in
structure, and I still have to finish importing new relationships
(which can only be applied once I've backuped up and cleaned out the
orphaned data).

Anyway, in that case, I think AddItem is an excellent way to
populate the listboxes. I'd be interested to hear if you disagreed.

(and yes, it's ugly! I was spending my time on functionality, not
appearance)

 

[David W. Fenton]

Still waiting for some help. Someone here has to
understand what I need and be able to help.

???

You replied to a post that handed you the code on a silver platter.
What more can you want?