Unanswered: How to build a form w/ 2 unrelated queries?
I'm trying to build a form in Access and I'm getting very confused.
I have 2 queries: "Jobs" and "Workers", and a table of "Assignments". I want to build an interface so that for a given Job, the user can look at different workers and assign them to the jobs. Once a worker has been selected for a job, a row should be added to the "Assignments" table linking the worker to that job. A job can have more than one worker and vice versa.
I think I need to set this up so that my main form has several subforms: "Jobs", "Workers", and "Assignments". For whichever job is selected in "Jobs", "Assignments" should list whichever workers have been assigned to that job. There will be an "Assign Worker" button below "Workers", and an "Unassign Worker" button below "Assignments" that will remove a worker from the assignment list.
I understand what's needed for the queries and VBA code behind the controls--I know how to have clicks and edits trigger events, because I've built a lot of Userforms in Excel. But I don't understand Access forms that well, so I'm not sure how to get started on this.
I solved this. I'm using a form for Jobs with a subform for Assignments and a subform for Workers. The subform for Workers is a join of some other tables that will let me measure the "fitness" of all workers for the job currently populated on the form. There is a button for populating the Assignments table using the current job and whatever Worker is currently selected.