HELP! List Box? Combo Box? What do I do??

[AlisonG]

Hello
I have a problem that has been driving me nuts for over two weeks now. Basically, I have created a Form called Purchase Orders. This is made up of a normal form (displaying details such as PO No, Date, Name of Supplier, Address of Supplier etc) which works perfectly well. There is then a sub form which displays details of the products being ordered ie Product Category, Product Name, Product Code, Price and Quantity. This sub form come from a table called ‘Product List for PO Form’, which in turn is fed from another table called ‘Product List’.
At the moment, I have drop down boxes for category and product name but this is getting quite confusing as there are in excess of 300 products and the user has to scroll through all those products to find what they are looking for. What I would like to do is once the category has been selected that the product field only show the products relating to that category (ie if ‘stationery’ has been selected then only stationery products are displayed in the product box). Does anyone have any idea how to do this? BTW, I’m working in Access 2007 and I’m very new to this!
Any help or suggestions would be very much appreciated as I am literally pulling my hair out! Thank you!

 

[missinglinq]

The concept is called "cascading combobxes." Use this term to search here or on Google. The exact same thing applies to listboxes and the code is interchangeable. Here is one link

http://bytes.com/forum/thread605958.html

 

[AlisonG]

Thank you very much for your suggestions. Unfortunately it doesn't work in my case as i have too many categories and my Access knowledge is so very limited. I think it best to just give up for now and not to try and make the database too complicated. Thank you for all your help though

 

[AlisonG]

Since my last post, I've asked my brother for some help. He doesn't know how to use Access very well but does know about MySQL and so he wrote the following code to go in the Row Source of the Product box in the sub form:

SELECT [TBL Products List].Product, [TBL Products List].Code, [TBL Products List].Price FROM [TBL Products List] WHERE ((([TBL Products List].Category)=[FRM Purchase Order].[Category])) OR (([TBL Products List].[Category]=Null)) ORDER BY [TBL Products List].Product;

This only works on the first PO when you go from design view to form view. It also affects the Product list drop down for all other Purchase Orders, which is a nightmare. He thinkgs there is a slight error in the code above. Can anyone spot it please?? Any help would be very much apprecited!