Error when cancelling BeforeUpdate event

[Steve Happ]

I give the user the option to NOT save a new record if they've changed their
mind by using the following code in the BeforeUpdate event of the form:
If Me.NewRecord Then
If MsgBox("Do you want to save this new document?", vbYesNo) = vbNo Then
Cancel = True
MsgBox "Update was cancelled", vbOKOnly
End If
End If

So, if the user begins to fill in a new form, changes their mind and closes
the form, they will be questioned and can hit NO. However, when they hit NO,
an error message comes up that says:
You can't save this record at this time!
Registrations may have encountered an error while trying to save a record.
If you close this object now, the data changes you made will be lost.
Do you want to close the database object anyway?
YES / NO

How do I keep this error message from coming up?

Thanks

Steve

 

[Graham Mandeno]

Hi Steve

This is a tricky problem which occurs only when you are closing the form
when it is dirty.

The only place to trap this error is in the Form_Error event procedure. The
following code will suppress that second dialog box and simply undo the
changes so the form closes without fuss.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2169 Then Response = acDataErrContinue
End Sub

 

[Steve Happ]

Graham:

Thanks so much. That did it!

Steve

Hi Steve

This is a tricky problem which occurs only when you are closing the form
when it is dirty.

The only place to trap this error is in the Form_Error event procedure. The
following code will suppress that second dialog box and simply undo the
changes so the form closes without fuss.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2169 Then Response = acDataErrContinue
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I give the user the option to NOT save a new record if they've changed
their
mind by using the following code in the BeforeUpdate event of the form:
If Me.NewRecord Then
If MsgBox("Do you want to save this new document?", vbYesNo) = vbNo
Then
Cancel = True
MsgBox "Update was cancelled", vbOKOnly
End If
End If

So, if the user begins to fill in a new form, changes their mind and
closes
the form, they will be questioned and can hit NO. However, when they hit
NO,
an error message comes up that says:
You can't save this record at this time!
Registrations may have encountered an error while trying to save a record.
If you close this object now, the data changes you made will be lost.
Do you want to close the database object anyway?
YES / NO

How do I keep this error message from coming up?

Thanks

Steve

 

[Reis Quarteu]

Hi Graham and all the other folks.

I have been dealing with a similar problem as Steve's, but your answer
helped me a lot. Thank you very much!

Nevertheless, I explored a little further, and noticed that you have written
"This is a tricky problem which occurs only when you are closing the form
when it is dirty". The word "dirty" called my attention, so I went to the
Access online help, and found out the Dirty property is read/write. Do you
think that setting False to the Dirty property would be a better solution to
this kind of problem than just ignoring the error, as you suggested us to do?
Because we might all agree that setting the acDataErrContinue constant to the
Form_Error's Response parameter is just hiding the problem away, don't you
think so?

Since I am performing maintenance on an Access-based application that has
reported the occurrence of this run-time error while it has been tested by
the final users, I am not able to reproduce this error on my machine.

Thank you very much in advance!

Greetings from Portugal.
Reis Quarteu

 

[Dirk Goldgar]

Hi Graham and all the other folks.

I have been dealing with a similar problem as Steve's, but your answer
helped me a lot. Thank you very much!

Nevertheless, I explored a little further, and noticed that you have
written "This is a tricky problem which occurs only when you are
closing the form when it is dirty". The word "dirty" called my
attention, so I went to the Access online help, and found out the
Dirty property is read/write. Do you think that setting False to the
Dirty property would be a better solution to this kind of problem
than just ignoring the error, as you suggested us to do?

Unfortunately for this idea -- though rather handy in other
circumstances -- setting the form's Dirty property to False actually
forces the Access to save the dirty record. In other words, the form is
made "not Dirty" by saving the record. So you can't prevent the saving
of the record or magically render it "not dirty" (without saving it) by
just clearing the Dirty property.

 

[Reis Quarteu]

Hi Dirk and everybody!

Yes, you're right. I tested it now and could confirm this. Maybe should we
UNDO the changes that have been made to the records so that the run-time
error we've been discussing about can be better corrected?

Thank you for your kind help!
Reis Quarteu

 

[Dirk Goldgar]

Hi Dirk and everybody!

Yes, you're right. I tested it now and could confirm this. Maybe
should we UNDO the changes that have been made to the records so that
the run-time error we've been discussing about can be better
corrected?

For some reason, I don't see any messages in this thread earlier than
the one I replied to, so I don't have enough background information to
answer your question reliably. If you want to close a form and not save
the user's changes to the current record, then it makes sense to undo
those changes. You could do that with the simple statement

Me.Undo ' if run from the form's code module

executed before the line of code that closes the form.

 

[Reis Quarteu]

Hi, Dirk!

This discussion began at July 7th, 2005 with a message written by Steve Happ:

************* Start of Message *****************************

"I give the user the option to NOT save a new record if they've changed their
mind by using the following code in the BeforeUpdate event of the form:
If Me.NewRecord Then
If MsgBox("Do you want to save this new document?", vbYesNo) = vbNo Then
Cancel = True
MsgBox "Update was cancelled", vbOKOnly
End If
End If

So, if the user begins to fill in a new form, changes their mind and closes
the form, they will be questioned and can hit NO. However, when they hit NO,
an error message comes up that says:
You can't save this record at this time!
Registrations may have encountered an error while trying to save a record.
If you close this object now, the data changes you made will be lost.
Do you want to close the database object anyway?
YES / NO

How do I keep this error message from coming up?

Thanks

Steve

************* End of Message *****************************

In the same day, Graham Mandeno answered:

************* Start of Message *****************************

Hi Steve

This is a tricky problem which occurs only when you are closing the form
when it is dirty.

The only place to trap this error is in the Form_Error event procedure. The
following code will suppress that second dialog box and simply undo the
changes so the form closes without fuss.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2169 Then Response = acDataErrContinue
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

************* End of Message *****************************

Then, I reasked yesterday:

************* Start of Message *****************************

Hi Graham and all the other folks.

I have been dealing with a similar problem as Steve's, but your answer
helped me a lot. Thank you very much!

Nevertheless, I explored a little further, and noticed that you have written
"This is a tricky problem which occurs only when you are closing the form
when it is dirty". The word "dirty" called my attention, so I went to the
Access online help, and found out the Dirty property is read/write. Do you
think that setting False to the Dirty property would be a better solution to
this kind of problem than just ignoring the error, as you suggested us to do?
Because we might all agree that setting the acDataErrContinue constant to the
Form_Error's Response parameter is just hiding the problem away, don't you
think so?

Since I am performing maintenance on an Access-based application that has
reported the occurrence of this run-time error while it has been tested by
the final users, I am not able to reproduce this error on my machine.

Thank you very much in advance!

Greetings from Portugal.
Reis Quarteu

************* End of Message *****************************

Hope this can help you.

Best regards.
Reis Quarteu

 

[Dirk Goldgar]

Hi, Dirk!

This discussion began at July 7th, 2005

That explains why I couldn't find the start of the thread.

with a message written by
Steve Happ:

************* Start of Message *****************************

"I give the user the option to NOT save a new record if they've
changed their mind by using the following code in the BeforeUpdate
event of the form: If Me.NewRecord Then
If MsgBox("Do you want to save this new document?", vbYesNo) =
vbNo Then Cancel = True
MsgBox "Update was cancelled", vbOKOnly
End If
End If

So, if the user begins to fill in a new form, changes their mind and
closes the form, they will be questioned and can hit NO. However,
when they hit NO, an error message comes up that says:
You can't save this record at this time!
Registrations may have encountered an error while trying to save a
record. If you close this object now, the data changes you made will
be lost.
Do you want to close the database object anyway?
YES / NO

How do I keep this error message from coming up?

Thanks

Steve

************* End of Message *****************************

In the same day, Graham Mandeno answered:

************* Start of Message *****************************

Hi Steve

This is a tricky problem which occurs only when you are closing the
form when it is dirty.

The only place to trap this error is in the Form_Error event
procedure. The following code will suppress that second dialog box
and simply undo the changes so the form closes without fuss.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2169 Then Response = acDataErrContinue
End Sub

If the attempt to save the record is forced by the closing of the form,
then Graham's right: the only way to avoid the error message is to trap
the error in the form's Error event. On the other hand, if the attempt
to save the form is caused by some other action -- a move to a new
record, or an explicit request to save the record, then adding "Me.Undo"
to the code in the BeforeUpdate event should prevent it:

If Me.NewRecord Then
If MsgBox( _
"Do you want to save this new document?", _
vbYesNo) _
= vbNo _
Then
Cancel = True
Me.Undo
MsgBox "Update was cancelled", vbOKOnly
End If
End If

If you give the user a "Close" or "Save" button to use instead of just
hitting the little "X" button in the title bar, then you can reduce the
chance of the user's seeing the ugly error message.

 

[Joaquin]

I've also struggled with this problem. I have used a similar solution as
suggested in the thread below by using a Form_Error event (code below). Yet I
still have a problem with this approach. If a user cancels the Close form
event, this approach seems to cause their unsaved edits thus far to be wiped
clean and a blank form is presented.

I would prefer to keep the record dirty so that the user can complete their
edits. Can anyone Help with this?

Thanks
__________

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

'Prompt to ask whether to save changes before leaving record
intMsgResult = MsgBox("Do you want to save changes to this comment?",
vbYesNoCancel, "QA Application")

'No - Close without saving changes
If (intMsgResult = vbNo) Then
Me.Undo
GoTo Exit_Form_BeforeUpdate 'To skip date modification

'Cancel - Cancel Form Close and return to the current record.
ElseIf (intMsgResult = vbCancel) Then

'Restore unsaved edits
'Attempted to save dirty field to a variable - Does not work
Me!tbxCommentText = edtCommentText

'Cancel Close event and Flag variable for the Form_Unload &
Form_Error event handlers
Cancel = True
blnCancelUpdate = True

'Skip date modification
GoTo Exit_Form_BeforeUpdate

'Yes - Test for Null values in required fields & Update Last Modified
time stamp
ElseIf (intMsgResult = vbYes) Then
If cbxCategory = Null Or cbxSection = Null Or tbxCommentText =
Null Then
MsgBox "You can not save this Recurring comment" & vbCrLf &
"because a required field is blank."
Cancel = True
blnCancelUpdate = True
End If
End If

'Update Modification Date
Me![LAST_MODIFIED] = Now

Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Number & " - " & Err.Description _
& vbCrLf & vbCrLf & "Error prompted by: Err_Form_BeforeUpdate"
Resume Exit_Form_BeforeUpdate

End Sub
__________

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Err_Form_Unload

'Test if Cancel was selected on Form_BeforeUpdate event
If blnCancelUpdate = True Then
Cancel = True

Exit_Form_Unload:
Exit Sub

Err_Form_Unload:
MsgBox Err.Number & " - " & Err.Description _
& vbCrLf & vbCrLf & "Error prompted by: Err_Form_Unload"
Resume Exit_Form_Unload

End Sub
__________

Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

'Trap Access save record error due to Dirty record caused by Cancel on
Form_BeforeUpdate event

Const cst_lngCannotSaveRecordErrNo As Long = 2169

'Test that Err.Number = 2169 & Close Event was cancelled by user
If DataErr = cst_lngCannotSaveRecordErrNo And blnCancelUpdate = True Then
Response = acDataErrContinue
End If

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Number & " - " & Err.Description _
& vbCrLf & vbCrLf & "Error prompted by: Err_Form_Error"
Resume Exit_Form_Error

End Sub
__________________________________________________________

 

[Joaquin]

Sorry about the bad word wrap in my code below. I'm new to this.

Cheers.

I've also struggled with this problem. I have used a similar solution as
suggested in the thread below by using a Form_Error event (code below). Yet I
still have a problem with this approach. If a user cancels the Close form
event, this approach seems to cause their unsaved edits thus far to be wiped
clean and a blank form is presented.

I would prefer to keep the record dirty so that the user can complete their
edits. Can anyone Help with this?

Thanks
__________

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

'Prompt to ask whether to save changes before leaving record
intMsgResult = MsgBox("Do you want to save changes to this comment?",
vbYesNoCancel, "QA Application")

'No - Close without saving changes
If (intMsgResult = vbNo) Then
Me.Undo
GoTo Exit_Form_BeforeUpdate 'To skip date modification

'Cancel - Cancel Form Close and return to the current record.
ElseIf (intMsgResult = vbCancel) Then

'Restore unsaved edits
'Attempted to save dirty field to a variable - Does not work
Me!tbxCommentText = edtCommentText

'Cancel Close event and Flag variable for the Form_Unload &
Form_Error event handlers
Cancel = True
blnCancelUpdate = True

'Skip date modification
GoTo Exit_Form_BeforeUpdate

'Yes - Test for Null values in required fields & Update Last Modified
time stamp
ElseIf (intMsgResult = vbYes) Then
If cbxCategory = Null Or cbxSection = Null Or tbxCommentText =
Null Then
MsgBox "You can not save this Recurring comment" & vbCrLf &
"because a required field is blank."
Cancel = True
blnCancelUpdate = True
End If
End If

'Update Modification Date
Me![LAST_MODIFIED] = Now

Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Number & " - " & Err.Description _
& vbCrLf & vbCrLf & "Error prompted by: Err_Form_BeforeUpdate"
Resume Exit_Form_BeforeUpdate

End Sub
__________

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Err_Form_Unload

'Test if Cancel was selected on Form_BeforeUpdate event
If blnCancelUpdate = True Then
Cancel = True

Exit_Form_Unload:
Exit Sub

Err_Form_Unload:
MsgBox Err.Number & " - " & Err.Description _
& vbCrLf & vbCrLf & "Error prompted by: Err_Form_Unload"
Resume Exit_Form_Unload

End Sub
__________

Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

'Trap Access save record error due to Dirty record caused by Cancel on
Form_BeforeUpdate event

Const cst_lngCannotSaveRecordErrNo As Long = 2169

'Test that Err.Number = 2169 & Close Event was cancelled by user
If DataErr = cst_lngCannotSaveRecordErrNo And blnCancelUpdate = True Then
Response = acDataErrContinue
End If

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Number & " - " & Err.Description _
& vbCrLf & vbCrLf & "Error prompted by: Err_Form_Error"
Resume Exit_Form_Error

End Sub
__________________________________________________________

That explains why I couldn't find the start of the thread.


If the attempt to save the record is forced by the closing of the form,
then Graham's right: the only way to avoid the error message is to trap
the error in the form's Error event. On the other hand, if the attempt
to save the form is caused by some other action -- a move to a new
record, or an explicit request to save the record, then adding "Me.Undo"
to the code in the BeforeUpdate event should prevent it:

If Me.NewRecord Then
If MsgBox( _
"Do you want to save this new document?", _
vbYesNo) _
= vbNo _
Then
Cancel = True
Me.Undo
MsgBox "Update was cancelled", vbOKOnly
End If
End If

If you give the user a "Close" or "Save" button to use instead of just
hitting the little "X" button in the title bar, then you can reduce the
chance of the user's seeing the ugly error message.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

You're resetting Me!tbxCommentText to edtCommentText. Where have you
declared that variable and set its value?

BTW, even though you've issued the Me.Undo if the user answers No, you still
need to set Cancel = True

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've also struggled with this problem. I have used a similar solution as
suggested in the thread below by using a Form_Error event (code below).
Yet I
still have a problem with this approach. If a user cancels the Close form
event, this approach seems to cause their unsaved edits thus far to be
wiped
clean and a blank form is presented.

I would prefer to keep the record dirty so that the user can complete
their
edits. Can anyone Help with this?

Thanks
__________

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

'Prompt to ask whether to save changes before leaving record
intMsgResult = MsgBox("Do you want to save changes to this comment?",
vbYesNoCancel, "QA Application")

'No - Close without saving changes
If (intMsgResult = vbNo) Then
Me.Undo
GoTo Exit_Form_BeforeUpdate 'To skip date modification

'Cancel - Cancel Form Close and return to the current record.
ElseIf (intMsgResult = vbCancel) Then

'Restore unsaved edits
'Attempted to save dirty field to a variable - Does not
work
Me!tbxCommentText = edtCommentText

'Cancel Close event and Flag variable for the Form_Unload &
Form_Error event handlers
Cancel = True
blnCancelUpdate = True

'Skip date modification
GoTo Exit_Form_BeforeUpdate

'Yes - Test for Null values in required fields & Update Last Modified
time stamp
ElseIf (intMsgResult = vbYes) Then
If cbxCategory = Null Or cbxSection = Null Or tbxCommentText =
Null Then
MsgBox "You can not save this Recurring comment" & vbCrLf &
"because a required field is blank."
Cancel = True
blnCancelUpdate = True
End If
End If

'Update Modification Date
Me![LAST_MODIFIED] = Now

Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Number & " - " & Err.Description _
& vbCrLf & vbCrLf & "Error prompted by: Err_Form_BeforeUpdate"
Resume Exit_Form_BeforeUpdate

End Sub
__________

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Err_Form_Unload

'Test if Cancel was selected on Form_BeforeUpdate event
If blnCancelUpdate = True Then
Cancel = True

Exit_Form_Unload:
Exit Sub

Err_Form_Unload:
MsgBox Err.Number & " - " & Err.Description _
& vbCrLf & vbCrLf & "Error prompted by: Err_Form_Unload"
Resume Exit_Form_Unload

End Sub
__________

Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

'Trap Access save record error due to Dirty record caused by Cancel on
Form_BeforeUpdate event

Const cst_lngCannotSaveRecordErrNo As Long = 2169

'Test that Err.Number = 2169 & Close Event was cancelled by user
If DataErr = cst_lngCannotSaveRecordErrNo And blnCancelUpdate = True
Then
Response = acDataErrContinue
End If

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Number & " - " & Err.Description _
& vbCrLf & vbCrLf & "Error prompted by: Err_Form_Error"
Resume Exit_Form_Error

End Sub
__________________________________________________________

That explains why I couldn't find the start of the thread.


If the attempt to save the record is forced by the closing of the form,
then Graham's right: the only way to avoid the error message is to trap
the error in the form's Error event. On the other hand, if the attempt
to save the form is caused by some other action -- a move to a new
record, or an explicit request to save the record, then adding "Me.Undo"
to the code in the BeforeUpdate event should prevent it:

If Me.NewRecord Then
If MsgBox( _
"Do you want to save this new document?", _
vbYesNo) _
= vbNo _
Then
Cancel = True
Me.Undo
MsgBox "Update was cancelled", vbOKOnly
End If
End If

If you give the user a "Close" or "Save" button to use instead of just
hitting the little "X" button in the title bar, then you can reduce the
chance of the user's seeing the ugly error message.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)