Keeping the focus on the next record when deleting a record in a

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

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

Re: Keeping the focus on the next record when deleting a record in a subform (Access 2003)

On Mon, 19 Nov 2007 14:03:25 -0800 (PST), stuart
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