How can I identify a record's current position in a form's recordset?

[Dave]

I am deleting a record from a subform in continuous forms view. There can be
over 100 records displayed in the subform.

I want to preserve the location of the record being deleted so that the user
is not automatically repositioned at the top of the recordset after deletion
(and so than has to scroll back through all the records again to find where
he was when he deleted)

What I thought I could do to accomplish this is....

'capture the position of the record to be deleted...
lCurs = Me.Recordset.AbsolutePosition

'perform the delete....

'then requery the sub form and position on the adjacent record
With Me
.RecordSource = sSQL
.Requery
With .RecordsetClone
.MoveLast
If .RecordCount > 0 Then
If .RecordCount - 1 < lCurs Then
lCurs = .RecordCount - 1
End If
Me.Recordset.AbsolutePosition = lCurs
End If
End With
End With

However, lCurs is always zero. I think this is because, according to HELP,
the "AbsolutePosition property isn't available on forward-only-type
Recordset objects,"

Can someone confirm this? And if it is true, does anyone have an
alternative approach I could use?

 

[Allen Browne]

An alternative would be to delete the record from the form's RecordsetClone:

If Me.Dirty Then Me.Undo
If Me.NewRecord Then
Beep
Else
With Me.RecordsetClone
If .RecordCount > 0 Then
.Boomark = Me.Bookmark
.Delete
End If
End With
End If

If that is not suitable, you could save the primary key of the next record
into a variable (using RecordsetClone to get that value), and then after the
delete, FindFirst that value in the RecordsetClone and set the Bookmark of
the form to that pointer.