Why am I getting an error when trying to cancel my form's BeforeUpdate event?

[Seth Schrock]

I am getting an error when trying to cancel my form's BeforeUpdate event. It says:
You can't save this record at this time.
ACH Manager 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?
Here is my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.PINVerified = False Then
    Cancel = True
    MsgBox ("This transaction is not complete and changes will be canceleld.")
   
End If

End Sub

When I commented out the Cancel = True, then there was no problem. Of course there was a problem saving the record. I CANCELLED the saving of the record. What is going wrong?

[NeoPa]

It sounds like the form is being closed rather than the record simply being saved Seth. When you set Cancel = True that ensures that whatever code called for the save will get a failure return. It's very similar to the NoData event of a report. It's standard practice to set Cancel = True in the event handler, yet when this is done the calling code is told that an error occurred. Confusing until you understand and prepare for it.

[Seth Schrock]

I guess I did miss giving the important detail of what triggered the event: clicking on the close button (the default one, not a custom one). If it helps, this is related to my previous question How do I stop a form from closing from the BeforeUpdate event. I was going to post this in that thread, but decided that it would be better on its own.

I did a web search to make sure I was doing it right and found it done as you describe in the event handler. So what do I need to do to prepare for it? Would DoCmd.RunComman d acCmdUndo work better?

[NeoPa]

That depends on what you actually want to happen - in all possible circumstances. One possibility is to undo any changes, but that isn't the only possible option. We need a better understanding of your requirements to guide you where you need to go.

[Seth Schrock]

I just want to undo all changes made to the record so that they aren't saved when the form closes.

[zmbd]

Seth,
Go back to Many-to-Many-to-Many relationship problem where we were talking about the many-to-many. I posted an example database there where the form wouldn't allow very many edits nor a new record creation directly to the bound record set. Instead I had in the footer a set of controls to handle the creation.

You asked for why I designed the form that way. In addtion to the answer I gave at the time, the situations you talking about both here and in How do I stop a form from closing from the BeforeUpdate event is exactly why I do make my forms in that particular style.

[Seth Schrock]

I don't quite see the reason. This particular form is almost exclusively used for entry, not review. Are different options available if the textbox is in the form footer vs. the detail section?

[zmbd]

If you don't want any of the changes to save at all then the DoCmd.RunComman d acCmdUndo is more than likely your way to go.

The form I refernce is just a different mindset.
By having the controls unbound, the only way to save the "New" record is thru VBA via the "Save" button.
I have forms wherein, nothing is bound.
Then forms along the line to the one I referenced.
In either case, I don't have to worry about a user canceling out of the new record before all of the entries are filled, the individual controls handle datavalidation, and the code behind the save button deals with the record set.
More than that and I think we'll go off topic. :)

[Seth Schrock]

I see your point now. I hadn't realized that the controls were unbound. Hmmm... you got me thinking. Might be an update to the database down the road. But for now, I think I will try the DoCmd.RunComman d acCmdUndo. I'll let you know the results tomorrow.

[NeoPa]

Seth, I explained quite explicitly that we needed to know about what you required - in all circumstances. You response explains what you require if the form is closed, but the Form_BeforeUpda te() procedure is not only run when the form is closed. What do you want to do for other scenarios when this procedure is triggered?

A safer way of undoing (as it doesn't rely on the form that the code is running from being the currently active window) is to use Me.Undo. This is clearly called for in the scenario you are considering. We need to know if that is also true for any other scenarios where this code is run before recommending it to you for this situation.

I'm not going to recommend using unbound forms. I've never seen them to be necessary, and they go behind the fundamental facilities of Access, which I've always recommended against as it simply makes life more complicated. It's certainly possible, but I've never seen the need for it when pretty well anything required can be done within the Access paradigm anyway. I will always look for a way to work within that paradigm where possible. Going outside of it puts you outside of the mainstream and that results in less likelihood of getting peer-support as well as more complicated and unexpected code to maintain. That said, it is fully supported in VBA by both DAO and ADODB.

[Seth Schrock]

My apologies NeoPa. I don't want the record to save if a certain field is blank (PINVerified). Anything that would cause the form's BeforeUpdate event to trigger (form closing, going to another record, and if there is another one that I don't know about, then that one too) needs to check the value of the PINVerified before allowing the record to save.

I see your point about unbound forms.

[NeoPa]

In that case I'd certainly use Me.Undo, but it may also be necessary to set Cancel = True too (I doubt it though). I suggest you try it with and without. Canceling after the Undo may work fine and allow the close, or it may not. The Undo may even cause the update to cancel automatically. If you can, avoid saving even the unchanged record, but if you have to allow that to ensure it works as required, then at least it should do no real harm.

[Seth Schrock]

That worked. I didn't need the Cancel = True.

Just curious, when I had the Cancel = True instead of the Me.Undo, I couldn't enter design view. I realize that the Cancel = True was stopping the action that caused the BeforeUpdate to trigger. However, I didn't get an error when just trying to enter design view but I did when trying to close the form. What is the difference?

[NeoPa]

Seth:
Just curious, when I had the Cancel = True instead of the Me.Undo, I couldn't enter design view. I realize that the Cancel = True was stopping the action that caused the BeforeUpdate to trigger.

When a form switches from display view to design view it goes through a close process. If that close process fails (due to setting Cancel = True) then the switching cannot happen. You got that bit already.

Seth:
However, I didn't get an error when just trying to enter design view but I did when trying to close the form. What is the difference?

I'm not sure I really understand that myself clearly. I guess the designers felt at some level that the form not switching to Design view is clear enough that you need no extra message, but why that wouldn't be the same thinking for a form closing I'm not sure about.

See if anyone pops in here with a sensible answer.