Trouble saving record on subform - Many to Many type relationship.

[Travb81]

I have an Access Front End, database is MariaDB. Interacting by Linked Tables.

Basic structure:
tblPeople
ID
FirstName
Surname
Phone
tblCompanies
ID
Name
tblPeopleCompanyLink
ID
PeopleID
CompanyID

I have a Companies Form.
On that, I have a People subform, with the recordsource as a query: tblPeopleCompanyLink, left join on tblPeople.
The People subform has a combobox, to select an ID from tblPeople. Works perfectly for selecting a record already in tblPeople.



If the combobox NotInList fires, I have this code to add a new record in tblPeople:

    Me.Undo
    
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblPeople", dbOpenDynaset, dbSeeChanges)
    rst.AddNew
    
    rst!FirstName = NewData
    rst.Update
    rst.Close
    Response = acDataErrAdded
    Me.Dirty = False

The problem...
I try to enter some additional information such as [Phone], and when leaving the field, I get error "Field cannot be updated".
I can't edit fields, until I click another record - and then go back to the new one I just created.
I'm assuming I need to force a save / requery the data source somehow?



Help greatly appreciated!

I've played around with requery, bookmark's, docmd.runcmd accmdsaverecord - I'm sure i'm making this harder than it needs to be!

[Travb81]

To simplify this even more.... it's not related to the ComboBox NotInList code.
If I use the ComboBox to select a PersonID - it will show me their name, phone number. I can edit these, but if I leave the record, I get the "Field cannot be updated" error.

How can I select a PersonID, and have the record save/requery to allow me to edit these values immediately?

[June7]

You should not include tblPeople in the subform except to display its data, not edit. And it is not even necessary to include the table just to display people info. Expressions in textboxes can reference columns of combobox. Edits to tblPeople should be done in another form. If you want to add/edit people 'on the fly' during data entry, open a pop up form. Review https://blueclawdatabase.com/notinlist-event-code/