I am going to try to explain this the best I can, but I apologize if it isn't clear.
I am creating a form in Access. This particular form has three combo boxes that pull information from three different tables.
The first combo box is called "cmbTO".
It pulls its information from a table titled "TaskOrders."
There are three fields that it shows. toID, toNo, and todesc.
The next box is called "cmbSTO."
It pulls its information from a table called "SubtaskOrders."
The fields in the SubtaskOrders table are stoID, toID, stoNo, and stoDesc
The combo box uses a query to filter the results in this box using the toID that is chosen in the first combo box, "cmbTO."
The last combo box is called "cmbStaff."
It gets its information from a table called "Staff"
The fields in the Staff table are staffID, Fname, and Lname.
This combo box is not filtered.
What I need to have happen is this:
I want to choose the task order in the first combo box, then I want to choose a subtask order in the next combo box, and finally choose a staff member in the staff combo box.
Then I want to click on "Save" (a button I added), and have the ID numbers from the items I chose in the combo boxes, from the three different tables, get added to a fourth table.
The fourth table only has three fields: toID, stoID, and StaffID. I need it to store the combination of choices made in the form.
I have tried using the Control Source property to do this, but it messes up they query that I have in the cmbsto combo box.
Is there SQL or VBA code I should be using in the "save" button to do this?
Am I making it too complicated?
Any help is greatly appreciated.
(And my apologies for how confusing this is)