complicated problem in sales subform

[ethan1701]

Hi,
I have a database for managing my inventory, sales, orders, etc.
For the sake of easy search, updating and reporting, I grouped my products into categories, via a Categories table, and each product had a Products_category_ID, which tells me in what category it is.
I created a form called sales, which has a sub-form named sales_details. In the subform, I list all the products sold in any particular order. I've created a drop down list (Combo box) of the products, but here's what I want to do:

I've created a categories drop-down, where I can select a category, and then only it's products will appear in the products drop down. I did this by writing the following in the products Row Source attribute:
SELECT products.Products_name, products.Products_id FROM products WHERE products.Products_categories_id=products_category ORDER BY products.Products_name;
My problem is that the categories drop down is only willing to hod one value, across multiple records. That is, if I select category A, it is selected for all the records, and I can't see products of other categories. This prevents me from updating the sale of products from several categories. Another problem with this is the fact that when I look at existing records in the form, I don't see the products in all the records, but only those that are in the selected category.
Can you please help me find a way to make this work?

thanks,
-Ethan

 

[willir]

Ethan
I understand and appreciate the need to categorize your data -- goes a long way in taming data.

... categories drop down is only willing to hod one value ... I can't see products of other categories

The issue seems to be that you are using the drop down combo box as a filter...

SELECT products.Products_name, products.Products_id FROM products WHERE products.Products_categories_id=products_category ORDER BY products.Products_name;

The bound combo box should use the ControlSource products_category in your Product table (although using the SELECT statement for the RecordSource is just fine).

You can use an unbound combo box to select categories - the Combo Box wizard should help you create this.

You can actually tell when a filter is applied - the filter icon is depressed when the filter is applied.

...To summerize
- Redesign your products_category combo box so it references the ControlSource and does not apply a filter. You may have to add this field to your Product table if it does not exist.
- Consider using an unbound combo box to select specific categories much the same way you do now.

Richard