Filter in Subform

[Paul Stan via AccessMonster.com]

Hi,
I am trying to make folowing thing:
I have main form "Purchasing Order" and subform "Purchase Order Details".
In the subform I want to filter products by category. I have table named
"Products" and other one "Categories". Information from "Purchase Order
Details" is stored in table named "Transaction Detail".
The idea is: when the user enter purchase order details in the subform to
be able to choose products from "Products" table, filtered by CategoryID in
the same detail row.
I tried to do this with query but it works partly: when I go to the next
row and choose different CategoryID the product on the previous row
disappears. I guess, the problem is that I cant use the control from the
form directly to the Select statement.

Thank you for consideration.
PS

 

[Allen Browne]

Access loads only one RecordSource for the combo, not one for every row of
your continuous form/datasheet.

That means it cannot show the values on other rows if both:
- you have filtered it so that not all values are present, and
- the bound column of the combo is zero-width.

One workaround is to display the bound column. Displaying the ProductID is
not very pretty if you've used an autonumber, but works fine if you use a
natural key (a unique name for the product).

 

[Paul Stan via AccessMonster.com]

Thank you for the advice.

It is not very convenient don't have this future. When you have long
databse with different products you need to filter them somehow. Any way
I'll try to go around this.