I have a form that is based on a DAO Recordset. The recordset could contain any number of records as it is based on search parameters entered by the user previously in a separate form.

3 of the fields in the form can be changed, but not directly via the recordset (these 3 fields are not bound). Instead the user makes a change in field(s) then clicks 'save' button and a db.execute method is run to update the relevant record in the table as per below example code which updates the Name of the Analyst (there are three of these methods, one for each field that can be updated):

Dim db As Database
If newAnalyst <> Nz(Trim(Me.Recordset!Analyst), "") Then
	sqlStr = "UPDATE tblRptHdr SET Analyst = '" & newAnalyst & "' WHERE FileTrack = '" & currentFileTrack & "';"
	db.Execute sqlStr, dbFailOnError
End If
FileTrack is a primary key
FileTrack and Analyst are both text fields
currentFileTrack & newAnalyst are declared variables and the values are pulled from the current record displayed in the form
There is an error handler.
It is a split DB, tblRptHdr is a linked table

I have tested this repeatedly on a number of records without issue.

However, if I sort the order of the records (using the Sort options in the MS Access Home Toolbar) and then make changes, the update often fails (the first record displayed after sorting usually works, but once I move to later records, then the issue occurs).

By fail I mean the record does not get updated, but no error is thrown, the change simply does not happen.

Checks:
checked the variable names, field names, table name, UPDATE method to ensure no typos etc.
Added all to Watch window to ensure the data they hold is correct.
Outputted the sqlStr to Immediate window and ensured a valid SQL String is being executed.

I have tried two other methods for performing the update (Docmd.Runsql and Recordset.Edit), and both of these methods do successfully update the data, however the change does not reflect in the Form Recordset (unlike the db.Execute method which is immediate), so these are not suitable.

I am using Access for Office 365.

I would be grateful if anyone has any ideas how to correct this as I have no idea what I am doing wrong here.