Moving to a Record after the Key has been changed

[arees]

I am using Access linked to SQL tables and have found major performance
issues using the DoCmd.FindFirst so I would like another technique to quickly
move a form to a record after the key has been changed.

The scenario is that I am using code to prompt a user who want to change the
record's key field (i.e., ID). I then use code to open a recordset and then
change the ID. Since the record shown on the form is no longer valid, I use
Me.Requery to update the form based on the changes. But how do I tell the
form to move to the new ID record following the Requery method?

(I have tried bookmarking both the Recordset and Form, and using
rst.AbsolutePosition with DoCmd.GotoRecord..., but these have not worked).

 

[Klatuu]

Changing primary key fields is pretty scary stuff. But, here is a routine
for requerying a form and staying on the same record:

Dim lngKey as Long

lngKey = Me.txtKeyControl 'Save the key field of the current record
Me.Requery
With Me.RecordsetClone
.FindFirst "[ID] " = lngKey
If Not .NoMatch Then
Me.BookMark = .Bookmark
End If
End With