Saving junction table record from subform

[RA99]

I am resetting my issue in the Forms forum (from Modules) in hopes I can get unstuck.

In my db, I have a ProgramsT table (pk ProgID), a ProgrammersT table (pk ProgmrID) and a junction table, ProgramRoleT (pk ProgRoleID). I have a form that presents a query of ProgramsT, with a subform to present current Program/Programmer relationships,bound as follows:

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;

That part seems to work OK. However, I want to give the user the option of selecting a new Programmer for the Program, who must be selected from amongst Members. I am trying to do this through a second subform bound to ProgramRolesT. The subform is set to data entry. It contains a combo box that draws MemberID from the table MembersT, passes it to a control called MemberID and calls a new form to create the new ProgrammerT record. My current code to do this is:

Private Sub Combo13_AfterUpdate()
Me.MemberID = Me!Combo13.Column(0)
DoCmd.Hourglass True
DoCmd.OpenForm "ProgrammerNewF2"
DoCmd.Close acForm, "ProgrammerNewF2", acSave
DoCmd.Hourglass False
End Sub

This works perfectly to create the new ProgrammersT record, but I have two problems - capturing the ProgmrID fk from the new ProgrammersT record, and writing the new ProgramRoleT junction table record to link the two. Capturing the new ProgmrID value should, I think, be doable in code, although I've not succeeded yet (I think problems with null values??) but the failure to save the new junction record looks to me like an events issue to my uninitiated brain. The blank subform shows (New) in the junction record pk field, but it does not populate the new pk value as the record is (supposedly) populated (see screenshot).

Can anyone make any suggestions?

[June7]

Options to 'capture' the ProgmrID and make it available to the junction table subform:

1. set a globally declared variable

2. set a TempVars variable

3. directly populate textbox on the junction subform

I am confused by which subform is for which table. The bottom-most form instructs to 'Select new programmer from membership'. If this is the junction table subform then shouldn't programmer be selected from ProgrammersT? What is the upper subform for?

[RA99]

The upper subform displays the programmer(s) currently attached to the program on the main form. The intent of the lower subform is to allow the user to attach a new programmer to that program. A new programmer must be selected from among current members, which is why the combo box is from MembersT. The programmers table is linked to the members table via MemberID as a foreign key.

[June7]

I am still confused.

If the upper subform displays the programmers attached to the program then that is the junction table form? But the lower subform also shows the ProgramID so that is also bound to junction table?

Do you want to provide db for analysis?

I don't understand why you need a separate programmers table. Why not use the members table as source for programmers. If only some members are programmers, then have another field in the table to identify those individuals. Use that field to filter combobox list.

[RA99]

It might be possible to use the members table for both, but membership and programmer status each have independent start and stop dates and other info specific to each, and the relationships looked to me to be getting too tangled, given that programs and programmers both have possible many-to-one relationships. Rather than confuse the issue more by trying to explain it, I should let you have a look, the main form in question is ProgramViewF.

[June7]

First, the combobox should probably offer members who are not already in ProgrammersT.

SELECT MemberActiveQ.MemberID, MemberActiveQ.LastName, MemberActiveQ.FirstName, MemberActiveQ.MiddleName
FROM ProgrammersT RIGHT JOIN MemberActiveQ ON ProgrammersT.MemberID = MemberActiveQ.MemberID
WHERE (((ProgrammersT.MemberID) Is Null))
ORDER BY MemberActiveQ.LastName, MemberActiveQ.FirstName, MemberActiveQ.MiddleName;

Why do you need the MemberID textbox? Just reference the combobox. Pass new ProgmrID back to the subform.
Private Sub Form_Current()
If IsNull(Me!MemberID) Then Me!MemberID = Forms!ProgramViewF!ProgramViewSub2.Combo13
If IsNull(Me!ProgmrActive) Then Me!ProgmrActive = "Active"
Forms!ProgramViewF!ProgramViewSub2.Form!ProgmrID = Me.ProgmrID
End Sub

Save the record and requery the subforms and the combobox after the programmer form closes.

Private Sub Combo13_AfterUpdate()
If Not IsNull(Me.Combo13) Then
DoCmd.OpenForm "ProgrammerNewF2"
DoCmd.Close acForm, "ProgrammerNewF2", acSaveNo
DoCmd.RunCommand acCmdSaveRecord
Parent.ProgramViewSubF.Requery
Me.Requery
Me.Combo13.Requery
Me.Combo13 = Null
End If
End Sub


Have you run Debug > Compile? There's a lot of code errors.

[RA99]

Wow, does that ever work, and thanks. You also revealed a fairly obvious flaw in my process logic - the user not only has to be able to assign a member as a new programmer, he/she also has to be able to assign a current or past programmer (i.e. someone already in ProgrammersT) to a program. So, I set up (yet) another subform bound to the junction table with a combo box to pick up ProgmrID from a query of ProgrammersT. Without the need to create a new ProgrammersT record, the code needed is simpler than above. Adapting your code:

Private Sub Combo21_AfterUpdate()
Me.ProgmrID = Me!Combo21.Column(0)
If Not IsNull(Me.Combo21) Then
 Me.Requery
 Parent.ProgramViewSubF.Requery
 Me.Combo21.Requery
 Me.Combo21 = Null
End If
End Sub

This, I am happy to say, seems to work for me! Thanks again.