Go to next record after Delete record.

[rwolff]

This post is related to thread705-612488.

This is what I want to do.
1. Delete a record.
2. Requery the form. This shows the first record of the set.
3. Go to the record after the one I just deleted to continue viewing records.

e.g. I delete Smith, John (record number 97 lets say). After the form is requeried, I want to view Smyth, John, the record that previously immediately followed Smith, John.

I do not think I can use RecordsetClone and bookmarks because I am deleting the reference record.

How can I do this?

 

[PHV]

But you may do a FindFirst against saved value of the deleted record ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wemeier]

Just before deleting the current record, do a movenext on the recordsetclone and save that record's bookmark. After the requery use the saved bookmark to position the form:

Private Sub cmdDelete_Click()
Dim rst As Recordset
Dim strBookmark As String

On Error GoTo Err_cmdDelete_Click

Set rst = Me.RecordsetClone
rst.MoveNext
If Not rst.EOF Then                 ' if not end-of-file
    strBookmark = rst.Bookmark      ' ...save the next record's bookmark
    rst.Bookmark = Me.Bookmark      ' ...go back to the record to delete
    rst.Delete                      ' ...delete the record
    Me.Requery
    Me.Bookmark = strBookmark       ' ...and return to the saved bookmark
Else
    rst.Delete                      ' ...just delete the record
    Me.Requery
    rst.MoveLast                    ' ...move to the last record
    Me.Bookmark = rst.Bookmark      ' ...position the form to it
End If

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
End Sub

Cruising the Information Superhighway
[sub] (your mileage may vary)[/sub]