I've managed to stump all of the Access users/designers at my company but we all agree that there should be a simple solution to our problem. Here goes:
Our database tracks the fabrication process in our manufacturing firm. The manufacture of parts (Serials) is tracked using a Work Order. The database includes a Work Order Form (frmFabrication, based on tblFabrication) and a subform where you list the serial numbers for that work order. Here's a summary of the relationships:
tblFabrication
Work_Order_Number (PK)
tblWorkOrder_Serials
Work_Order_Number (FK)
Serial_Bar_Code (FK)
tblSerial_Bar_Codes
Serial_Bar_Code (PK)
Drawing_Number (FK)
Part_Location
tblDesign
Drawing_Number (PK)
Drawing Name
All of this information is compiled into a Work Order report (rptWorkOrder) based on qryWorkOrder. This query prompts the user to enter a drawing number and then pulls up all of the work orders associated with that drawing number. This works fine. However, we would like to be able to filter the forms based on the same principle: by the drawing number.
The only other hitch to this situation is that, in addition to parts (Serials), we also track assemblies in the same fashion but with a different subform. I've attached a copy of the database so you can see this all in action. I'll warn you ahead of time that qryWorkOrder is incredibly complex--any suggestions as to how to simplify it would be greatly appreciated (I was not the original designer, so I won't be offended, haha).