By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,337 Members | 2,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,337 IT Pros & Developers. It's quick & easy.

Custom delete messages

P: 25
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?
Oct 13 '07 #1
Share this Question
Share on Google+
16 Replies


nico5038
Expert 2.5K+
P: 3,072
One way is to change the created code behind the button into:
Expand|Select|Wrap|Line Numbers
  1. IF msgbox("Sure you want to delete this record?",vbYesNo) = vbYes then
  2.     currentdb.execute ("delete * from tblX where ID=" & Me.ID
  3. end if
  4.  
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)
Oct 13 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
...
Expand|Select|Wrap|Line Numbers
  1. IF msgbox("Sure you want to delete this record?",vbYesNo) = vbYes then
  2.     currentdb.execute ("delete * from tblX where ID=" & Me.ID
  3. end if
...
Would a .Requery be required for this Nico?
Oct 13 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Good point Ade, the standard refesh rate is indeed too slow in general so the Me.Requery is advisable.

Nic;o)
Oct 13 '07 #4

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub delete_indep_Click()
  2. On Error GoTo Err_delete_indep_Click
  3.  
  4.  
  5.     DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  6.     DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
  7.  
  8. Exit_delete_indep_Click:
  9.     Exit Sub
  10.  
  11. Err_delete_indep_Click:
  12.     MsgBox Err.Description
  13.     Resume Exit_delete_indep_Click
  14.  
  15. End Sub
  16.  
How do I insert the code for the custom deleter prompt without breaking anything?
Oct 14 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 14 '07 #6

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub delete_indep_Click()
  2. On Error GoTo Err_delete_indep_Click
  3.  
  4.    If MsgBox("Sure you want to delete this record?", vbYesNo) = vbYes Then Me.Requery
  5.     currentdb.execute ("delete *from Project_Contact_Record where Contact_ID=" & Me.ID
  6. End If
  7.  
  8. Exit_delete_indep_Click:
  9.     Exit Sub
  10.  
  11. Err_delete_indep_Click:
  12.     MsgBox Err.Description
  13.     Resume Exit_delete_indep_Click
  14.  
  15. End Sub 
What did I do wrong?
Oct 15 '07 #7

NeoPa
Expert Mod 15k+
P: 31,494
Try :
Expand|Select|Wrap|Line Numbers
  1. Private Sub delete_indep_Click()
  2. On Error GoTo Err_delete_indep_Click
  3.  
  4.   If MsgBox("Sure you want to delete this record?", vbYesNo) = vbYes Then
  5.     currentdb.execute ("delete * from Project_Contact_Record where Contact_ID=" & Me.ID
  6.     Me.Requery
  7.   End If
  8.  
  9. Exit_delete_indep_Click:
  10.   Exit Sub
  11.  
  12. Err_delete_indep_Click:
  13.   MsgBox Err.Description
  14.   Resume Exit_delete_indep_Click
  15.  
  16. End Sub 
Please try to include the line number where an error occurrs when reporting a problem in future.
Oct 15 '07 #8

P: 27
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?
Oct 16 '07 #9

nico5038
Expert 2.5K+
P: 3,072
The WHERE clause should have a trailing ")":

Expand|Select|Wrap|Line Numbers
  1. currentdb.execute ("delete * from Project_Contact_Record where Contact_ID=" & Me.ID)
This all needs to be on one line!

Nic;o)
Oct 16 '07 #10

NeoPa
Expert Mod 15k+
P: 31,494
I think it needs a "Call" if it has the parentheses. The following are both usable syntax :
Expand|Select|Wrap|Line Numbers
  1. CurrentDB.Execute StringVal
  2. 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 :
Expand|Select|Wrap|Line Numbers
  1. Call CurrentDB.Execute("DELETE * FROM [Project_Contact_Record] WHERE [Contact_ID]=" & Me.ID)
Oct 16 '07 #11

P: 25
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub delete_indep_Click()
  2. On Error GoTo Err_delete_indep_Click
  3.  
  4.   If MsgBox("Sure you want to delete this record?", vbYesNo) = vbYes Then
  5.    CurrentDb.Execute ("delete * from Project_Contact_Record where Contact_ID=" & Me.ID)
  6.   Me.Requery
  7.   End If
  8.  
  9. Exit_delete_indep_Click:
  10.   Exit Sub
  11.  
  12. Err_delete_indep_Click:
  13.   MsgBox Err.Description
  14.   Resume Exit_delete_indep_Click
  15.  
  16. End Sub
Oct 17 '07 #12

P: 27
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub delete_indep_Click()
  2. On Error GoTo Err_delete_indep_Click
  3.  
  4.   If MsgBox("Sure you want to delete this record?", vbYesNo) = vbYes Then
  5.    CurrentDb.Execute ("delete * from Project_Contact_Record where Contact_ID=" & Me.ID)
  6.   Me.Requery
  7.   End If
  8.  
  9. Exit_delete_indep_Click:
  10.   Exit Sub
  11.  
  12. Err_delete_indep_Click:
  13.   MsgBox Err.Description
  14.   Resume Exit_delete_indep_Click
  15.  
  16. End Sub


I have just gotten the correct syntax (after too long of troubleshooting!).
Line 5 should be (all on one line):
Expand|Select|Wrap|Line Numbers
  1. 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 :)
Oct 17 '07 #13

P: 25
I still got the error. :(
Oct 17 '07 #14

NeoPa
Expert Mod 15k+
P: 31,494
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.
Oct 17 '07 #15

P: 27
@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.
Oh, ok. My equivalent to Me.ID is a string, so maybe thats why I needed the quotes?
Oct 18 '07 #16

NeoPa
Expert Mod 15k+
P: 31,494
I guessed that was the case. Yes it certainly would be why, but you can see that the OP almost certainly didn't have it as a string. ID's are rarely strings anyway, but from the earlier posts it's almost certainly not true for the OP at least.
You'll see from my earlier post also, that there are two "correct" versions. Neither is wrong but people have their preferences.
Oct 18 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.