Select "All" from one combo box show all records in other combo box.

[hasanrazaj1]

Hey All Dears.

i am wondering for several hours to reading threads and searching about this problem but i can't fix it till yet.

I have a form1 containing two combo boxes. cboCategory and cboProduct.
both combo boxes are unbound.

cboCategory RowSource property is as under:
SELECT Categories.CategoryID, Categories.CategoryName FROM Categories;

and after update event of cboCategory requery the second cboProduct.

while cboProduct RowSource Property is as under:
SELECT Products.ProductID, Products.CategoryID, Products.ProductName
FROM Products
WHERE (((Products.CategoryID)=[Forms]![Form1]![Combo0]))
ORDER BY Products.ProductName;

both work correct. now i want to add word "All" in first cboCategory and when i select "All" beside any Category name then it populate the second combo box cboProduct with All Records.

can it is possible.

i try to use a union query to add "All" in first combo box and it works good. but when i select "All" from first combo box it not update the second cbobox and not show all records which are not limit by categoryID.

any ideas about this......

 

[pbaldy]

One way would be to use this

http://www.mvps.org/access/forms/frm0043.htm

and this

http://www.mvps.org/access/queries/qry0001.htm

 

[DCrake]

Why do you want your dependant combo box contain all the products? this may lead to losses in referetial integrity if the combos do not cascade correctly. As product codes are unique to their parent category you could end up with mismatches.

 

[hasanrazaj1]

thank guys i get the solution which is that:

the second combo box "cboProduct" RowSource will as under:


SELECT Products.ProductID, Products.CategoryID, Products.ProductName
FROM Products
WHERE (((Products.CategoryID)=[Forms]![Form1]![cboCategory])) OR ((([Forms]![Form1]![cboCategory]) Is Null))
ORDER BY Products.ProductName;

 

[pbaldy]

Happy to help.

 

[themurph2000]

One way would be to use this

http://www.mvps.org/access/forms/frm0043.htm

and this

http://www.mvps.org/access/queries/qry0001.htm

Wish I had found this thread sooner. Would have saved myself two hours of aggravation.

Thanks, Paul.

 

[pbaldy]

No problem Patrick.