Delete Record Button with Confirmation Message

  • Thread starter Konchetta via AccessMonster.com
  • Start date
K

Konchetta via AccessMonster.com

I finally got the message box to pop up but the same time the message box
pops up, the record is deleted without confirmation of yes or no. I know I am
missing something in my code and this forum helped me to get this far from
viewing other posts but not sure what I am missing. Here is my code:
(Deleting a record in a subform)

Private Sub Testdelete_Click()
Dim frm As Form
Dim Response As String
Set frm = Me.[Scheduling Visits Subform].Form
If Not frm.NewRecord Then
With frm.RecordsetClone
.Bookmark = frm.Bookmark
.Delete
Response = MsgBox("Are you sure you want to delete this record")

If Response = vbYes Then
.Bookmark = frm.Bookmark
.Delete
End If

End With

Else

End If

Set frm = Nothing
DoCmd.SetWarnings True

End Sub
 
J

John Spencer

Well, the only value returned to response is always going to be 1 which should
never match vbYes (that is 6). So something else is happening here such as
the fact that you do a delete before you check if the user wants to delete the
record.

Response = MsgBox("Are you sure you want to delete this record?", vbYesNo,
"DELETE")

Personally, I use a generic function to do this. If you put the delete button
on the subform, you can call the below code with a simple

sCmdDelete Me

If the button is on the main form you will need to use
sCmdDelete Me.[Scheduling Visits Subform].Form

Public Function sCmdDelete(frmAny As Form, _
Optional strCaption As String) As Boolean
'Delete the currently selected record on a form

'---------------------------------------------------------------
' Code Change: Add optional strCaption to code to allow specific user message
'---------------------------------------------------------------

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
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


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I finally got the message box to pop up but the same time the message box
pops up, the record is deleted without confirmation of yes or no. I know I am
missing something in my code and this forum helped me to get this far from
viewing other posts but not sure what I am missing. Here is my code:
(Deleting a record in a subform)

Private Sub Testdelete_Click()
Dim frm As Form
Dim Response As String
Set frm = Me.[Scheduling Visits Subform].Form
If Not frm.NewRecord Then
With frm.RecordsetClone
.Bookmark = frm.Bookmark
.Delete
Response = MsgBox("Are you sure you want to delete this record")

If Response = vbYes Then
.Bookmark = frm.Bookmark
.Delete
End If

End With

Else

End If

Set frm = Nothing
DoCmd.SetWarnings True

End Sub
Click to expand...
 
K

Konchetta via AccessMonster.com

Thanks a bunch Mr. Spencer..It's amazing how you can write the code. Is there
an Access training that you guys attend to be certified or something? If so,
where is it offered? But I will try this code on the subform as well to see
what I get!!

John said:
Well, the only value returned to response is always going to be 1 which should
never match vbYes (that is 6). So something else is happening here such as
the fact that you do a delete before you check if the user wants to delete the
record.

Response = MsgBox("Are you sure you want to delete this record?", vbYesNo,
"DELETE")

Personally, I use a generic function to do this. If you put the delete button
on the subform, you can call the below code with a simple

sCmdDelete Me

If the button is on the main form you will need to use
sCmdDelete Me.[Scheduling Visits Subform].Form

Public Function sCmdDelete(frmAny As Form, _
Optional strCaption As String) As Boolean
'Delete the currently selected record on a form

'---------------------------------------------------------------
' Code Change: Add optional strCaption to code to allow specific user message
'---------------------------------------------------------------

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
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

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I finally got the message box to pop up but the same time the message box
pops up, the record is deleted without confirmation of yes or no. I know I am
Click to expand...
[quoted text clipped - 27 lines]
End Sub
Click to expand...
Click to expand...
 
K

Konchetta via AccessMonster.com

Mr. Spencer,
I did correct the following with this code you provided: Response = MsgBox
("Are you sure you want to delete this record?", vbYesNo,
"DELETE")

and I now have my confirmation box with Yes/No but it still deletes the
record before I am able to click Yes or No and an error time message pops up
saying no current record. My button is on my main form which is a tab form
named Scheduling and my subform is a datasheet. Does that make a difference
in the coding?

I will try the other code as well! Thanks a bunch!!

John said:
Well, the only value returned to response is always going to be 1 which should
never match vbYes (that is 6). So something else is happening here such as
the fact that you do a delete before you check if the user wants to delete the
record.

Response = MsgBox("Are you sure you want to delete this record?", vbYesNo,
"DELETE")

Personally, I use a generic function to do this. If you put the delete button
on the subform, you can call the below code with a simple

sCmdDelete Me

If the button is on the main form you will need to use
sCmdDelete Me.[Scheduling Visits Subform].Form

Public Function sCmdDelete(frmAny As Form, _
Optional strCaption As String) As Boolean
'Delete the currently selected record on a form

'---------------------------------------------------------------
' Code Change: Add optional strCaption to code to allow specific user message
'---------------------------------------------------------------

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
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

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I finally got the message box to pop up but the same time the message box
pops up, the record is deleted without confirmation of yes or no. I know I am
Click to expand...
[quoted text clipped - 27 lines]
End Sub
Click to expand...
Click to expand...
 
K

Konchetta via AccessMonster.com

I got it to work!! Thanks so much!!

John said:
Well, the only value returned to response is always going to be 1 which should
never match vbYes (that is 6). So something else is happening here such as
the fact that you do a delete before you check if the user wants to delete the
record.

Response = MsgBox("Are you sure you want to delete this record?", vbYesNo,
"DELETE")

Personally, I use a generic function to do this. If you put the delete button
on the subform, you can call the below code with a simple

sCmdDelete Me

If the button is on the main form you will need to use
sCmdDelete Me.[Scheduling Visits Subform].Form

Public Function sCmdDelete(frmAny As Form, _
Optional strCaption As String) As Boolean
'Delete the currently selected record on a form

'---------------------------------------------------------------
' Code Change: Add optional strCaption to code to allow specific user message
'---------------------------------------------------------------

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
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

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I finally got the message box to pop up but the same time the message box
pops up, the record is deleted without confirmation of yes or no. I know I am
Click to expand...
[quoted text clipped - 27 lines]
End Sub
Click to expand...
Click to expand...
 
J

John Spencer

For me, it's been the school of hard knocks and lots of time spent
experimenting and correcting my mistakes. Also, some reading and lots of time
spent on the internet looking for solutions.

I've reposted your code below. NOTE the changes that should make this work.

Private Sub Testdelete_Click()
Dim frm As Form
Dim Response As String
Set frm = Me.[Scheduling Visits Subform].Form
If Not frm.NewRecord Then
With frm.RecordsetClone
.Bookmark = frm.Bookmark
'========================================================================
.Delete '<<<<<<<<<<<<< YOU DELETE HERE >>>>>>>>
'So the deletion has already taken place. You need to remove this line
'=========================================================================
Response = MsgBox("Are you sure you want to delete this record", _
VbYesNo, "DELETE")

If Response = vbYes Then
.Bookmark = frm.Bookmark
.Delete
End If

End With

Else

End If

Set frm = Nothing
DoCmd.SetWarnings True

End Sub

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Mr. Spencer,
I did correct the following with this code you provided: Response = MsgBox
("Are you sure you want to delete this record?", vbYesNo,
"DELETE")

and I now have my confirmation box with Yes/No but it still deletes the
record before I am able to click Yes or No and an error time message pops up
saying no current record. My button is on my main form which is a tab form
named Scheduling and my subform is a datasheet. Does that make a difference
in the coding?

I will try the other code as well! Thanks a bunch!!

John said:
Well, the only value returned to response is always going to be 1 which should
never match vbYes (that is 6). So something else is happening here such as
the fact that you do a delete before you check if the user wants to delete the
record.

Response = MsgBox("Are you sure you want to delete this record?", vbYesNo,
"DELETE")

Personally, I use a generic function to do this. If you put the delete button
on the subform, you can call the below code with a simple

sCmdDelete Me

If the button is on the main form you will need to use
sCmdDelete Me.[Scheduling Visits Subform].Form

Public Function sCmdDelete(frmAny As Form, _
Optional strCaption As String) As Boolean
'Delete the currently selected record on a form

'---------------------------------------------------------------
' Code Change: Add optional strCaption to code to allow specific user message
'---------------------------------------------------------------

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
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

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I finally got the message box to pop up but the same time the message box
pops up, the record is deleted without confirmation of yes or no. I know I am
Click to expand...
[quoted text clipped - 27 lines]
End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

Konchetta via AccessMonster.com

That did it!! You're AMAZING!!!

John said:
For me, it's been the school of hard knocks and lots of time spent
experimenting and correcting my mistakes. Also, some reading and lots of time
spent on the internet looking for solutions.

I've reposted your code below. NOTE the changes that should make this work.

Private Sub Testdelete_Click()
Dim frm As Form
Dim Response As String
Set frm = Me.[Scheduling Visits Subform].Form
If Not frm.NewRecord Then
With frm.RecordsetClone
.Bookmark = frm.Bookmark
'========================================================================
.Delete '<<<<<<<<<<<<< YOU DELETE HERE >>>>>>>>
'So the deletion has already taken place. You need to remove this line
'=========================================================================
Response = MsgBox("Are you sure you want to delete this record", _
VbYesNo, "DELETE")

If Response = vbYes Then
.Bookmark = frm.Bookmark
.Delete
End If

End With

Else

End If

Set frm = Nothing
DoCmd.SetWarnings True

End Sub

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Mr. Spencer,
I did correct the following with this code you provided: Response = MsgBox
Click to expand...
[quoted text clipped - 102 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Found Subform Record 0
=RowNum([Form]) - equivalent in a report? 3
intermittent problem bookmarking record to subform of itself 0
Multiple Form Instances & Me 6
Run delete query instead of report... 4
Populate main form from subform record 0
You must use the dbSeeChanges option with Open Recordset whenaccessing a SQL Server table that has a 1
subform used for changing mainform record 0

Top