I am stymied. The problem probably has a simple fix, but nothing I can do (or find online) comes close to solution.
My DB has a basic many-to-many relationship. It correlates scenes and roles in a theatrical performance. Each scene has many roles, and most roles are in multiple scenes, so I have a many-to-many set of tables: tblRoles, tblScenes and a junction table lnkRolesScenes. Each of the "one" tables has just an ID (i.e., RoleID) and a description (RoleName). The junction table contains only the foreign key IDs, which constitute its composite key. There is a query (qryRolesScenes) that joins (correct word?) all three, and a form (frmRolesScenes, which turns out to be irrelevant to the problem) to address the query.
The problem: updating the junction table spontaneously adds new records to the other two tables. For example, if I enter a valid RoleID in lnkRolesScenes.RoleID, things work right. But if something is entered in the RoleName field, it creates a new record in tblRoles, with a new RoleID and the new data in in RoleName. My understanding is that this should not be possible. Clearly, my understanding is wrong.
Like I said, it's probably a simple fix -- but I can't find it anywhere.
Got any ideas?