Limit combo box to show only values not already selected

[0 1]

My database manages inventory for paint stores.

The mainform (frmStores) has two subforms. When the user selects a
paint brand in Subform A, a combo box on Subform B gets requeried and
shows only colors available for the selected brand.

I would like to further limit the combo box to show only colors (for
that brand) that have not already been added to the store's inventory
- i.e., the store/brand/color combination doesn't exist in
tblStoresBrandsColors.

tblStroresBrandsColors
------------------------
StoreID
BrandID
ColorID

The rowsource for the combo box is:

SELECT tblColors.ColorID, tblColors.ColorName, tblBrandsColors.BrandID
FROM tblColors
INNER JOIN tblBrandsColors
ON tblColors.ColorID = tblBrandsColors.ColorID
WHERE (((tblBrandsColors.BrandID)=[Forms]![frmStores]!
[fsubStoresBrands].[Form]![BrandID]));

Any idea how I can expand the WHERE clause to further limit the
values?

Thank you.

 

[John Spencer]

You could try adding a subquery based on tblStoresBrandsColors but you need a
way to limit the result to a specific store.

SELECT tblColors.ColorID, tblColors.ColorName, tblBrandsColors.BrandID
FROM (tblColors
INNER JOIN tblBrandsColors
ON tblColors.ColorID = tblBrandsColors.ColorID)
LEFT JOIN
(SELECT BrandID, ColorID
FROM tblStoresBrandsColors
WHERE StoreID = Forms![frmStores]![StoreID]) as AlreadyHas
ON tblBrandsColors.BrandID = AlreadyHas.BrandID
AND tblBrandsColors.ColorID = AlreadyHas.ColorID
WHERE (((tblBrandsColors.BrandID)=[Forms]![frmStores]!
[fsubStoresBrands].[Form]![BrandID]))
AND AlreadyHas.BrandID is Null;

Or you could use the following
SELECT tblColors.ColorID, tblColors.ColorName, tblBrandsColors.BrandID
FROM (tblColors
INNER JOIN tblBrandsColors
ON tblColors.ColorID = tblBrandsColors.ColorID)
WHERE (((tblBrandsColors.BrandID)=
[Forms]![frmStores]![fsubStoresBrands].[Form]![BrandID]))
AND NOT EXISTS
(SELECT *
FROM tblStoresBrandsColors as Temp
WHERE StoreID = Forms![frmStores]![StoreID])
AND Temp.BrandID = tblBrandsColors.BrandID
AND Temp.ColorID = tblBrandsColors.ColorID);

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[0 1]

I used the first example and it worked great. Thank you!