Multiple list boxes

[PC]

I'm wondering if anyone knows how to do this.

I have a table (Projects) that can have several
(Alternatives). 1 Project with many Alternatives. I've
divided these into 2 tables. Any Alternative can use a
number of Parcels which is stored in another table
(Parcels). As I have a "many to many" (Many Alternatives
using Many Parcels) situation, I have an intermediate
table that sits between the "Alternatives" table and
the "Parcels" table called (Alt_Parcels). I use the
Primary Keys from each of these 2 tables to make a unique
key for the intermediate table.

What I'd like the user to be able to do is select any
number of these Parcels from a listbox and show what
Parcels they have chosen in an adjacent listbox and it is
those chosen Parcels would be stored into the table
(Alt_Parcels)

Kind of like this;
Alt1 = Parcels 1,2,4,5,6
Alt2 = Parcels 1,3,5,6
Alt3 = Parcels 2,4,6
and so on....

It's easy to show all of the master Parcels in the one
list box, but I cannot figure out how to get them so the
user can say, double-click on the master Parcel listbox,
have that item moved over to the chosen listbox, then
remove that item from the master Parcel listbox so it
cannot be chosen again for this particular alternative. As
the master Parcel list must be used for any given
alternaitve, the master Parcel table is never really
tampered with, it is only there to show what Parcels are
available.

Has someone done this? Can someone guide me here? Can
someone maybe share some code that I can learn this from?

Thanks,
PC

 

[Andrew Smith]

I've done this sort of thing. My approach was:

- First list box is based on an "unmatched query" - ie in your case this
query would select everything from the Parcels table that did not appear in
the Alt_Parcels table (for a given Alternative).
- Second list box is based on the second table - in your case this would be
query that selects all the Parcels in Alt_Parcels for the Alternative.
- The double click event on the first list box runs code to append a new
record to the second table (Alt_Parcels) based on what is selected in the
first list box. It then requeries both list boxes.
- The double click event on the second list box deletes the selected item
from the table and requeries the list boxes.

Hope this helps

Andrew

 

[Sandra Daigle]

See my SelectRecords Sample database on
http://www.daiglenet.com/MSAccess.htm