469,126 Members | 1,276 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

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

Seth Schrock
2,957 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3. If Me.PINVerified = False Then
  4.     Cancel = True
  5.     MsgBox ("This transaction is not complete and changes will be canceleld.")
  6.  
  7. End If
  8.  
  9. 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?
Dec 3 '12 #1

✓ answered by 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.

13 4562
NeoPa
32,161 Expert Mod 16PB
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.
Dec 3 '12 #2
Seth Schrock
2,957 Expert 2GB
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.RunCommand acCmdUndo work better?
Dec 3 '12 #3
NeoPa
32,161 Expert Mod 16PB
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.
Dec 3 '12 #4
Seth Schrock
2,957 Expert 2GB
I just want to undo all changes made to the record so that they aren't saved when the form closes.
Dec 3 '12 #5
zmbd
5,400 Expert Mod 4TB
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.
Dec 3 '12 #6
Seth Schrock
2,957 Expert 2GB
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?
Dec 3 '12 #7
zmbd
5,400 Expert Mod 4TB
If you don't want any of the changes to save at all then the DoCmd.RunCommand 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. :)
Dec 3 '12 #8
Seth Schrock
2,957 Expert 2GB
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.RunCommand acCmdUndo. I'll let you know the results tomorrow.
Dec 3 '12 #9
NeoPa
32,161 Expert Mod 16PB
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_BeforeUpdate() 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.
Dec 4 '12 #10
Seth Schrock
2,957 Expert 2GB
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.
Dec 4 '12 #11
NeoPa
32,161 Expert Mod 16PB
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.
Dec 4 '12 #12
Seth Schrock
2,957 Expert 2GB
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?
Dec 5 '12 #13
NeoPa
32,161 Expert Mod 16PB
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.
Dec 5 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jeff Thur | last post: by
2 posts views Thread by darthghandi | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.