I have a feeling this is either going to be too complicated to do or it's so ridiculously simple to do that I'm just missing it. So any help you can offer would be greatly appreciated! Here's what I've got:
I'm creating a database to help manage tools used at a given workstation. I've got a few dozen workstations, each with different configurations (for example mechanical assembly, electrical assembly, and product support). Each workstation would require different tools based on its configuration. For example, a mechanical assembly workstation would require tools like torque wrenches while an electrical assembly workstation would require soldering irons.
I have a table set up for each workstation with it's ID number, the employee ID of the person working at that station, they type of workstation it's set up to be, and the location of the workstation. I have another table set up to list each tool required at each type of workstation (two fields: "jctTypes" for the type of workstation and "jctToolTypes" for a generic description of the tool required, i.e. "Soldering Iron", "Torque Wrench", "Digital Multi-Meter", etc.). I have a third table set up with a list of tools available with a number of different fields including Tool ID, Manufacturer, Model, Tool Type (the same generic description used in "jctToolTypes" mentioned before), and a few measurements to help determine ESD classification. Finally, I have a fourth table set up to be a junction table (three fields: "jctBenchID" for the workstation ID, "jctToolID" for the tool ID number, and "txtToolStatus" indicating whether the tool has been ordered, installed, or is out for calibration).
My question is this: How would I create a form that could help combine all of this information? I'd like to be able to choose a workstation ID number from a combobox, have it list all of the generic tool descriptions required for that workstation based on the workstation type, and allow me to choose a tool from each category (that is, a list of all available torque wrenches for the "Torque Wrench" category or a list of all available DMMs from the "Digital Multi-Meter" category).
I'm pretty proficient in VBA, so I won't shy away from some code and I'm very open if there's a more efficient way to organize my data assuming it will help me accomplish this task.
Thanks!