You might be able to Requery the subform, and then go to the last record.
But if the table does not have an AutoNumber, or if the AutoNumber is not
sequential, or if other users are adding records, this might not take you to
to desired one.

Would you consider an AddNew on the RecordsetClone of the subform instead of
running the update query? That avoids the Requery, and you know for certain
that you are ending up with the correct record.

The alternative would be to Execute the query statement instead of RunSQL,
and ask Access for the new identity value, requery the subform, and then
FindFirst in its RecordsetClone. That's a long way round for a shortcut, and
works in JET 4 only (Access 2000 and later.)

Jumping back to the first record after setting the form bookmark is not the
normal behavior, so something else must be going on as well.

It could be a Requery, or it might be related to something that happens in
the main form. Access reloads the subform when the main form moves record
(or is requeried.)

If you OpenRecordset on the table and add the record there, the new record
will not be in the form's recordset until a requery. You will then need to
FindFirst in the RecrodsetClone of the of the form, i.e. you cannot match
the Bookmark of the table's recordset against that of the form.