Thanks for that. I can't (I don't think) use the NotInList facility as I can't think of a way to restrict the user's choices to current Members. So, I'm still working my way through my problem.
So far, I have my main form pointing to a query of Programs, and a subform bound to the ProgramRoles junction table. The SQL for the subform actually looks like this:
Code:
SELECT ProgramRoleT.*, ProgrammersT.MemberID, MembersT.MasterID, ContactMasterT.LastName, ContactMasterT.FirstName, ContactMasterT.MiddleName
FROM ((ContactMasterT INNER JOIN MembersT ON ContactMasterT.MasterID = MembersT.MasterID) INNER JOIN ProgrammersT ON MembersT.MemberID = ProgrammersT.MemberID) INNER JOIN ProgramRoleT ON ProgrammersT.ProgmrID = ProgramRoleT.ProgmrID;
The complexity in that comes from the need to see identifying fields from tables other than the junction table.
A combo box on the footer of the subform accesses records from the Members table, and the following code is attached to the AfterUpdate event of the combo box:
Code:
Private Sub Combo44_AfterUpdate()
DoCmd.Hourglass True
DoCmd.OpenForm "ProgrammerNewF2"
Me.[MemberID] = Me![Combo44].Column(0)
DoCmd.Close acForm, "ProgrammerNewF2", acSave
DoCmd.Hourglass False
End Sub
The ProgrammerNew form in this code is bound to the Programmers table, and contains controls for ProgrammerID (pk), MemberID (fk) and an active flag (text). The form is set to Data Entry.
This setup appears to populate the subform with a new programmer associated with the correct Program. However, it fails to save the change, indicating that I am trying to introduce a duplicate to a primary key field or indexed.
I've checked my three tables (Programs, Programmers, ProgramRoles) to ensure only the primary keys are indexed. I've tried populating the ProgrammerNew form manually, and that form appears to do what was intended, i.e. it creates a new Programmer record with a new ProgrammerID, and this entry appears on the main form/subform where I would expect it. However, it will not let me save from the ProgrammerNew form or the main/subform combination. The error message to a manual save says that the changes "were not successful because they would create duplicate values in the index, primary key or relationship." The message from the AfterUpdate code is similar.