Just tested your set up.
"In the subform, I select a **new** position from the the "Billet Name" combo. Let's say I select "Financial Manager".
- This creates a new record in the OBS table and (temporarily) associates staff member "Vacant" to this position."
No, it does not create a record in the OBS junction table--at least not just when you select Financial Manager.
The Financial Manager is identified as record 27 on your form, but only 24,25 and 26 are in the table -so 27 has not been added yet, and even after I selected Robert Redforb (but haven't moved off that record) there is no 27 in OBS.
As soon as I move to another record on the subform, then OBS is updated with the 27 records info.
I tested with this code ---Note the Purple line and it seems to adjust the Staff Members available list box
--but I am not familiar with the details of your business or code.
Code:
Private Sub cbo_lastName_AfterUpdate()
'Updates date/time stamp when staff member is added
On Error GoTo cbo_lastName_AfterUpdate_Error
Me.txt_dateTimeModified = Now()
Forms![F10_StaffMemberToOffice].Requery 'jed
On Error GoTo 0
Exit Sub
cbo_lastName_AfterUpdate_Error:
'Throws error message
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cbo_lastName_AfterUpdate, line " & Erl & "."
End Sub