Filtering across Combo Boxes on a Form

[Boots6]

Hello,

I have a table for my inventory with fields 1_CAT, 2_CAT, 3_CAT, and 4_CAT. I have a DETAIL table that I am trying to make a datasheet Form from that has all of these categories. I want the user to select an item from the 1_CAT category and have it filter the records that show up in the 2_CAT combo box. I have the Row Source of my Subcategory box set to:

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

It works for the first one, but not the second. It keeps the first filter and I realize it's because it's not updating. I set a macro to Requery the control On Current. This works and filters properly, but after I select the S_CAT and move on to the next row, the data in the S_CAT disappears. It's getting stored in the DETAIL table, but it doesn't appear on the form. I want the user to be able to see what they have chosen.

Am I going about this the wrong way or is there a way to make the S_CAT "stick" after I've selected it by adding something to my SQL statement?

I realize my naming might be confusing. M_CAT and 1_CAT refer to the Main Category. 2_CAT and S_CAT refer to the subcategories. I did it that way so it would display how I wanted in my navigation pane.

I would greatly appreciate advice. Thanks!

 

[PGAC]

Carkick,

I don't think this can be done as a datasheet because if you requery a combo, all records having some other value than the previous combo will seem to disappear. You could probably do this on a single form which only displays one record at a time and requery all combos everytime you change record.

 

[Boots6]

Thanks for the response! I finally got it to work - 3 combo boxes, 2 needing requerying after update so it filtered down. I have it on a separate continuous form and got it to pick up the Main form's ID with a TempVar. They do not display like you said, but I pull it to a report right away so the users can see that the data does exist I might mess with text boxes and settings to see if I can get them to = the combo box's value and be visible. It's good to know I wasn't crazy trying to figure that out!

Thanks again.