Bookmark says 'No Current Record'

[SarahG]

Hi,
I have some code which clones a form's recordset and then adds a record to it using DAO .AddNew. I want to retrieve the ID of the record just updated (it's an identity column in a linked SQL Server view)
So I have code which looks like this:

dim sNewRec as string

rs.AddNew
rs!field1=somevalue
rs.Update
rs.Requery
rs.Movelast
rs.BookMark=rs.LastModified
sNewRec=rs!NewID

When it gets to 'rs.BookMark=rs.LastModified
' it generates error 3159 - Invalid Bookmark. It then generates an error on the form 'No Current Record'.
Note: I had to include the 'requery' as if I didn't it said the record was deleted. I included the 'Movelast' as per a KB article. The frustrating thing is that this works sometimes, even though I am performing exactly the same steps each time.
Can anyone help?

 

[PeasNCarrots]

If you are using Access 2000 or higher I suggest start using ADO.

Private Sub Command11_Click()
Dim sNewRec As String
Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
.AddNew
.Fields("Name") = "Paul"
.Update
.Bookmark = .LastModified
sNewRec = .Fields("Name").Value

End With
End Sub

 

[wemeier]

The .LastModified method only works with records that are added or changed in the current recordset. Maybe the Requery is interrupting that. Try removing the .Requery and the .MoveLast instructions and see if it helps. The .LastModified method will always point to the last record added or modified in a recordset regardless of whether or not you move to the last record in the recordset.

Cruising the Information Superhighway
_{(your mileage may vary)}