Keeping the focus on the next record when deleting a record in asubform (Access 2003)

[stuart]

I have a list of records in a subform that a user can either edit or
delete. This is an unbound form. If the user deletes a record, I
want to refresh the form, and then position the cursor on the next
record on that subform. This seems like that this should be a fairly
easy thing to accomplish, but I cannot get it to reposition the cursor
on the next record. The following is my code segment:

Any help would be greatly appreciated!!!

Thanks.

Stuart

----------------------------------------------
(The record source is a query that returns all the records from the
Card1 table)

Set rst = Forms![frmedit]![card1 subform].[Form].RecordsetClone

rst.Bookmark = Forms![frmedit]![card1 subform].[Form].Bookmark

RecordPos = rst.AbsolutePosition
NCID = rst![NCID]

db.Execute "Delete * from Card1 where NCID = " & "'" & NCID & "'",
dbFailOnError

'Commit the database changes
wsp.CommitTrans

Forms![frmedit]![card1 subform].[Form].Requery

rst.MoveFirst
rst.Move RecordPos, rst.Bookmark

----------------------------------------------------

[Wayne Gillespie]

On Mon, 19 Nov 2007 14:03:25 -0800 (PST), stuart <st***********@ncmail.net>
wrote:

>I have a list of records in a subform that a user can either edit or
delete. This is an unbound form. If the user deletes a record, I
want to refresh the form, and then position the cursor on the next
record on that subform. This seems like that this should be a fairly
easy thing to accomplish, but I cannot get it to reposition the cursor
on the next record. The following is my code segment:

Any help would be greatly appreciated!!!

Thanks.

Stuart

----------------------------------------------
(The record source is a query that returns all the records from the
Card1 table)

Set rst = Forms![frmedit]![card1 subform].[Form].RecordsetClone

rst.Bookmark = Forms![frmedit]![card1 subform].[Form].Bookmark

RecordPos = rst.AbsolutePosition
NCID = rst![NCID]

db.Execute "Delete * from Card1 where NCID = " & "'" & NCID & "'",
dbFailOnError

'Commit the database changes
wsp.CommitTrans

Forms![frmedit]![card1 subform].[Form].Requery

rst.MoveFirst
rst.Move RecordPos, rst.Bookmark

----------------------------------------------------

Bookmarks are destroyed and recreated when a form or recordset is requeried and
so are useless for navigation in this scenario.

The most reliable way to move to a specific record after a form (and it's
associated RecordsetClone) is requeried, is to store the PK value or a unique
identifier from the record you wish to move to before you do the delete and
requery, and then use this value in a FindFirst call on the recordsetClone after
it has been requeried.

'get the PK of the next record
rst.MoveNext
NextPK = rst!PkField
rst.MovePrevious

NCID = rst![NCID]
db.Execute "Delete * from Card1 where NCID = " & "'" & NCID & "'",dbFailOnError

wsp.CommitTrans
Forms![frmedit]![card1 subform].[Form].Requery

rst.FindFirst "[PKField]=" & NextPK

If Not rst.NoMatch Then
Forms![frmedit]![card1 subform].[Form].Bookmark = rst.Bookmark
End If

Wayne Gillespie
Gosford NSW Australia