Deleting record

[ClaraBarton]

I've been using this simple sub to delete records (which I very seldom do, so I was unaware...) and discovered I was deleting the wrong record. I believe the problem is in the Me.FilterOn = false line and when I comment it out the proper record is deleted. If I don't turn off the filter, move next creates an empty record that needs to be deleted. Where is the proper place to unfilter this form and show the next record?

Private Sub cmdDelete_Click()
On Error GoTo Error_Handler
Dim intID As Integer
Dim rs As DAO.Recordset
Dim varResponse As Variant

varResponse = MsgBox("Are you sure you want to delete " & vbLf & _
        "this record? " & vbCr, _
        vbOKCancel, "Deleting Contacts")
   If varResponse = vbOK Then
  
        intID = Me.ContactsID
        Me.FilterOn = False
        Set rs = Me.RecordsetClone
            With rs
            .FindFirst "[ContactsID]=" & intID
            .Bookmark = Me.Bookmark
            .Delete
            .MoveNext
            End With
        rs.Close
 
    Else: Exit Sub
    End If
  Me.Requery
Exit_Procedure:

 

[Gasman]

Why do you want to set the filter off?
If you are able to see the record to be deleted, the filter does not matter, surely?
If contacts I'd is autonumber then inside should be long?

 

[ClaraBarton]

Because I don't want an empty record on my form

 

[Gasman]

If you mean empty recordset, set the filter off as the last command.
No need to movenext either I would have thought?

 

[DickyP]

Assuming the empty record you are seeing is in a list form all that's needed is a Form.requery.

 

[MarkK]

I would never navigate in a recordset to perform a delete. If you know the ID of the row, and you are happy to perform a requery after the operation completes, use an SQL delete command, like...

Private Sub cmdDelete_Click()
    Const SQL_DELETE As String = _
        "DELETE FROM tContact WHERE ContactID = "
    
    Dim rsp As VbMsgBoxResult

    rsp = MsgBox("Are you sure you want to delete " & vbLf & _
        "this record? " & vbCr, _
        vbOKCancel, "Deleting Contacts")
    
    If rsp = vbOK Then If SQLExecute(SQL_DELETE & Me.ContactID) Then Me.Requery
End Sub

Private Function SQLExecute(SQL As String) As Integer
    With CurrentDb
        .Execute SQL, dbFailOnError
        SQLExecute = .RecordsAffected
    End With
End Function

 

[gemma-the-husky]

Seriously, think very carefully about deleting any records. You could just mark them as "dead". The real question is how are records being entered that turn out to be not required? Are users misunderstanding something? Is the database or code not working correctly?

 

[MarkK]

The real question is how are records being entered that turn out to be not required?

Excellent point.

 

[The_Doc_Man]

When you recognize that you will have a requirement to selectively delete records at a future time, with or without prior archiving, you need to add a field to your record to help you recognize and implement these potential steps. This field would be Yes/No and would always / only represent a record to be archived/removed. So then run a SELECT query to show only the marked records and use a DoCmd.OutputTo (or your favorite other way of doing this) from that query to write to an Excel or text file. Then immediately after that, run a DELETE query to remove only the marked records. Now, how you go about SETTING that Y/N flag is up to you. And note, of course, that the archiving step is "only if you have that requirement."

 

[ClaraBarton]

Thanks y'all!