469,071 Members | 1,922 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,071 developers. It's quick & easy.

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

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
Apr 15 '13 #1
13 2930
Seth Schrock
2,957 Expert 2GB
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.
Apr 15 '13 #2
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelete_Click()
  2. If Me.Currentrecord = Me.Recordset.Recordcount then
  3. Msgbox "Are you sure you want to delete this record?", vbCritical, "Deleting Record", vbYesNo
  4. If vbNo then
  5. DoCmd.CancelEvent
  6. Else
  7. If vbYes then
  8. Recordset.Delete
  9. DoCmd.GoToRecord, ,acPrevious
  10. End If
  11. End If
  12. End Sub
  13.  
Apr 15 '13 #3
NeoPa
32,154 Expert Mod 16PB
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 :

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelete_Click()
  2.     Dim blnLast As Boolean
  3.  
  4.     'MsgBox call must return the value in order to be checked.
  5.     'If user says no then cancelling is not required.  It is only required 
  6.     '  that the deletion command is not invoked.
  7.     If MsgBox(Prompt:="Are you sure you want to delete this record?" _
  8.             , Buttons:=vbYesNo Or vbQuestion _
  9.             , Title:="Deleting Record") = vbNo Then Exit Sub
  10.     'At this point it's clear we need to delete the record.
  11.     'Using With Me is a more efficient approach to object usage.
  12.     With Me
  13.         'Remember for later if record is last.
  14.         blnLast = (.CurrentRecord = .Recordset.RecordCount)
  15.         Call .Recordset.Delete
  16.         'Only step back if deleted record was the last.
  17.         If blnLast Then Call DoCmd.GoToRecord(Record:=acPrevious)
  18.     End With
  19. 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.
Apr 15 '13 #4
Seth Schrock
2,957 Expert 2GB
@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.
Apr 15 '13 #5
ADezii
8,800 Expert 8TB
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.
Apr 15 '13 #6
NeoPa
32,154 Expert Mod 16PB
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.
Apr 16 '13 #7
Ok, I used the following code and got an error saying End With without with.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelete_Click()
  2.  
  3. Dim blnLast As Boolean
  4.  
  5. 'Msgbox call must return the value in order to be checked
  6. If MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deleting Record") Then
  7. If vbNo then
  8. Exit sub
  9.  
  10. With me
  11.   blnLast=(.CurrentRecord=.Recordset.RecordCount)
  12.   Call .Recordset.Delete
  13.   If blnLast Then
  14.   DoCmd.GoToRecord, ,acPrevious
  15.  End With
  16. End Sub
Apr 16 '13 #8
Rabbit
12,516 Expert Mod 8TB
You're missing an End If.
Apr 16 '13 #9
I've added the End If. Now there are no errors but it is also not deleting the record.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelete_Click() 
  2.  
  3. Dim blnLast As Boolean 
  4.  
  5. 'Msgbox call must return the value in order to be checked 
  6. If MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deleting Record") = vbNo then 
  7. Exit sub 
  8.  
  9. With me 
  10.   blnLast=(.CurrentRecord=.Recordset.RecordCount) 
  11.   Call .Recordset.Delete 
  12.   If blnLast Then 
  13.   DoCmd.GoToRecord, ,acPrevious 
  14. End If
  15.  End With
  16. End If 
  17. End Sub 
  18.  
Apr 16 '13 #10
Seth Schrock
2,957 Expert 2GB
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.
Apr 16 '13 #11
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.
Apr 16 '13 #12
Rabbit
12,516 Expert Mod 8TB
Your End If for your first If statement is in the wrong place. Move line 16 to line 8.
Apr 16 '13 #13
NeoPa
32,154 Expert Mod 16PB
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.
Apr 17 '13 #14

Post your reply

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

Similar topics

3 posts views Thread by Steve Lefevre | last post: by
3 posts views Thread by NateDawg | last post: by
2 posts views Thread by jim Bob | last post: by
5 posts views Thread by Jeff User | last post: by
1 post views Thread by JasonK | last post: by
7 posts views Thread by ITAutobot25 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.