2 Listboxes

[DS]

I have 2 listboxes based on the same table.
As an example only the Table is named "Colors"
and has at this point the following records, (more can be added later)
Red, Blue, Yellow, Green.
If Listbox 1 has Red and Blue in it. How can I get the remaining
records, in this case Yellow and Green to show up in Listbox 2 when I
click on Listbox 1?
Any help appreciated.
Thnaks
DS

 

[Guest]

DS,

It is not clear just how the first listbox is getting its records, but here
is how I do it.

If you have a table named "tblColors" with only one field named "Color".

You can populate the first list box with the following sqk statement in the
row source of the listbox:
SELECT tblColors.Color FROM tblColors WHERE (((tblColors.Color)="red")) OR
(((tblColors.Color)="blue"));

This sql statement will cause Red and Blue to appear in the first listbox.

Then place the following sql statement in the row source of the second
listbox:

SELECT tblColors.Color FROM tblColors WHERE (((tblColors.Color) Not In
(SELECT tblColors.Color FROM tblColors WHERE (((tblColors.Color)="red")) OR
(((tblColors.Color)="blue")); )));

This sql statement will cause all values that exist in the "tblColors"
table, but are not already listed in the first lisbox, to be displayed in the
second listbox.

 

[DS]

This part sounds like it's going up the right tree!

SELECT tblColors.Color FROM tblColors
WHERE (((tblColors.Color) Not In
SELECT tblColors.Color FROM tblColors
WHERE (((tblColors.Color)="red")) OR
(((tblColors.Color)="blue")); )));

Can I use this without the where statement? Remember that since I don't
really know beforehand what the values in the first listbox are going
to be. Is it a loop statement that is need here?
Thanks for your help.
DS

 

[Guest]

Just replace the part of the sql statement enclosed in the parentheses after
the "No In" with what ever statement you wind up with in the first list box.

There are times that these sql statements have to be developed through code
at runtime.

 

[DS]

Just replace the part of the sql statement enclosed in the parentheses after
the "No In" with what ever statement you wind up with in the first list box.

There are times that these sql statements have to be developed through code
at runtime.

Thanks Mr B.
DS