Unanswered: Combo Box in a form which alters a junction table
I thought I had this problem licked. Nevertheless, as noted in a previous post, I have a db with tables for Faculty Members, Expertises, and a junction table between the two which is comprised of a FacultyID and ExpertiseID column which relate to the same columns in Faculty Members and Expertises. Thus, faculty members may be listed by a specific expertise or all of a faculty member's expertises may be listed.
To facilitate data input, I am attempting to create a form which displays a Faculty Members record with a subform listing their expertises in a datasheet. This subform needs to have new entries put in it via a combo box which contains all of the expertises. Thus, the user can select an expertise from the dropdown and create an entry in the junction table correlating a FacultyID with an ExpertiseID.
I have created such a table which allows me to put new entries into the junction table via the coded ID numbers (i.e., I can type 7 in the datasheet under expertiseID in the record for the Faculty Member whose FacultyID is 4 and an entry in the junction table for 4, 7 is created. However, I cannot get a combo box, where the user selects the Expertise by name (Chemistry of Wood instead of 7, for example) and have it update the junction table with 4, 7, to work. How can I accomplish this?
Let me know if any additional illustration is necessary.