Can't Move Cursor in Recordset After Deleting Record

[Altemir]

I have a form in an ADP project that allows the user to delete a
selected record. The problem is that I want the record selector to
return to the adjacent record after the deletion is made, but it won't
do that -- the cursor always jumps to the top of the recordset. How
can I use a bookmark to return the cursor to the place (more or less)
where the deleted record used to be?

Here's what I got now:

' Store current recordset and primary key value for current record
Set rst = Me.Recordset
iRecID = Me!RecID

' Bookmark record that is adjacent to record that is currently selected
' and will be deleted
With rst
If .BOF = True Then
.MoveNext
Else
.MovePrevious
End If
bm = .Bookmark
End With


DoCmd.RunSQL "DELETE FROM dbo.MyTable WHERE RecID = " & CStr(iRecID)
Set Me.Recordset = rst
Me.Refresh

' Return to adjacent record (this is what is not working)
Me.Bookmark = bm

 

[Baz]

That's a bizarre way of going about doing a deletion. Why are you sneaking
behind the form's back with RunSQL instead of just deleting the record via
the form? If you use the form to do the deletion, as God and Microsoft
intended, then it will behave exactly as you want.

As for your code, I would think that the Refresh will render your saved
bookmark invalid. In any case, in my experience, bookmarks are very buggy
in ADP's and often don't behave as they should.

 

[Sylvain Lafontaine]

Bookmarks are very buggy in ADP is you are using Me.RecordsetClone instead
of Me.Recordset.Clone; however, with the later, you shouldn't have any
problem under Access 2003.

Also, I'm agreeing with the fact that the code posted in the OP seems to be
very tortuous and that there are probably better alternatives. For example,
you could use the macro DeleteRecord or the ADO recordset's action Delete or
the Delete button on the menu (that can be probably accessed from VBA like
any other menu's options).

If you want to delete first on the SQL-Server, then you should store the
primary key instead of the bookmark, make a requery and jump to the desired
location using the stored primary key.

Finally, your piece of code won't work when the cursor is on the first
record because BOF doesn't test for the first record but test for when the
position of the cursor is *before* the first record (ie, point toward
nothing like EOF).

 

[Sylvain Lafontaine]

I forgot to mention that the newsgroup for ADP is m.p.a.adp.sqlserver.

 

[Sylvain Lafontaine]

For using the menu command or the acCmdDeleteRecord command to delete a
record, see:
http://www.access-programmers.co.uk/forums/showthread.php?t=108727