Canceling Close

[JamesJoey]

I'm using the following code to ask whether to save changes to a record or not:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Dirty Then
Dim msg, style, TITLE, Response
msg = "Record(s) have been added or changed." & vbCrLf & "Save the Changes?"
style = vbYesNo + vbQuestion
TITLE = "Data Change Confirmation"
Response = MsgBox(msg, style, TITLE)
If Response = vbYes Then
Else
Me.Undo
End If
End If
End Sub

I want to add a 3rd option to cancel the close and return to make more changes to the form data.
Currently I don't call this form a command button. The code triggers after I click the forms close (x) button. But I thing I'll need to call this from a command button.
I've found several example on the internet but not haven't tried any of them because they seem to be very different.

Any help will be appreciated,
James

 

[CJ_London]

Hi,

Please put code into the code box, makes it easier to read - use the advanced editor to do this.

With regards your problem

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Dirty Then
Dim msg, style, TITLE, Response
msg = "Record(s) have been added or changed." & vbCrLf & "Save the Changes?"
style = vbYesNo + vbQuestion
TITLE = "Data Change Confirmation"
Response = MsgBox(msg, style, TITLE) 
If Response = vbYes Then
Else
Me.Undo
[COLOR=red]cancel=true[/COLOR]
End If
End If
End Sub

With regards putting this on a button I would simply have the button close the form which will then trigger the before update event

 

[JamesJoey]

The same thing occurs. The form closes whether I click No or Yes. I want a 'Cancel' button in the message box to cancel the close and return to the form for editing.

 

[boblarson]

If you don't close the form with a button you need to cancel the close in the form's ON UNLOAD event to keep it from happening. So, you need to put a variable in to a standard module.

Public blnNoClose As Boolean


And then in the Before Update you need to issue

Cancel = True
blnNoClose = True

And in the Form's Unload Event you use

Cancel = blnNoClose

 

[JamesJoey]

I have no problem with closing the form with a button. What ever will be easiest for me.
Will it be the same closing with a button.
Should I run the code I pasted to the On Click of this button or leave it in the Before Update?

 

[CJ_London]

You could always have the button try to save the record rather than close the form - but since the user is positively clicking the button to save the message should be turned round to 'Record Not Changed'

Hope that makes sense - it's a bit late here

 

[JamesJoey]

Never thought of that.
I'll look into it.

 

[JamesJoey]

Thanks for the input.
But, I found that I can use 2 command buttons. One to save the record changes and one to close the form. The close button is disabled when the form becomes dirty. I needed to turn off the context menu and the form's close (x) button. But it works fine.

Thanks again,
James

 

[JamesJoey]

I don't code that much so I need to ask a couple questions here.

Should the code I use to ask if the record needs to be saved remain the same?
When I define the variable where should it go? Should I create a new function. Should it be Public or Private? I'm assuming it should be a Function as opposed to a Sub?

 

[JamesJoey]

There are lots of ways to close a form. You will still have a problem unless you trap the events correctly.

I see.
The variable declaration goes in the declaration section of the form module. If I'm using the proper terminology. And the rest is self explanatory. I'm still a bit confused about the code I use to ask to save the record. This should still go in the Before Update?

 

[JamesJoey]

The form isn't closing but, when I click No or Cancel I get and error message:

"You can't save this record at this time.
'mydatabasename' may have encountered an error while trying to save a record.
If you this object now, changes you made will be lost.
Do you want to close the database object anyway?"

 

[spikepl]

Did you follow the recipe in #8 and #13 to the letter? I suspect not, since it tells you it wants to close, but that was supposed to be canceled by the code.

 

[JamesJoey]

I double and triple checked.
My way only has 1 or 2 ways top close the form. I have the Control Box and Close Button set to No, the form is Popup and Model and the form has no Shortcut Menu.
The only way to close the form is to go to Task Manager and end the Access app.

 

[JamesJoey]

I still get the error messages that I referred to. I keep getting told can's save the record at this time. Don't have any idea ho to fix the error(s).
Again I rechecked and I have everything as you stated.
One thing though. I really only require 2 command buttons Save and Close. Save saves the record and stays on the record and the close button, of course closes the form. Or Instead of a custom close button I can use the Form's Close (x) button.

 

[JamesJoey]

When I click No I return to the form but lose the ability to close the form at all. The only way to close the form is if I click Yes. If I click Cancel I get the error "Can't save the record at this time..."

 

[JamesJoey]

Now when I click either the No or the Cancel I get the following:

"You can't save this record at this time.
'mydatabasename' may have encountered an error while trying to save a record.
If you this object now, changes you made will be lost.
Do you want to close the database object anyway?"

 

[JamesJoey]

I guess if I breath wrong on my Access db the forms get corrupt.
I only have 1 instance of the db open.
Here is a ZIP of the form and table.

View attachment PreventClose.zip

 

[JamesJoey]

I created a new form and pasted the code but got the same results.

 

[JamesJoey]

No pending updates.

Maybe I'll just go with "My" way, I don't get any error messages.

 

[spikepl]

When I try it reacts like you say in #21.

Have tinkered a bit - see if you like this one better. Don't know why it does an Undo, when you press Cancel, but...