Need help w/ Multiple Listboxes

[Tom]

I need some help with using "pairs of multiselect listboxes" to add/remove
records.

MVP Sandra Daigle has posted a wonderful sample file: "Select Records
(version 3.0 - 8/20/2003)" -- this file can be found at:
http://www.daiglenet.com/msaccess.htm

Now, when opening the form, select option "Select Students by Class", then
"MultiSelect Students by Class".

Sandra's concepts makes total sense... there are multiple classes and
students can be enrolled in one or more class(es).

Now, here's what I need for my db... although I don't have "classes" and
"students", I'll use the same examples for explation purposes.

Instead of allowing "students" to enroll in multiple "classes", I want this
to be a 1:1 relationship... each student can be enrolled in only one class
at a given time.

Essentially, in my db, I have a table with "required functions" and another
table with "existing capabilities". Once I found the "best match" between
RequiredFunction (Class) and ExistingCapability (Student), I want the
matched capabilty removed from the "available view". This will ensure
that no double-allocation of resources occurs. This problem is purely
"technical" (not functional).

Does anyone know how to tweak Sandra's sample to get this to work?

Thanks,
Tom

 

[Douglas J Steele]

Looking at the left-hand listbox on frmClassPersons, the RowSource is based
on the following SQL:

SELECT p.personnbr, p.personname,cp.personnbr FROM tblpersons AS p LEFT JOIN
parmqryClassPersonsfrmClassPersons AS cp ON p.personnbr = cp.personnbr WHERE
cp.personnbr is null

What this is doing is joining the list of all persons (tblpersons) to the
list of people enrolled in the specific class
(parmqryClassPersonsfrmClassPersons), and returning those that aren't in the
list.

If you want to return those who aren't taking any classes at all, you could
change that SQL to

SELECT p.PersonNbr, p.PersonName, cp.PersonNbr FROM tblpersons AS p LEFT
JOIN tblPersonClasses AS cp ON p.PersonNbr = cp.PersonNbr WHERE cp.PersonNbr
Is Null

 

[Tom]

Douglas:

Perfect... that's exactly what I was looking for.

Thousand thanks!!!

Tom