Modifying Delete Button code

[Mishanya]

In my form I have Delete Button coded by the wizard with some modification as
follows:

Private Sub btnDeleteAsset_Click()
On Error GoTo Err_btnDeleteAsset_Click

If MsgBox("Are You sure You want to delete this asset?", _
vbYesNo + vbExclamation, "Attention!") = vbNo Then
Me.Undo
Cancel = True

Else

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
MsgBox "The deletion is completed"

Exit_btnDeleteAsset_Click:
Exit Sub

Err_btnDeleteAsset_Click:
MsgBox "Your deletion attempt is aborted"
Resume Exit_btnDeleteAsset_Click

End If

End Sub

The events caused by pressing the button go as this:
1) My message "Are You sure You want to delete this asset?"

if NO selected - this is it

if YES selected
2) Access message box "You are about to delete 1 record", while the record
I'm trying to delete is changed by the next record

if NO selected
3) My message "Your deletion attempt is aborted", while the recordset is
back to the attempted

if YES selected
3) My message "The deletion attempt is completed"

The step 2) is quite confusing - firstly, it doubles my message, secondly -
next record is already presented, wich makes the user hesitate what is he
about to delete.

How can I modify the code so the whole thing will work properly:
1) no Access messages but defined by me
2) no next record popping up in the step 2 (ideally - I still see the
attempted record)
3) after the deletion is completed the form is in New record mode (again, no
next record popping up)?

 

[John Spencer]

I use a more generic function that I put in a standard module and then call
from a button on a form. You can call this from a button on a form or a
subform and delete the current record on the form or subform.

The code for the button is

Private Sub btnDeleteAsset_Click
sCmdDelete Me
End Sub

or if you want to be a bit more specific about what is about to be deleted

Private Sub btnDeleteAsset_Click
sCmdDelete Me, "Asset"
End Sub

'======================= CODE Follows ======================
Public Function sCmdDelete(frmAny As Form, _
Optional strCaption As String) As Boolean
'Delete the currently selected record on a form
Dim tfAllowDeletions As Boolean
Dim tfReturn As Boolean

tfReturn = True

On Error GoTo ERROR_sCmdDelete

With frmAny
If strCaption = vbNullString Then
strCaption = .Caption
End If

If strCaption = vbNullString Then
strCaption = .name
End If

If .CurrentRecord > 0 Then 'make sure there is a record

If .NewRecord = True And .Dirty = False Then
Exit Function
End If

If MsgBox("Delete selected " & strCaption & " record?", _
vbYesNo + vbCritical, "Delete") = vbYes Then

If .NewRecord = False Then
tfAllowDeletions = .AllowDeletions
If .AllowDeletions = False Then .AllowDeletions = True
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
.AllowDeletions = tfAllowDeletions

Else 'Undo new records
.Undo
End If 'Delete existing records only

End If 'Confirm delete
End If 'Current Record
End With

EXIT_sCmdDelete:
sCmdDelete = tfReturn
DoCmd.SetWarnings True
Exit Function

ERROR_sCmdDelete:
Select Case Err.Number
Case 2501
'action cancelled
Case Else
MsgBox Err.Number & ": " & Err.Description, , "Error:
modButtons.sCmdDelete"
End Select

tfReturn = False

Resume EXIT_sCmdDelete
End Function
'======================= CODE Ends ========================

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale Fye]

Well, I never use the wizard anympre, particularly since they like to use
doMenuItem, and I have no idea what "acEditMenu, 8" or "acEditMenu, 6" means.
and this is very hard to debug.

So, I would write it something like:

Private Sub btnDeleteAsset_Click()

On Error GoTo Err_btnDeleteAsset_Click

Dim strSQL as string

If MsgBox("Are You sure You want to delete this asset?", _
vbYesNo + vbExclamation, "Attention!") = vbNo Then

'I don't know why this was included in the wizard code
'Me.Undo undoes any unsaved changes to the recordset
'and Cancel does not apply to the Click event.
Me.Undo
Cancel = True

Else

'Write changes to the current record
'Ensures the PK is valid
if me.Dirty then me.dirty = false

'Define a SQL statement to delete the currrent record
'This assumes the Primary Key on the table that is the
'record source for this form is [ID], and it is numeric
'**Replace 'yourTable' with the name of the appropriate
'**table in your database
strSQL = "DELETE * FROM yourTable WHERE [ID] = " & me.[ID]

'Delete the record. Use dbfailonerror to trap errors
'which might be encountered during the deletion
'The Execute method avoids the warnings that you
'get when you run an action query
Currentdb.execute strsql, dbfailonerror

'Set Echo to False to prevent screen updates
docmd.Echo False

'Requery the recordset to get rid of the delete record
me.requery

'Go to a new record
docmd.GoToRecord , , acNewRec

'Turn screen updates back on
docmd.Echo True

msgbox "Deletion was complete!"

'I moved the EndIf statement to preceed the
'Exit and Error pieces of the code.
End If

Exit_btnDeleteAsset_Click:
Exit Sub

Err_btnDeleteAsset_Click:
MsgBox "Your deletion attempt is aborted"
Resume Exit_btnDeleteAsset_Click

End Sub


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[ianc]

In order to stop the generic Access messages you firstly need to go to

Tools>Options>Edit/Find tab and unselect "action quires".

I would then change your code as follows:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub btnDeleteAsset_Click()
On Error GoTo Err_btnDeleteAsset_Click

If MsgBox("Are You sure You want to delete this asset?", _
vbYesNo + vbExclamation, "Attention!") = vbYes Then

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
MsgBox "The deletion is completed"
DoCmd.GoToRecord , , acNewRec
else
'do nothing
MsgBox "The deletion is aborted" ' if you want to reassure the user
end if

Exit_btnDeleteAsset_Click:
Exit Sub

Err_btnDeleteAsset_Click:
MsgBox "Your deletion attempt is aborted"
Resume Exit_btnDeleteAsset_Click

End If

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hope this helps

Ian

 

[Ken Sheridan]

In the button's Click event procedure put:

Const DELETETIONCANCELLED = 2501

On Error Resume Next
RunCommand acCmdDeleteRecord
Select Case Err.Number
Case 0
' no error
Case DELETETIONCANCELLED
' do nothing
Case Else
' unknown error so inform user
MsgBox Err.Description, vbExclamation, "Error"
End Select

In the form's Delete event procedure put:

If MsgBox("Are You sure You want to delete this asset?", _
vbYesNo + vbQuestion, "Attention!") = vbNo Then

Cancel = True
MsgBox "Your deletion attempt is aborted.", vbInformation,
"Information"
End If

and in the form's BeforeDelConfirm event procedure put:

MsgBox "Your deletion attempt is completed.", vbInformation, "Information"
Response = acDataErrContinue

By bringing the Delete and BeforeDelConfirm procedure's into play, rather
than trying to do it all in the button's event procedure, this ensures that
the same messages are displayed whether a user uses the button or uses the
built in toolbar button or menu item to delete a record.

Ken Sheridan
Stafford, England

 

[Dale Fye]

Ken,

I like that. I've never used the Delete or BeforeDelete events before.

I assume the acCmdDeleteRecord functions on the currently selected record,
in the form that has the focus.

Dale

 

[tedmi]

At the entry to the event procedure, put this statement:
DoCmd.SetWarnings False

At the exit (and *ALL* exits if there are any Exit Sub statements), put this:
DoCmd.SetWarnings True

 

[Steve Sanford]

Two things I see right away:

1) the Click event does not have a "Cancel" argument.
2) the last "End If" statement should be above the
"Exit_btnDeleteAsset_Click:" label.

Instead of DoCmd.DoMenuItem , I would use DoCmd.RunCommand.

To convert from DoMenuItem to RunCommand, see
http://www.accessruncommand.com

Try this revised code:

'***************** Code Start *******************
Private Sub btnDeleteAsset_Click()
On Error GoTo Err_btnDeleteAsset_Click

If MsgBox("Are You sure You want to delete this asset?", _
vbYesNo + vbExclamation, "Attention!") = vbYes Then

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

DoCmd.SetWarnings False
If MsgBox("Confirm deletion of the record?", _
vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes
Then

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
MsgBox "The deletion is completed"
End If
End If

Exit_btnDeleteAsset_Click:
Exit Sub

Err_btnDeleteAsset_Click:
MsgBox "Your deletion attempt is aborted"
Resume Exit_btnDeleteAsset_Click

End Sub
'****************** Code End ********************

 

[Steve Sanford]

I don't know if my first reply got posted, so I'm reposting.

There are two things I see right off:

1) The Click event is not cancelable.
2) The last "End If" is in the wrong place.

Terry Wickendon has a site that shows "DoMenuItem" conversion to "RunCommand".

Below is your code I modified.....

'***************** Code Start *******************
Private Sub btnDeleteAsset_Click()
On Error GoTo Err_btnDeleteAsset_Click

If MsgBox("Are You sure You want to delete this asset?", _
vbYesNo + vbExclamation, "Attention!") = vbYes Then

DoCmd.SetWarnings False
If MsgBox("Confirm deletion of the record?", _
vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes
Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
MsgBox "The deletion is completed"
End If
End If

Exit_btnDeleteAsset_Click:
DoCmd.SetWarnings True
Exit Sub

Err_btnDeleteAsset_Click:
MsgBox "Your deletion attempt is aborted"
Resume Exit_btnDeleteAsset_Click

End Sub
'****************** Code End ********************

HTH

 

[tedmi]

At the entry to the sub, put this statement:
DoCmd.SetWarnings False

At the exit (and *every* exit if there are any Exit Sub statments):
DoCmd.SetWarnings True

 

[MikeJohnB]

Try the following, (untested)

Private Sub btnDeleteAsset_Click()
On Error GoTo Err_btnDeleteAsset_Click

If MsgBox("Are You sure You want to delete this asset?", _
vbYesNo + vbExclamation, "Attention!") = vbNo Then
Me.Undo
Cancel = True

Else
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True
MsgBox "The deletion is completed"
End If

Exit_btnDeleteAsset_Click:
Exit Sub

Err_btnDeleteAsset_Click:
MsgBox "Your deletion attempt is aborted"
Resume Exit_btnDeleteAsset_Click



End Sub

Hope this answers your question?

Regards
Mike B

 

[Ken Sheridan]

Dale:

That's correct. It’s the RunCommand equivalent of the old menu item
commands which the wizard used.

Ken Sheridan
Stafford, England

 

[Mishanya]

To all the guys who've answered - thank U very much.
I was not able to appriciate Your replies due to MS forum site technical
problems (could not see the tre till today).