Returning to the previous record after addnew

[Guest]

I have a button that copies the data from a number of tables and creates a
new record.

I want to go back to the original record after a requery, which re-sorts my
form.

Initally I used the current record but that did not always work:

Dim CurrentRecordNo As Long

CurrentRecordNo = Me.CurrentRecord

Call CopyTables

Me.Requery
'DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, CurrentRecordNo

Next I tried using a bookmark but that did not work either:

Dim strBook As String

strBook = Me.Bookmark

Call CopyTables

Me.Requery
Me.Bookmark = strBook

Any help would be appreciated.

 

[Allen Browne]

The Bookmark is the way to go, but (as you found) it does not survive the
Requery. Therefore you need to save the primary key value, and FindFirst
after the requrey.

This kind of thing:
Dim rs As DAO.Recordset
Dim varID As Variant
varID = Me.[MyID]
'other stuff here
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "MyID = " & varID
If rs.NoMatch Then
MsgBox "not found"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

If MyID is a Text field (not a Number type), you need extra quotes:
rs.FindFirst "MyID = """ & varID & """"
Explanation of the quotes:
http://allenbrowne.com/casu-17.html

An alternative approach that does not require a requery is to AddNew to the
RecordsetClone of the form.