Restrict ListBox contents

  • Thread starter Thread starter Gerry
  • Start date Start date
G

Gerry

I have a combobox and a list box.
I know how to show data in the list box based on the selection in the
combobox.
But how do I restrict data in the list box to be OTHER than the selection in
the Combobox.
Here is the table set up.

tblCastTitles (for the List box) CastTitleID - CastTitle
tblProductions(for the Combo) ProductionID - Production
tblCasts (Created from above two tables) CastID - CastTitleID - ProductionID

When I select a Production in the Combo Box
I want the List box to show CastTitles that have NOT YET
been created with the chosen production.
Does this make sense?
I thought this would be so simple, but I can't figure it out.
Thanks for any help.
 
Assuming that the list box is using a query as its Row Source, and that
query is using the combo box as a source of the value for a Criterion, just
change the WHERE clause to be not equal to (or not like) the combo box's
value.

If you post the list box's SQL statement here, I'm sure we can assist.
 
Thanks Ken,
What you suggest is what I wrongly assumed.
Here are the complete details.
Thanks again for any help.

This is the Query for the ComboBox
"SELECT tblProductions.PoductionID, tblProductions.Production
FROM tblProductions;"

This is the Query for the List Box
"SELECT tblCastTitles.CastTitle, tblCast.ProductionID
FROM tblCastTitles INNER JOIN tblCast ON
tblCastTitles.CastTitleID = tblCast.CastTitleID WHERE
(((tblCast.ProductionID) Not Like
[Forms]![frmCreate_Casts]![cboProductions].[BoundColumn]));"

With this query I get CastTitles for Productions that have been
created for Other than the one in the Combo.
What I want is All CastTiltes that Have YET to be Created
for the Production in the Combo

Let me explain what I mean by "created"...
CastTitles Consist of Cast1 thru Cast50
Productions Consist of Pro2004 Pro2005 Pro2006 Etc...

To create a CAST from existing CastTitles I have to assign
it to a production before I can begin adding Castmembers.

So In my List box I have All CastTitles Listed.
Once I have Selected a Production in the Combo & a CastTitle(s)
from the Listbox, I then click a button that Adds the selected
CastTitleID from the Listbox AND the ProductionID from the
Combo to the tblCasts.
So now tblCasts would have CastTitleID & ProductionID set.
Whatever these CastTiles that were selected, I now want them
removed from the ListBox.
So If Cast1 is assigned to a production of Pro2005,
when I select Pro2005 in the Combo I don't Want to see Cast1
anymore because it has already been created.

Hope i've been able to clear things up a little
Assuming that the list box is using a query as its Row Source, and that
query is using the combo box as a source of the value for a Criterion,
just
change the WHERE clause to be not equal to (or not like) the combo box's
value.
Click to expand...
 
Try this:

"SELECT tblCastTitles.CastTitle, tblCast.ProductionID
FROM tblCastTitles INNER JOIN tblCast ON
tblCastTitles.CastTitleID = tblCast.CastTitleID WHERE
(((tblCast.ProductionID) Not Like
[Forms]![frmCreate_Casts]![cboProductions].Value));"


--

Ken Snell
<MS ACCESS MVP>


Gerry said:
Thanks Ken,
What you suggest is what I wrongly assumed.
Here are the complete details.
Thanks again for any help.

This is the Query for the ComboBox
"SELECT tblProductions.PoductionID, tblProductions.Production
FROM tblProductions;"

This is the Query for the List Box
"SELECT tblCastTitles.CastTitle, tblCast.ProductionID
FROM tblCastTitles INNER JOIN tblCast ON
tblCastTitles.CastTitleID = tblCast.CastTitleID WHERE
(((tblCast.ProductionID) Not Like
[Forms]![frmCreate_Casts]![cboProductions].[BoundColumn]));"

With this query I get CastTitles for Productions that have been
created for Other than the one in the Combo.
What I want is All CastTiltes that Have YET to be Created
for the Production in the Combo

Let me explain what I mean by "created"...
CastTitles Consist of Cast1 thru Cast50
Productions Consist of Pro2004 Pro2005 Pro2006 Etc...

To create a CAST from existing CastTitles I have to assign
it to a production before I can begin adding Castmembers.

So In my List box I have All CastTitles Listed.
Once I have Selected a Production in the Combo & a CastTitle(s)
from the Listbox, I then click a button that Adds the selected
CastTitleID from the Listbox AND the ProductionID from the
Combo to the tblCasts.
So now tblCasts would have CastTitleID & ProductionID set.
Whatever these CastTiles that were selected, I now want them
removed from the ListBox.
So If Cast1 is assigned to a production of Pro2005,
when I select Pro2005 in the Combo I don't Want to see Cast1
anymore because it has already been created.

Hope i've been able to clear things up a little
Assuming that the list box is using a query as its Row Source, and that
query is using the combo box as a source of the value for a Criterion,
just
change the WHERE clause to be not equal to (or not like) the combo box's
value.
Click to expand...
If you post the list box's SQL statement here, I'm sure we can assist.
Click to expand...
Ken Snell
<MS ACCESS MVP>
Click to expand...
Click to expand...
 
Hi Ken,
I tried your suggestions & it gave me the same results.
But after a lot of trial & error I came up with the following
Query & subQuery.
It just seems a little complicated but it works.

1st Query(which becomes the subQuery)
"SELECT tblCastTitles.CastTitleID, tblCastTitles.CastTitle,
tblCast.ProductionID FROM tblCastTitles INNER JOIN tblCast
ON tblCastTitles.CastTitleID = tblCast.CastTitleID
WHERE (((tblCast.ProductionID)=
[Forms]![frmCreate_Casts]![cboProductions].[Value]));"

2nd Query
"SELECT tblCastTitles.CastTitleID, tblCastTitles.CastTitle,
qryCast_Production.ProductionID FROM tblCastTitles
LEFT JOIN qryCast_Production ON tblCastTitles.CastTitleID =
qryCast_Production.CastTitleID
WHERE (((qryCast_Production.ProductionID) Is Null));"

Complicated, but it works so i'm happy!

Thanks for your Help.
"SELECT tblCastTitles.CastTitle, tblCast.ProductionID
FROM tblCastTitles INNER JOIN tblCast ON
tblCastTitles.CastTitleID = tblCast.CastTitleID WHERE
(((tblCast.ProductionID) Not Like
[Forms]![frmCreate_Casts]![cboProductions].Value));"


--

Ken Snell
<MS ACCESS MVP>


Gerry said:
Thanks Ken,
What you suggest is what I wrongly assumed.
Here are the complete details.
Thanks again for any help.

This is the Query for the ComboBox
"SELECT tblProductions.PoductionID, tblProductions.Production
FROM tblProductions;"

This is the Query for the List Box
"SELECT tblCastTitles.CastTitle, tblCast.ProductionID
FROM tblCastTitles INNER JOIN tblCast ON
tblCastTitles.CastTitleID = tblCast.CastTitleID WHERE
(((tblCast.ProductionID) Not Like
[Forms]![frmCreate_Casts]![cboProductions].[BoundColumn]));"

With this query I get CastTitles for Productions that have been
created for Other than the one in the Combo.
What I want is All CastTiltes that Have YET to be Created
for the Production in the Combo

Let me explain what I mean by "created"...
CastTitles Consist of Cast1 thru Cast50
Productions Consist of Pro2004 Pro2005 Pro2006 Etc...

To create a CAST from existing CastTitles I have to assign
it to a production before I can begin adding Castmembers.

So In my List box I have All CastTitles Listed.
Once I have Selected a Production in the Combo & a CastTitle(s)
from the Listbox, I then click a button that Adds the selected
CastTitleID from the Listbox AND the ProductionID from the
Combo to the tblCasts.
So now tblCasts would have CastTitleID & ProductionID set.
Whatever these CastTiles that were selected, I now want them
removed from the ListBox.
So If Cast1 is assigned to a production of Pro2005,
when I select Pro2005 in the Combo I don't Want to see Cast1
anymore because it has already been created.

Hope i've been able to clear things up a little
Assuming that the list box is using a query as its Row Source, and that
query is using the combo box as a source of the value for a Criterion,
just
change the WHERE clause to be not equal to (or not like) the combo
box's
value.
Click to expand...
If you post the list box's SQL statement here, I'm sure we can assist.
Click to expand...
Ken Snell
<MS ACCESS MVP>
Click to expand...
Click to expand...
Click to expand...
 
Back
Top