4 cascading combo boxes and an <All>???

[dan1dyoung]

i currently have 4 combo boxes that each has its own table, and setup as cascading. Now what i want to add is an <All> in each combo to box to give any results in that table.

i have

combo 1
SELECT [tblProductManufacturer].[ProductManufacturerID], [tblProductManufacturer].[ProductManufacturer]
FROM tblProductManufacturer UNION Select "<All>", "<All>" as Bogus From tblProductManufacturer
ORDER BY [tblProductManufacturer].[ProductManufacturer];


combo 2
SELECT tblProductModel.ProductModel, tblProductModel.ProductModelID
FROM tblProductModel
WHERE (((tblProductModel.ProductManufacturerID)=[Forms]![MainScreen]![MainWindow].[Form].[CboProductManufacturer])) UNION Select "<All>", "<All>" as Bogus From tblProductModel
ORDER BY tblProductModel.ProductModelID;


combo 3
SELECT [tblProductVersion].[ProductVersion], [tblProductVersion].[ProductVersionID]
FROM tblProductVersion
WHERE ((([tblProductVersion].[ProductModelID])=[Forms]![MainScreen]![MainWindow].Form.CboProductModel)) UNION Select "<All>", "<All>" as Bogus From tblProductVersion
ORDER BY [tblProductVersion].[ProductVersionID];


combo 4
SELECT [tblProductSize].[ProductSize], [tblProductSize].[ProductSizeID]
FROM tblProductSize
WHERE ((([tblProductSize].[ProductVersionID])=[Forms]![MainScreen]![MainWindow].Form.CboProductVersion)) UNION Select "<All>", "<All>" as Bogus From tblProductSize
ORDER BY [tblProductSize].[ProductSizeID];


in place of UNION Select "<All>", "<All>" i have also tried

UNION Select Null as AllChoice, "(All)" as Bogus


And in place of UNION Select "<All>", "<All>" as Bogus i have also tried

UNION select distinct null, null (And added distinct after the first Select of the query)

but to no avail????

 

[dan1dyoung]

no luck

thanks for your reply unclu gizmo,

but i forgot to put that i tried that aswell and all that gave me was The* in the next combo instead of the <All> and it still did not show all products

thanks

dan

 

[dan1dyoung]

Still Trying

Uncle Gizmo,

I will try to reduce the size to post but for now i have also included the tables, e.t.c incase you can see anything obvious. I have a few more things to try but if you have a working example i would realy appriciate if you could attach it and i can compare it to what i have done to see where i have gone wrong

Tables:

tblProductManufacturer

ProductManufacturerID - PK - Autonumber
ProductManufacturer - Text



tblProductModel

ProductModelID - PK - Autonumber
ProductManufacturerID - Number
ProductModel - Text



tblProductVersion

ProductVersionID - PK - Autonumber
ProductModelID - Number
ProductVersion - Text



tblProductSize
ProductSizeID - PK - Autonumber
ProductVersionID - Number
ProductSize - Text



Combo Queries:

CboProductManufacturer

SELECT tblProductManufacturer.ProductManufacturerID, tblProductManufacturer.ProductManufacturer
FROM tblProductManufacturer UNION Select "*", "<All>" as Bogus FROM tblProductManufacturer
ORDER BY tblProductManufacturer.ProductManufacturer;




CboProductModel

SELECT tblProductModel.ProductModel, tblProductModel.ProductModelID
FROM tblProductModel
WHERE (((tblProductModel.ProductManufacturerID)=[Forms].[MainScreen]![MainWindow].[Form].[CboProductManufacturer])) UNION Select "*", "<All>" as Bogus FROM tblProductModel
ORDER BY tblProductModel.ProductModelID;




CboProductVersion

SELECT [tblProductVersion].[ProductVersion], [tblProductVersion].[ProductVersionID]
FROM tblProductVersion
WHERE ((([tblProductVersion].[ProductModelID])=[Forms]![MainScreen]![MainWindow].Form.CboProductModel)) UNION Select "*", "<All>" as Bogus From tblProductVersion
ORDER BY [tblProductVersion].[ProductVersionID];



CboProductSize

SELECT [tblProductSize].[ProductSize], [tblProductSize].[ProductSizeID]
FROM tblProductSize
WHERE ((([tblProductSize].[ProductVersionID])=[Forms]![MainScreen]![MainWindow].Form.CboProductVersion)) UNION Select "*", "<All>" as Bogus From tblProductSize
ORDER BY [tblProductSize].[ProductSizeID];



Relationships:

[tblProductSize].[ProductVersionID] To [tblProductVersion].[ProductVersionID]


[tblProductVersion].[ProductModelID] To [tblProductModel].[ProductModelID]


[tblProductModel].[ProductManufacturerID] To [tblProductManufacturer].[ProductManufacturerID]

 

[dan1dyoung]

Part Solved

I changed as below and it works OK now, now on to other challanges!!!

Thanks all for your help and ideas, i'm sure i will be back soon!!

Before:

SELECT tblProductModel.ProductModel, tblProductModel.ProductModelID
FROM tblProductModel
WHERE (((tblProductModel.ProductManufacturerID)=[Forms].[MainScreen]![MainWindow].[Form].[CboProductManufacturer])) UNION Select "*", "<All>" as Bogus FROM tblProductModel
ORDER BY tblProductModel.ProductModelID;

After:

SELECT tblProductModel.ProductModelID, tblProductModel.ProductModel
FROM tblProductModel
WHERE (((tblProductModel.ProductManufacturerID) Like [Forms].[MainScreen]![MainWindow].[Form].[CboProductManufacturer])) UNION Select "*", "<All>" as Bogus FROM tblProductModel
ORDER BY tblProductModel.ProductModel;