Query by Form to Filter Products to Order

[TSimonick]

Hi,

I'm using Northwind.mdb as the basis for creating a simple supply order. I want to restrict the products that are available to order in the order subform based on a particular supplier in the main form. Ideally, I also want to restict based on the product category. "Mastering Access 2002" shows a Query by Form example that should work. I created a unbound combo box on the main form and it does work to restrict the products shown in the filter query. But when I put it together with the "apply filter" macro as a "After Update" event, it doesn't filter the products available on the subform.
Any ideas on what I'm doing wrong? Is the filtering technique the wrong way to go?
Thanks!

 

[WildHare]

FYI - not really sure how you're implementing this, but remember that only ONE filter at a time can be applied against a datasource, but it CAN be a compound filter:

Me.Filter = "State = 'CA' and City = 'Pacoima'" is fine, but
Me.Filter = "State = 'CA' will be overridden by
me.Filter = "City = 'Pacoima'"

(of course, since there's probably no other state in the world with a city named "Pacoima", this may be a bad example..but you get the idea????

Can you post your "after_update" event code here, perhaps we can get it working for you...

Jim


There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[TSimonick]

Jim,

Thanks for your interest, I should have supplied more details. I hadn't tried to create both filters, just a category filter to start with, get that working and then try for the second filter for Supplier:

Tables: Category, Suppliers, Products, OrderDetails, Orders each with a primary key, except OrderDetails which has a key of ProductID and OrderID.

Forms: Order (main form) with an Orders Subform that is used to choose the products to order. The two forms are linked by OrderID. The record source for the subform was a query of OrderDetails and Products which included one expression(standard Northwind design).

New Design: I put the combo box (cboCategory)on the main form to select the Category. The new record source for the Products subform is a filter query that has the Category, Products, OrderDetails tables. I used the primary key CategoryID with the criteria of [Forms]![Orders]![cboCategory]. I set the "Output All Fields" property to Yes.
If I select a category in cboCategory, leave the form open, then select the filter query and run it, I only see the products that belong to that category. So that part works!!

Next, I created a one line macro that has the action ApplyFilter (the filter query) which is called from the AfterUpdate event of cboCategory. I thought that should do it, but when I open the Order form, select a category and then go into the subform, all of the products in all categories are available.

At this point, the only change I have made to the subform is to change the record source from OrderDetails query to the filter query. I didn't change any of the fields in the subform.

The subform does have one macro attached to the On Current event. The macro performs OpenForm where the condition is: IsLoaded("frmViewProduct&quotand the Where condition is: [ProductID]=[Forms]![Orders]![Orders Subform]![ProductID]. The comments for the VB IsLoaded function state that it determines if the Orders Subform is opened as a stand-alone form and what to do, which apparently is to open frmViewProduct?
When I look at frmViewProduct the record source is a query of the Suppliers and Products tables and has the Supplier name, plus the fields from Products.

I deleted this macro from the On Current event, but it didn't help with my problem. Any ideas what I should try next?

Thank you!

-Tom