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.

 

[Pat Hartman]

FYI - if you are "in" the BeforeUpdate event, you are there because the current record is dirty and it is the process of being saved. There is no need to check the Dirty property since the answer is always true.

To control closing the form, you need a global variable as was already mentioned and you need code in several events.

Define the variable

Public bPreventClose As Boolean

In the Form's Current event

bPreventClose = False

In the Form's Dirty event

bPreventClose = True

In the Form's AfterUpdate event

bPreventClose = False

In the Form's Unload event

If bPreventClose = True
  Cancel = True
End If

 

[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

 

[Pat Hartman]

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

 

[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?

 

[Pat Hartman]

Yes. Your save? prompt goes in the Form's BeforeUpdate event because that is where you will cancel the update and back out the changes if the user says no.

Dim UserResp As Integer
    UserResp = MsgBox("Press Yes to save.  No to discard changes or Cancel to return to form without saving.", vbYesNoCancel) 
    Select Case UserResp
        Case vbNo
            Cancel = True
            Me.Undo
            Exit Sub
        Case vbCancel
            Cancel = True
            Exit Sub
    End Select

 

[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.

 

[Pat Hartman]

Forget about "your" way to close the form. You will be chasing the close until the cows come home and will still miss situations. Rather than attempting to control the close action, all you have to do is sit in the right place (the Unload event) and catch it (with bPreventClose) when it happens.

You need code in the appropriate form level events to cancel the unload event or allow it to proceed and the only way you know what to do is by leaving yourself a note in the guise of the boolean that I named bPreventClose.

 

[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..."

 

[Pat Hartman]

I left out the reset for the boolean. Anytime you "undo" the update, you need to set the boolean back to False since the record is no longer dirty.

Dim UserResp As Integer
    UserResp = MsgBox("Press Yes to save.  No to discard changes or Cancel to return to form without saving.", vbYesNoCancel) 
    Select Case UserResp
        Case vbNo
            Cancel = True
            Me.Undo
            bPreventClose = False              <<<<<<<<<<<<<<<
            Exit Sub
        Case vbCancel
            Cancel = True
            Exit Sub
    End Select

If that doesn't resolve the problem, post your database (after removing anything sensitive, compacting, and zipping) and someone will probably find the problem.