WHERE Statement to Filter Records on Form

[Boots6]

I’m having trouble getting my form to filter based on my WHERE statement and I would really appreciate any help.

I have Tables 1_CAT and 2_CAT. 1_CAT stores the main inventory categories in the field M_CAT. 2_CAT contains the fields M_CAT (a look-up field to M_CAT in the 1_CAT table), S_CAT for subcategories and other relevant fieds. These 2 fields are in my DETAIL table and form.

On the DETAIL form, I want the user to select the main category from M_CAT and have it filter S_CAT to include only relevant records. The combo box on the form for M_CAT uses 1_CAT as the Row Source. For S_CAT, I put the Row Source to:

SELECT [2_CAT].ID, [2_CAT].S_CAT FROM 2_CAT WHERE ((Forms![DETAIL]![M_CAT]=[2_CAT]![M_CAT]));

This filter works, but only for the first record on the form. Subsequent records use the first record’s M_CAT to filter the S_CAT. What am I missing in my syntax?

Any help is appreciated!

 

[PHV]

For me, a combobox used as a navigation/filter tool shouldn't be bound.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Boots6]

I guess I'm not that good yet. I've gone through Access for Dummies and I've started on a SQL book, and I'm trying to incorporate the VBA that I know. A lot of my stuff isn't best practice because I'm doing it on the fly to meet my company's needs. Any suggestions or information for best practice on filtering and how bind tables together properly?

I'm working on an inventory database and the fields to describe the materials are: Main Category, Sub Category, Description, and Length. The inventory # of the items will be auto-numbered. The fields need to go in a datasheet form and filter down as the user selects each category so he can't screw it up basically. There are 10 main categories, 75 sub categories, and several versions combining the other 2 criteria.

I'd really appreciate any suggestions. Thanks!