Delete button not asking if the user wants to delete record first

[SeadooRider]

I used the button wizard to set up a delete record button. It does delete a record and then moves to the next record, however I need it to ask the user first before deleting and also if it is the last record I need it to return the previous record.
I'm not real familier with working with macros.
Is this an easy fix or should this be in code instead?

v/r,
jason

[Seth Schrock]

Most of us in this forum use VBA for everything, so most solutions will probably use VBA.

What I would do is to use the MsgBox function to ask the user if they want to delete the record. If yes, then use the Recordset.Delete method to delete the record and then do a MovePrevious to go the previous record.

[SeadooRider]

Ok so, I've put together this code but wanted you to look at it so I don't delete something I shouldn't...like my whole form or something.

Private Sub cmdDelete_Click()
If Me.Currentrecord = Me.Recordset.Recordcount then
Msgbox "Are you sure you want to delete this record?", vbCritical, "Deleting Record", vbYesNo
If vbNo then
DoCmd.CancelEvent
Else
If vbYes then
Recordset.Delete
DoCmd.GoToRecord, ,acPrevious
End If
End If
End Sub
 

[NeoPa]

That seems like a good question to ask Jason. Here is an alternative version of your code with some comments explaining some of it and the differences :

Private Sub cmdDelete_Click()
    Dim blnLast As Boolean
 
    'MsgBox call must return the value in order to be checked.
    'If user says no then cancelling is not required.  It is only required 
    '  that the deletion command is not invoked.
    If MsgBox(Prompt:="Are you sure you want to delete this record?" _
            , Buttons:=vbYesNo Or vbQuestion _
            , Title:="Deleting Record") = vbNo Then Exit Sub
    'At this point it's clear we need to delete the record.
    'Using With Me is a more efficient approach to object usage.
    With Me
        'Remember for later if record is last.
        blnLast = (.CurrentRecord = .Recordset.RecordCount)
        Call .Recordset.Delete
        'Only step back if deleted record was the last.
        If blnLast Then Call DoCmd.GoToRecord(Record:=acPrevious)
    End With
End Sub

The MsgBox() call uses named parameters in order to make understanding the code easier. Multi-parameter functions are easy to be confused by if only the position of the parameters is there to indicate what is what. I recommend you look into the possibilities here. The Buttons parameter at least can be fiddly as it uses bitwise OR operations on the various possible values. Not easy logic to grasp.

[Seth Schrock]

@NeoPa Just curious, would there be a difference in line 17 between using the DoCmd.GoToRecord... vs .Recordset.MovePrevious? The later seems simpler, especially since you already have it in the With statement.

Also, I just want to clarify something. I believed that Me.Recordset.Delete deleted the current record, but in verifying this to make sure the wrong records wouldn't be deleted, I found the following post in another forum that said you have to tell the recordset which record you want to delete. Unfortunately, MSDN says nothing about the Delete method for versions 2007 or 2010 (I haven't looked for 2003), and I couldn't find anything else online to confirm one side or another. I see you have used the Delete method, so I'm assuming that I was correct the first time.

[ADezii]

As a side note, if you are going to use this approach you must make sure that the User cannot use the Menu Bar or any other Method to Delete a Record. Should this happen, your Code will never be executed.

[NeoPa]

Seth:
@NeoPa Just curious, would there be a difference in line 17 between using the DoCmd.GoToRecord... vs .Recordset.MovePrevious? The later seems simpler, especially since you already have it in the With statement.

Very possibly Seth. My aim in providing the code was to illustrate the basic logic and the flow of the code. I didn't really bother about the actual job being done. The original code illustrated some lack of understanding of the flow I felt, so I wanted to illustrate what was doing what and and when each part should be included. Also, to provide a structure for the code that was solid and reliable, to be built on further if required.

[SeadooRider]

Ok, I used the following code and got an error saying End With without with.

Private Sub cmdDelete_Click()
 
Dim blnLast As Boolean
 
'Msgbox call must return the value in order to be checked
If MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deleting Record") Then
If vbNo then
Exit sub
 
With me
  blnLast=(.CurrentRecord=.Recordset.RecordCount)
  Call .Recordset.Delete
  If blnLast Then
  DoCmd.GoToRecord, ,acPrevious
 End With
End Sub

[Rabbit]

You're missing an End If.

[SeadooRider]

I've added the End If. Now there are no errors but it is also not deleting the record.

Private Sub cmdDelete_Click() 
 
Dim blnLast As Boolean 
 
'Msgbox call must return the value in order to be checked 
If MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deleting Record") = vbNo then 
Exit sub 
 
With me 
  blnLast=(.CurrentRecord=.Recordset.RecordCount) 
  Call .Recordset.Delete 
  If blnLast Then 
  DoCmd.GoToRecord, ,acPrevious 
End If
 End With
End If 
End Sub 
 

[Seth Schrock]

Try doing a requery (or just close the form and reopen it) to make sure that the form is updated with the latest data. I didn't think that this was necessary, but no harm checking.

[SeadooRider]

adding a requrey takes it back to the 1st record if the user clicks yes, instead of going to the previous record. And, it doesn't delete the record.

[Rabbit]

Your End If for your first If statement is in the wrong place. Move line 16 to line 8.

[NeoPa]

Jason, you've changed both instances of my single-line If statements into multi-line ones, but without converting it to the multi-line format. If it goes across multiple lines, and without continuation characters, it's the multi-line format and requires an End If.