Need to populate junction table from form, please help

[cereo]

Hi,
I am no programmer and my Access training has big holes in it but I will try to be clear.
I have attached a small part of the database I am building to keep track of employees and the training they have taken or need to take based on their job descriptions.
I created a form based on tbl_Employees with a subform from the junction table that manages the many-to-many relationship of Employees to Job Descriptions.
When the user selects a new Job Description to add for that employee from the combo box (Record source is from the third table, "Job Descriptions), I would like it to add the record to the junction table and update the results seen in the subform.
Can I write the code in the combo box's Control Source to have it update the junction table or would an "update" button work? Or is this not possible?
Thanks!

 

[Beetle]

I can't look at your db because I don't have A2007/2010 where I am, but to answer your question, the combo box should be bound to the Job Description foreign key field in the junction table, but it should retrieve (via the Row Source) the primary key field and description field from the Job Description table.

To clarify a bit more, let's say we have the following tables;

tblEmployees
EmployeeID (Primary Key)
FirstName
LastName

tblJobs
JobID (PK)
Description

tblEmployeeJobs (the junction table)
EmployeeID (Foreign Key to tblEmployees)
JobID (FK to tblJobs)

So if you wanted to display/edit which jobs are assigned to each employee then you would have a main form based on tblEmployees (or a query thereof) and a subform based on the junction table. In the subform you would have a combo box with the following properties;

Control Source: JobID (this is the FK field from the junction table)
Row Source: Select JobID, Description From tblJobs; (this will pull the needed fields from the Jobs table)
Bound Column: 1
Column Count: 2
Column Widths: 0", 2" (or whatever width works best for the second column)

When you make a selection in the combo the pk value from the Jobs table will be stored in the fk JobsID field in the junction table.

Conversely, if you wanted to show which employees were associated with a given job you would have a main form based on the jobs table with the subform still based on the junction table and the combo box bound to the EmployeeID field (in the junction table) and pulling from the Employee table.

Hope that makes sense.

 

[cereo]

I am awed by the time you took to work on this and how thorough your answer! Thank you, Beetle!
I have attached some screenshots that might help you see where I am going wrong.

You wrote
I can't look at your db because I don't have A2007/2010 where I am, but to answer your question, the combo box should be bound to the Job Description foreign key field in the junction table,
This is where my difficulty is. I don't know how to bind the combo box to the fk in the junction table. And if I can, will it create a record in the junction table for the employee selected in the main form?
Also, you can see the control source is empty because I do not know the syntax for the code to tell it, "put the selected JobDescription in the junction table" and have it create the new record.
Sorry if I seem thick, I told you my training had holes in it!
Thanks again!

 

[Beetle]

The problem is that the combo box for selecting the job description is on the main form, not the sub form. The text box currently in your sub form that displays the job description should be a combo box set up like I described earlier. When the user makes a selection in the combo box it will insert the appropriate value into that record in the junction table.

As far as how the EmployeeID gets inserted into the junction table, that is controlled by the Master/Child link property of the Subform Control. The Subform Control is the "window" that holds the sub form;

I have attached a sample application that you can so you can see how this is normally handled.

 

[cereo]

Thank you, Sean.
I'll give that a try.
Now I just need to learn how to "thank" you in this forum!!

 

[Beetle]

You're welcome. Just click Thanks button in the bottom right of the post if you want to than someone.