Move to next record after delete

[ClaraBarton]

I've debugged and debugged and NextPK always returns the same record as IntID
I feel like I'm missing something very obvious and will feel like an idiot...

20        Me.Filter = ""
30        Me.FilterOn = False
       
40    Set rst = Me.RecordsetClone
50    intID = Me.recipeid
      'get the PK of the next record
60        With rst
70         If Not .EOF Then
80             .Move 1
90                 NextPK = Me.recipeid
100            .Move -1
        'or the previous one
110          Else: .Move -1
120                NextPK = Me.recipeid
130            .Move 1
140        End If
150        End With
180   varResponse = MsgBox("Are you sure you want to delete " & vbLf & _
              "this recipe? " & vbCr, _
              vbOKCancel, "Deleting Recipe")
190      If varResponse = vbOK Then
200           strSQL = "DELETE * FROM [t_Recipe] WHERE RecipeID = " & intID
        
210       CurrentDb.Execute strSQL, dbFailOnError

220       Me.Requery
230       rst.FindFirst "[RecipeID]=" & NextPK

240       rst.Close

 

[moke123]

Me.Bookmark = .Bookmark on your second find first?

 

[pbaldy]

I would also expect you to get the next record after moving the recordset to the current record, not before. You're always going to get the same record as it is now (the second if I'm not mistaken).

 

[ClaraBarton]

eh... I doubt I even need the bookmark. Or the findfirst. I'm marking the current record (intID) then moving to the next or previous and making it NextPK.
Then deleting intID. Right?
Wait... the Sql should have recipeid = intID. Still...
I just need to know how to return the next or previous record.

 

[pbaldy]

The conceptual mistake is that you've opened a recordset on the recordset clone of the form. That's fine, but that recordset is not necessarily sitting on the same record as is selected on the form. It is likely sitting on the first record, no matter what record may be selected on the form. That means when you move 1, you aren't moving 1 from the selected record, you're moving 1 from the first record.

 

[jdraw]

Perhaps you could step back and tell us what you want to happen. I note you want to delete a record (recipe) given a specific ID. But then what is it you want to do?
For example, are recipes stored by Number, or subject...? If I delete RecipeID 35 "Coconut Shrimp", do I go to recordID 36 "Roadkill Stew" or a recipe like "Coconut Toast"?
Do you have a list of recipes that you want to delete? Sort of cleanup the Recipe Inventory??

I think PBaldy has identified the current issue.

 

[Josef P.]

Note:

70         If Not .EOF Then
80             .Move 1
90                 NextPK = Me.recipeid ' <--- but here could now be EOF.

I would break the code down into the individual tasks, then you don't get confused with Recordset vs. RecordsetClone.

intID = Me.recipeid
varResponse = MsgBox("Are you sure you want to delete " & vbLf & _
              "this recipe? " & vbCr, _
              vbOKCancel, "Deleting Recipe")
If varResponse = vbOK Then

     NextPK = GetNextPkToShow()
     DeleteRecord intID
     Me.Requery
     GoToPkRecord NextPK

end if

..

private function GetNextPkToShow() as Long
' Note: what if there is currently only 1 record?

    dim NextPK as long

     with me.RecordsetClone
          ...
     end with

    GetNextPkToShow = NextPK

end function

private sub DeleteRecord(byval PkToDelete as long)
     dim strSQL as String
     strSQL = "DELETE * FROM [t_Recipe] WHERE RecipeID = " & PkToDelete
     CurrentDb.Execute strSQL, dbFailOnError
end sub

private sub GoToPkRecord(byval Pk as long)
     me.Recordset.FindFirst "[RecipeID]=" & Pk
end sub

[OT]

varResponse = MsgBox("Are you sure you want to delete " & vbLf & _
              "this recipe? " & vbCr, _
              vbOKCancel, "Deleting Recipe")

I'm not quite sure, because I don't know which ones you want to delete.

How about:

varResponse = MsgBox("Should recipe " & me.RecipeNo.Value & " be deleted?", _
              vbYesNo, "Deleting Recipe")

or

varResponse = MsgBox("Recipe " & me.RecipeNo.Value & " will now be deleted.", _
              vbOKCancel, "Deleting Recipe")

 

[ClaraBarton]

Wait... The clone doesn't match the recordset? I thought they were equal. What I want to do is go to the next record after deleting a record. I'm using unique id's.

 

[Gasman]

They are equal, but you can move independently. One member here constantly makes that error.
They locate a record in the recordset, then use the form control, which generally has different data.

 

[MajP]

They are equal, but you can move independently. One member here constantly makes that error.

Strange because I can think of no other reason than to be able to move in the recordsetclone without moving in the recordset. I cannot think of an example of why you might need a clone for something else.

 

[moke123]

This works as the bookmark property is unaffected by deleting previous records.

    On Error GoTo Handle_Click_Error
 
    Dim NextBmk
    Dim rs As DAO.Recordset
 
    Set rs = Me.Recordset    
 
    If Not rs.EOF Then

        rs.MoveNext

        NextBmk = rs.Bookmark

        rs.MovePrevious

        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True

        Me.Bookmark = NextBmk
 
    Else
 
        MsgBox "No next record"
 
    End If
     
    On Error GoTo 0
    Exit Sub

Handle_Click_Error:

    If Err.Number = 3021 Then MsgBox "No next record": Exit Sub

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure , line " & Erl & "."

 

[Gasman]

Strange because I can think of no other reason than to be able to move in the recordsetclone without moving in the recordset. I cannot think of an example of why you might need a clone for something else.

Well al I can say, is that a recordsetclone record was found, then the form control was used, and they wondered why they had not dound the correct data. It has been a while I admit, but I am pretty sure that was the case.

I am away for the weekend, but if I remember, I will try a quick test to confirm, when I get back.
I am sure you could test far quicker than I anyway.

 

[Gasman]

Had time for a quick test
Just opened a form on a table.

Private Sub cmdTest_Click()
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "Id= 21"

Debug.Print rs!TransactionDate
Debug.Print Me.TransactionDate
End Sub

Result

25/01/2016 
08/01/2016

 

[apr pillai]

When you delete a record (say current record EmployeeID=5) on the Form, selecting the Recordselector of the Form manually and pressing the Delete Key will make the next record EmployeeID 6 become current on the Form automatically.

If you want to do the same thing through the Form's RecordSetClone method then try the following VBA Code:

Private Sub Command9_Click()
Dim rst As Recordset
Dim RecKey As Integer

'Make EmployeeID 5 as current Record
'on the Form before running the Code
'with the Command9_Click()
RecKey = Me!EmployeeID 'get current record ID

Set rst = Me.RecordsetClone
rst.FindFirst "EmployeeID = " & RecKey
If Not rst.NoMatch Then

   If MsgBox("Are you Sure to delete EmployeeID: " _
   & RecKey, vbYesNo + vbCritical, "Command9_Click()") = vbYes Then

       rst.Delete

   End If
Else
   MsgBox "EmployeeID: " & RecKey & " Not Found!", vbCritical+vbOK,"Command9_Click()"
End If

'After EmployeeID 5 Deletion, the next record's EmployeeID 6 will become current on the Form
'Msgbox given below displays the current record ID.

MsgBox "Next EmployeeID: " & Me![EmployeeID] & " Is Current."

'if you would like use the BookMark method to make the next record current, after deletion, then use the following statement:
Me.bookmark = rst.BookMark 

rst.Close
End Sub

 

[ebs17]

Move to next record after delete

The sequence of delete query, requery and findfirst is a cumbersome effort.
If you focus on the issue itself, you can work directly on the form recordset. When the current record is deleted, the focus is automatically on the next record. For the step back one would add a MovePrevious.

Private Sub cmdButtonDelete_Click()
        'Debug.Print Me.ID
    Me.Recordset.Delete
    ' Me.Recordset.MovePrevious
        'Debug.Print Me.ID
End Sub

I left out the stuff with the query and the check for BOF or EOF. The question would first have to be answered as to what should happen if there is no next or previous record.

By the way: My users would be very annoyed if they were disturbed by messages. When they press a delete button, they definitely want to delete and continue working productively and smoothly.

 

[Mike Krailo]

By the way: My users would be very annoyed if they were disturbed by messages. When they press a delete button, they definitely want to delete and continue working productively and smoothly.

One of my pet peeves for sure. And are you sure you want to delete? Damn, those message box prompts. I try to turn the record selectors off when it makes sense and use a button for the deleting that is nice and small. Also the tab stop is set to no on this type of button. So when it is clicked on, there is no doubt the user means to delete it.

 

[jdraw]

I wonder what the actual procedure the OP (Clara) is using/automating. If the activity involved is to delete one or more records, then having a list of recordids or recipe names (something unique to a record) could be done without recordsetClone. Depending on the user and the process involved, the messages could be quite distracting as has been mentioned.