Positioning on a continuous form after deleting a record

(Last updated - 2010-09-27)

[ Access Tips | Access | Main ]

If you delete a record which is on a continuous form you do a me.requery to remove the deleted record from the continuous form.  However the requery then positions the cursor at the top of the records in the continuous form.  If you have scrolled down a few pages you will lose your position.  And you will then have to scroll back down again.

I use the following code to save the primary key of the record previous to the record I'm deleting.   Once deleted I then position the cursor to that record.  The problem is that this particular record is now moved to the top of the screen. 

(There may very well be a means of repositioning the continuous form so it appears as though the deleted record has just disappeared and the subsequent records have shifted up.   But I don't know of any.)

 ' locate the previous transaction
Me.RecordsetClone.FindFirst "[transID] = " & Me.transID
Me.RecordsetClone.move -1
PosnTransID = Me.RecordsetClone!transID

' Delete your records here

' Position at the record previous to the one just deleted
Me.Requery
If PosnTransID > 0 Then
    Me.RecordsetClone.FindFirst "[transID] = " & PosnTransID
    Me.Bookmark = Me.RecordsetClone.Bookmark
End If

' more logic

DeleteTransaction_Error:
Select Case Err.Number
Case 3021 ' No current record. which happens when deleting the first transaction
    On Error Resume Next
Case Else
    MsgBox Err.Description
End Select
 

[ Access Tips | Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available