BaldyWeb
Remain on a Selected Record After Requerying Form
Sometimes you need to requery a form to refresh the data it contains, but you want to remain on the record you're currently on rather than having focus go to the first record, which is the normal behavior after a requery. One method involves using a bookmark. The guts of the code:
Dim lngPK As Long 'change data
type if yours is different
'set variable to current record ID
lngPK = Me.EmpID
Me.Requery
'return form to original record
With Me.RecordsetClone
.FindFirst "EmpID = " & lngPK
'if your key field is text use this
line instead
'.FindFirst "EmpID = " & Chr(34) & lngPK & Chr(34)
If .NoMatch Then 'just in case another user deleted
it in the interim
MsgBox "Record not found!", vbCritical
Else 'go to that record
Me.Bookmark = .Bookmark
End If
End With
Thanks to Tom van Stiphout for his improvements to my earlier method!
And a sample to show the process in action: