Exit forms without saving

[sk88]

Below is the code for exiting without saving. It works perfectly fine. I just want to know if I can change the MSGBOX message to something like "Are you sure you want to exit without saving?" instead of its default message - "Are you sure you want to delete this record?

and also, right now, if I click NO when I am asked if I want to delete this record, it exit the form rather than staying in the same form.
But how do I make it to STAY in the same form if I click NO.

Thanks...

Private Sub Command24_Click()


On Error GoTo Command24_Click_Err
    On Error Resume Next
    DoCmd.RunCommand acCmdUndo
    DoCmd.Close
    DoCmd.OpenForm FormName:="fdlgSearchPatient"
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
 
Command24_Click_Exit:
    Exit Sub
Command24_Click_Err:
    MsgBox Error$
    Resume Command24_Click_Exit




End Sub

[robsworld78]

Use the following to ask the question.

If MsgBox("Are you sure you want to delete this record? ", vbQuestion + vbYesNo, "Delete Record") = vbNo Then Exit Sub

Using that If statement you don't need an "End If" after it. Just place that code infront of the code you want to run if yes is pressed. If no is pressed it will "Exit Sub" which means it won't run any more code below this line. If Yes is pressed it skips the "Exit Sub" and continues with any code you wrote for the button.

[sk88]

Use the following to ask the question.

If MsgBox("Are you sure you want to delete this record? ", vbQuestion + vbYesNo, "Delete Record") = vbNo Then Exit Sub

Using that If statement you don't need an "End If" after it. Just place that code infront of the code you want to run if yes is pressed. If no is pressed it will "Exit Sub" which means it won't run any more code below this line. If Yes is pressed it skips the "Exit Sub" and continues with any code you wrote for the button.

Thanks robsworld!
it worked but right now it is giving me double message.
The first one is "are you sure you want to delete.." then comes the second question which I think is part of Access - "Relationships that specify cascading deletes are about to cause 1 record(s) in this table and in related tables to be deleted. Are you sure you want to delete these records?"

How do I prevent the second question from appearing?

my code right now is :

Private Sub Command24_Click()
If MsgBox("Are you sure you want to exit without saving? ", vbQuestion + vbYesNo, "Delete Record") = vbNo Then Exit Sub

    DoCmd.RunCommand acCmdUndo
    DoCmd.Close
    DoCmd.OpenForm FormName:="fdlgSearchPatient"
    


End Sub

[robsworld78]

What are you trying to do, delete a record or undo a record?

You command is undo but your question is delete. I'm guessing you have a delete button on a form to delete a record and that record may have other records attached to it because of a relationship so its asking if it should delete all records.

Are you sure you want the other records related in other tables to be deleted as well? I don't think you can delete a main record without deleted records belonging to it.

If you do want to delete all records try the following after the question code.

CurrentDb.Execute "DELETE FROM TableName WHERE TableFieldName= " & Me.FormFieldName
DoCmd.Close

[robsworld78]

I just read properly, you want to undo changes made to a record.

For that I use a macro, its called "RunMenuCommand" then select "UndoRecord" as command.

After that add a close window event to the macro to close that form.

[sk88]

What are you trying to do, delete a record or undo a record?

You command is undo but your question is delete. I'm guessing you have a delete button on a form to delete a record and that record may have other records attached to it because of a relationship so its asking if it should delete all records.

Are you sure you want the other records related in other tables to be deleted as well? I don't think you can delete a main record without deleted records belonging to it.

If you do want to delete all records try the following after the question code.

CurrentDb.Execute "DELETE FROM TableName WHERE TableFieldName= " & Me.FormFieldName
DoCmd.Close

sorry for confusing you. I am actually finding a way to have a button which will exit the form without saving it.
I read some where that Access saves the record automatically and the only way to exit without saving is to use the UNDO command.

is it true?

[robsworld78]

yeah, that's true, I couldn't get VBA to work for undo so I used a macro and it works great. You can use the "onclick" event for the macro and "mousedown" or "mouseup" event for any extra VBA you want to run, that's what I've been doing to mix the to, not sure if its correct but it works. I'm no expert so....

[robsworld78]

Is this for an existing record edited or a new record entered and then canceled out?

[sk88]

I just read properly, you want to undo changes made to a record.

For that I use a macro, its called "RunMenuCommand" then select "UndoRecord" as command.

After that add a close window event to the macro to close that form.

this works well only for undo right?

But I want Undo and exit. Will it work?

[robsworld78]

this works well only for undo right?

But I want Undo and exit. Will it work?

Yes it will close the form as well, but you need to add another line to the macro to close the form.

[sk88]

Is this for an existing record edited or a new record entered and then canceled out?

it could be the edited record or a new record entered and then canceled out...

[robsworld78]

it could be the edited record or a new record entered and then canceled out...

The macro should handle both

[sk88]

The macro should handle both

THANKS! its working ... just one more thing I would like to add.. not sure if it is possible... right now it just give me a message box "are you sure you want to undo?" with OK button. It does not give me an option to click NO or Cancel and stay.

[robsworld78]

Create a new macro except create it using the "Create Tab" in the ribbon Then "Macro" don't use the event method. Then save the macro as "Undo" or something and anytime you need to undo a record you can use VBA to call the macro.

DoCmd.RunMacro "Undo"

In the macro only use "RunMenuCommand" with "UndoRecord" don't add the close window event to the macro or you can't use it for other forms, you can add it in the VBA.

Then use VBA to run the message box and possibly the macro depending on user input.

If MsgBox("Are you sure you want to delete this record? ", vbQuestion + vbYesNo, "Delete Record") = vbNo Then Exit Sub
DoCmd.RunMacro "MACRONAME"
DoCmd.Close

[sk88]

Create a new macro except create it using the "Create Tab" in the ribbon Then "Macro" don't use the event method. Then save the macro as "Undo" or something and anytime you need to undo a record you can use VBA to call the macro.

DoCmd.RunMacro "Undo"

In the macro only use "RunMenuCommand" with "UndoRecord" don't add the close window event to the macro or you can't use it for other forms, you can add it in the VBA.

Then use VBA to run the message box and possibly the macro depending on user input.

If MsgBox("Are you sure you want to delete this record? ", vbQuestion + vbYesNo, "Delete Record") = vbNo Then Exit Sub
DoCmd.RunMacro "MACRONAME"
DoCmd.Close

Thanks Rob! Its working but giving me the same problem like before. It shows me two messages. One is ARE YOU SURE YOU WANT TO DELETE THIS RECORD? and the next one is the default by Access I think... can I get rid of that message?