Filter Multiselect Listbox

[Patrick]

I have a multiselect list box that points to a table with over 200,000 unique
part numbers. I also have a text box that will set the .RowSource for the
list box based on its value in the After_Update event. This works fine...
right up until I need to select more than one value from the listbox. Because
I am re-setting the .RowSource after each update to the textbox... I lose the
previously selected item in the listbox.
My first thought is to wonder if you can Filter in the .RowSource instead of
Selecting. My second thought is to beg help from people better at this stuff
than I am (this means YOU!)
Any ideas are appreciated!! patrick

 

[sparker]

Patrick,

when you say your first wonder is can you filter in the .rowsource instead
of selecting. I am not sure what that means as you know you can filter the
list you just said you are doing it and you know you can filter the values
selected as you must be working with them already. So if I am understanding
your post correctly then I am reading you have a multi-select listbox that
has the rowsource reset each time you change values in an adjoining text box
during the after-update event. It sounds like the problem you are having is
that when you select some parts in the multi-select listbox and then change
the row source for it you are loosing what you previously selected? Is this
the case? If that is the case you could simply use an array or temp table to
hold those values you select and maybe display them in a new adjoining
listbox etc... and then when the user is finished selecting all of the parts
desired use the values stored up in the temp table / array to perform
whatever action happens next. If this does not help then I must not
completely understand your post. I am trying though! =) If this is the case
and the solution and you need help with that then let us know! =)

 

[Patrick]

Thanks Sparker! You understood correctly. I could definitely use some help
with how to load the clicked values into an array. There are actually 6
multiselect listboxes and two regular listboxes on my form. I am having a
hard time figuring out which event to use to trigger loading a value into the
array.

When I say Filter vs Select... As far as I can determine, in Access, when
you filter data, you are not really subsetting it. The entire dataset is
still there, you are only seeing a portion of it. On the other hand,
Selecting data actually subsets the data and returns only the matching
records. I guess Filtering is only used when viewing forms or tables.

 

[sparker]

Patrick,

You may want to place an [Add] button on your form that once the user is
ready to add values from any of the 8 available listboxes (both multi-select
and not) the [Add] button OnClick event would then call a function that
checks all 8 available listboxes and adds any and all selections to your
array. This same function would then also update any display you provide to
the user that shows any and all previous selections from the listbox. This
will allow the user to filter and select and re-filter each and every box as
much as they wish. You may also want the update event from the adjoining text
box that triggers the re-filter of the listbox to go ahead and call the same
function as well prior to re-filtering the listbox. Does this sound about
right? Do you know how to work with arrays? Have you considered using a temp
table to hold the values? Let me know if there is anything I can do to help
you proceed. Take Care & God Bless

 

[sparker]

Patrick,

Another thought is that you will want to add a function to the click event
of the control you provide to your users that displays any and all selections
made from any listbox so that they can click on it and have any specific item
removed from your temp table and/or array that is holding the selected
values. A user is going to click on something that they do not want and need
a way to remove it from the list without having to start all over. Take Care
& God Bless ~
--
~ SPARKER ~

Patrick,

You may want to place an [Add] button on your form that once the user is
ready to add values from any of the 8 available listboxes (both multi-select
and not) the [Add] button OnClick event would then call a function that
checks all 8 available listboxes and adds any and all selections to your
array. This same function would then also update any display you provide to
the user that shows any and all previous selections from the listbox. This
will allow the user to filter and select and re-filter each and every box as
much as they wish. You may also want the update event from the adjoining text
box that triggers the re-filter of the listbox to go ahead and call the same
function as well prior to re-filtering the listbox. Does this sound about
right? Do you know how to work with arrays? Have you considered using a temp
table to hold the values? Let me know if there is anything I can do to help
you proceed. Take Care & God Bless

--
~ SPARKER ~

Thanks Sparker! You understood correctly. I could definitely use some help
with how to load the clicked values into an array. There are actually 6
multiselect listboxes and two regular listboxes on my form. I am having a
hard time figuring out which event to use to trigger loading a value into the
array.

When I say Filter vs Select... As far as I can determine, in Access, when
you filter data, you are not really subsetting it. The entire dataset is
still there, you are only seeing a portion of it. On the other hand,
Selecting data actually subsets the data and returns only the matching
records. I guess Filtering is only used when viewing forms or tables.