Custom delete messages

[Thirdworld]

I've created a form and used the wizard to create a delete button. Upon testing, I noticed that the warning message would be very confusing for some users. How can I create a custom, user friendly warning message that comes up once the button is pressed?

[nico5038]

One way is to change the created code behind the button into:

IF msgbox("Sure you want to delete this record?",vbYesNo) = vbYes then
    currentdb.execute ("delete * from tblX where ID=" & Me.ID
end if

This will show the message with a Yes/No popup and when Yes is clicked the row with the ID from the form is deleted "silently".
Just change the tblX and ID into the table and the PrimaryKey fieldname.

Nic;o)

[NeoPa]

...

IF msgbox("Sure you want to delete this record?",vbYesNo) = vbYes then
    currentdb.execute ("delete * from tblX where ID=" & Me.ID
end if

...

Would a .Requery be required for this Nico?

[nico5038]

Good point Ade, the standard refesh rate is indeed too slow in general so the Me.Requery is advisable.

Nic;o)

[Thirdworld]

Ok, I went to insert the code for the delete button and found this:

Private Sub delete_indep_Click()
On Error GoTo Err_delete_indep_Click


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

Exit_delete_indep_Click:
    Exit Sub

Err_delete_indep_Click:
    MsgBox Err.Description
    Resume Exit_delete_indep_Click
    
End Sub

How do I insert the code for the custom deleter prompt without breaking anything?

[nico5038]

Just remove lines 5 and 6 and replace them with the code I proposed.
Add within the Then branch:
Me.Requery
after the delete statement.

Nic;o)

[Thirdworld]

I tried the code with your suggestions and got a syntax error. Here is what I did:

Private Sub delete_indep_Click()
On Error GoTo Err_delete_indep_Click
  
   If MsgBox("Sure you want to delete this record?", vbYesNo) = vbYes Then Me.Requery
    currentdb.execute ("delete *from Project_Contact_Record where Contact_ID=" & Me.ID
End If
 
Exit_delete_indep_Click:
    Exit Sub
 
Err_delete_indep_Click:
    MsgBox Err.Description
    Resume Exit_delete_indep_Click
    
End Sub

What did I do wrong?

[NeoPa]

Try :

Private Sub delete_indep_Click()
On Error GoTo Err_delete_indep_Click
  
  If MsgBox("Sure you want to delete this record?", vbYesNo) = vbYes Then
    currentdb.execute ("delete * from Project_Contact_Record where Contact_ID=" & Me.ID
    Me.Requery
  End If
 
Exit_delete_indep_Click:
  Exit Sub
 
Err_delete_indep_Click:
  MsgBox Err.Description
  Resume Exit_delete_indep_Click
    
End Sub

Please try to include the line number where an error occurrs when reporting a problem in future.

[blakerrr]

Hi everyone,
I am also in need of this 'silent' delete code, but I also get an error stating "Syntax error in query. Incomplete query clause."
No line number is given for the error.

Any syntax suggestions?

[nico5038]

The WHERE clause should have a trailing ")":

currentdb.execute ("delete * from Project_Contact_Record where Contact_ID=" & Me.ID)

This all needs to be on one line!

Nic;o)

[NeoPa]

I think it needs a "Call" if it has the parentheses. The following are both usable syntax :

CurrentDB.Execute StringVal
Call CurrentDB.Execute(StringVal)

I usually use the latter to express explicitly that, if it is a function, I'm dropping the returned value anyway. In this case that particular line (in my preferred format) would be :

Call CurrentDB.Execute("DELETE * FROM [Project_Contact_Record] WHERE [Contact_ID]=" & Me.ID)

[Thirdworld]

I tried the code again with the changes and got "Compile error: Method or data not found." The cursor stands on line 5.

Here's the code I used.

Private Sub delete_indep_Click()
On Error GoTo Err_delete_indep_Click
  
  If MsgBox("Sure you want to delete this record?", vbYesNo) = vbYes Then
   CurrentDb.Execute ("delete * from Project_Contact_Record where Contact_ID=" & Me.ID)
  Me.Requery
  End If
 
Exit_delete_indep_Click:
  Exit Sub
 
Err_delete_indep_Click:
  MsgBox Err.Description
  Resume Exit_delete_indep_Click
    
End Sub

[blakerrr]

I tried the code again with the changes and got "Compile error: Method or data not found." The cursor stands on line 5.

Here's the code I used.

Private Sub delete_indep_Click()
On Error GoTo Err_delete_indep_Click
  
  If MsgBox("Sure you want to delete this record?", vbYesNo) = vbYes Then
   CurrentDb.Execute ("delete * from Project_Contact_Record where Contact_ID=" & Me.ID)
  Me.Requery
  End If
 
Exit_delete_indep_Click:
  Exit Sub
 
Err_delete_indep_Click:
  MsgBox Err.Description
  Resume Exit_delete_indep_Click
    
End Sub

I have just gotten the correct syntax (after too long of troubleshooting !).
Line 5 should be (all on one line):

CurrentDb.Execute "DELETE * FROM Project_Contact_Record WHERE Contact_ID = '" & Me!ID & "'"

If the table Project_Contact _Record is actually three seperate words, it needs to be enclosed in square brackets. ie. [Project Contact Record].

I hope this works for you :)

[Thirdworld]

I still got the error. :(

[NeoPa]

I still got the error. :(

You'd better post what you've changed your code to I suggest.
@Blakerrr
You would have saved yourself some time if you'd read my last post #11, which actually gives the correct syntax. Your version is also correct (syntactically) , but you added quotes (') around Me.ID which (from what we've seen so far) is a numeric value and therefore doesn't require them.