two ‘interactive’ listboxes

Topic

New Reply

Any ideas how to:
1. populate a list box with some data from a table or query (the easy bit!), then
2. let the user select items in the list box and
3. move the selected items to a second list box and remove them from the first.

There’s no problem doing it in the database tables, but any ideas how to do it with arrays or perhaps the list boxes themselves or something like that? I only need the contents of the second list as an intermediate result, after that I don’t need to store the list at all.

Marty

BTW, on one of my PCs the Solutions.mdb refuses to show the help window. Any ideas on this one?

Reply | Quote

Replies

What you want to do is generally referred to as a “pick list”, and you should be able to find examples in the knowledgebase or in help. You don’t need a separate array, because you put the selected values into the second listbox and remove them from the first. The listboxes themselves serve as your “arrays”.

If you have trouble finding what you need, post again and one of us will walk you through it.

Reply | Quote

Walking walking walking…

Thanks for your post. I don’t find what I want in help or in the Solutions.mdb. The closest I got was the RowSourceType function. Can you please point me to a knowledgebase, and that should get me going.

I’ve done the pick list thing before but only by changing data in the tables, which I don’t want to do.

Thanks,

Marty

Reply | Quote

Marty,

I’m attaching a zip file with an Access 2000 picklist sample database in it. This uses the event procedures behind Add and Remove buttons to add an item to one list and remove it from the other. It’s a lot more work in Access 97, because it lacks functions like Split(), Join() and Replace(), so you would have to build the equivalents.

Reply | Quote

From which folder are you opening solutions.mdb. Did you move the dataBase to another folder and leave the the other two related file in that folder

Reply | Quote

Hi Marty,

I found a solution that’s worked very well for me from a past Woody’s Access Watch. There is an option for using single-select listbox and a multi-select listbox.
I’ve tried several different methods and found this solution the quickest and easiest of them all. It uses DAO and a temporary table to store the “Available” and “Selected” lists.

You can find the code and information at:
http://www.woodyswatch.com
It’s in Access Watch numbers 2.22 (for single-select) and 2.23 (for multiple-select)
(I’m sorry I couldn’t include a direct link – I’m having trouble getting to the website right now)

HTH

-MarkJ-

Reply | Quote

Thanks Charlotte and Mark.

Unfortunately I only have Access 97 so I can’t look at the attachment (object lesson in quoting version numbers, oops sorry!).

I checked out WAW 2.23 and the pictures look pretty funky – seems like what I want to do although I notice that tables are used.

Maybe I shouldn’t be so stubborn and just go with tables. I suppose I can then let the user choose to keep the selection list or reset it at their own discretion. May be better that way.

Do you know how to get a copy of the demonstration files? When I looked at the page there was no obvious link to download a copy.

thanks again,
Marty

Reply | Quote

Hi Marty,

Here’s the link:
http://www.helenfeddema.com/access.htm

Access Archon Article 71 (for single) and 72 (for multiple) paired listboxes.

Also, you don’t have to store the intermediate data once the editing process is finished. I always delete all records from the temp tables when the form closes.

I have an example of my own. Let me know if you need to see it and I’ll post it.

HTH

-MarkJ-

Reply | Quote

Thanks for that – The multiselect listbox came in very handy. I altered the code a fair bit to get the result I wanted, but the example code to cope with multiple selections was great.

I ended up using a table to store the “selected” set. This will actually end up being a feature, as the user can make a selection and leave it, then each time they open the form they are not obliged to repeat the exercise. Given there are 2500 records if no filters are applied, I’m sure they’ll be happy with this.

By the way, does anyone know how to remove the vertical dividing lines in a multi-column listbox?

Reply | Quote

If you don’t want dividing lines, you would have to make each row a single column (or hide the other columns). Consider dividing lines a “feature” and deal with it accordingly.

Reply | Quote