Filtering a subform based on a combo box value in the main form.

[jcarpenter22]

Hello all,
After reading quite a few solutions that seemed to be the fix i needed i have been unable to solve my own problem. Hopefully by attaching my db to this thread someone smarter than myself can lead me to the right road. I'm starting to think maybe my table structure is incorrect.

I have frm_workorder with a subform_workorder_detail which is linked parent/child with WorkorderID.
In frm_workorder i have a field VariantID (it is from the tbl_vehicle_Issue) and is autopopulated when the VehicleID is selected to start a new Workorder.
The subform_workorder_detail has a field RetrofitKitID which i am trying to filter based on the VariantID once the VehicleID is selected in the main form. When i use the drop down in the RetrofitKitID i can see all kits available and can see the variants, but i only want to see the specific variants applicable to that variant identified in the main form.

I surely would appreciate someone taking a look and see what i have not been able to figure out. Thanks

 

[Lilliabeth]

In my opinion, your database is made difficult to work with because you use lookup lists in tables. It's like being blindfolded half the time. Get rid of the lookup lists in your tables and create relationships in the Relationship window. Lookup fields in tables are the creation of the Evil One.

http://www.mvps.org/access/tencommandments.htm

I should have been able to reference column 1 of the VariantID combobox, but the lookup list code obscures that. So I made an invisible textbox in the main form, named it txtVariantID, and set the Control Source to VariantID.
Then in the subform, set the combobox to something like this:
SELECT [tbl_Retrofit_Kit.RetroKitID], [tbl_Retrofit_Kit.VariantID], [tbl_Retrofit_Kit.Retrofit_Kit] FROM tbl_Retrofit_Kit WHERE [tbl_Retrofit_Kit.Variantid]=[Forms]![frm_Workorder]![txtVariantID] ORDER BY [Retrofit_Kit];

--Lilliabeth