db.Execute Update method fails without errors

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):

Code:

---

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.

If Recordset.Edit works, doesn't a simple requery update the form records?
Is there some reason that you do not allow the user to directly update the data in the Form's fields?

Depending on the search criteria for the recordset and what changes were made, the record might not be included in the search results after the requery.

Any changes were immediate, it would be preferable to give the user the opportunity to not save the changes if they make a mistake.

We were also experiencing issues with the backend suddenly in "inconsistent state", this was always occurring immediately after updating a record, sometimes Access was not able to repair it. I wasn't sure if multiple users having same records open/editable in a recordset was part of the issue, so changed it to read-only and added the db.execute method for updating.

OK, how about using a VBA Recordset to update the table using
.Edit
to do so, you can use a msgbox to ensure that the data is correct and then use the VBA to enter it direcyly in to the table.

The issue with using the Recordset.edit method is that while the record is correctly updated in the table, the record does not update in the Form recordset (where as the db.Execute method updates in the Form recordset immediately...when it works), its the same for Docmd.runsql

And I want to avoid requery of the form recordset if possible as it could change the records available to the user.

Well, you could it in reverse, update the form real fields using VBA which will automatically update the table, won't it?
It is very simple to update the current record fields, which can be on the form but in invisible mode.