Delete Record - need Question asked first 2

[knuckelhead]

I have an icon on my form to delete a form if I need to.
Below is what someone wrote for me a long time ago.
I want to add a question before deleting the record. Like "Are you sure you want to delete this record?"

while you review the vba code below, I am open to improving it too. I do not know how to read it.
thanks. Knucklehead

=====================================
Private Sub DeleteRecord_Click()

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

End Sub
======================================

 

[hneal98]

The following should do the trick...

=====================================
Private Sub DeleteRecord_Click()

  Dim Msg, Style, Title, Help, Ctxt, Response, MyString
  Msg = "Are you sure you want to delete this record?"    'Define message.
  Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
  Title = "MsgBox Delete Reocord?"    ' Define title.
  Response = MsgBox(Msg, Style, Title)
  If Response = vbYes Then    ' User chose Yes.
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    MsgBox "Record Deleted."
  Else    ' User chose No.
    MsgBox "Record not Deleted."
    Exit funciton
  End If

End Sub
======================================

 

[SJFoust]

Unless you are using an old version of Access, you should change these to the newer RunCommand -- it's also a lot easier to understand.

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

becomes

RunCommand acCmdSelectRecord
RunCommand acCmdDeleteRecord

Ie, you are selecting the current record, then deleting the selected record.

For more info on the RunCommand options (which Microsoft *really* needs to document better), you can check out:

http://home.clara.net/tkwickenden/index.htm

For converting DoMenuItem elements in particular, check out their page at:

http://home.clara.net/tkwickenden/domenuitem.htm

 

[knuckelhead]

Thanks alot. There is probably a tweak needed since i get an error. the words "Exit funciton" was in RED so i rewrote it as Exit function. the RED went away.

but i had another error message. it revolves around the runCommand. i also tried it with the Docmd but it hung up.
so there is some tweak needed. can you do? thanks alot.
Knucklehead

===============================================
Private Sub DeleteRecord_Click()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to delete this record?" 'Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Delete Reocord?" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.

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

RunCommand acCmdSelectRecord
RunCommand acCmdDeleteRecord

MsgBox "Record Deleted."
Else ' User chose No.
MsgBox "Record not Deleted."

Exit Function
End If

End Sub
==============================================

 

[hneal98]

Sorry, that is becuase the button is a sub, not a fuction. It should be exit sub.

 

[ProgramError]

Instead of exit function try exit by itself

Ian Mayor (UK)
Program Error
There's ALWAYS more than one way to skin a cat!
But only one way to get it RIGHT!

 

[knuckelhead]

thanks guys. You both got a star and i will send a donation to the club.

i added the "SJFoust " RunCommand deal into the "hneal98" and all works now. For anyone else interested, i show the final working code below in the "ON Click event procedure" on my button.
Knucklehead

====================================
Private Sub DeleteRecord_Click()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to delete this record?" 'Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Delete Reocord?" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.

RunCommand acCmdSelectRecord
RunCommand acCmdDeleteRecord

MsgBox "Record Deleted."
Else ' User chose No.
MsgBox "Record not Deleted."

Exit Sub
End If

End Sub
=========================================

 

[mauricionava]

Hello, I try your code and it works perfectly. I was actually going to code one delete button similar to that one.
Is there a way to password-protect that button?

thanks!

 

[hneal98]

you could put a dialog box in there that would prompt for a password before the user would be alowed to delete the record.

You would set the box up similar to what is used for prompting the user whether they want to delete or not and if they don't enter the correct password, the button could be disabled.

There are many ways to do it.