How can i cancel out of a form without running the subform's beforeupdate event?

I have a form with a datasheet-view subform.  On the main form is a cancel button so the user can stop making the order/invoice and cancel out.

When you press cancel, the beforeupdate event in the subform fires first, reporting errors about the data the user has just started to fill in, if any exist.  Is there a way I can prevent this?

Also, I found that Me.Undo doesn't delete the subform or mainform records.  I'm having to use sql delete statements.  Is this necessary?
Who is Participating?

The problem you describe is classical for main forms used as mere frame for a subform datasheet. The problem is that for the "normal" subform mechanism to work, Access automatically:
1) saves the main form when entering a subform
2) saves the subform when exiting the subform control

In other words, the [Cancel] button on the main form comes "too late", at that point the subform is already saved. You need this button either on the subform or completely outside the form. Some solutions:

a) Use a custom toolbar. This is really what you are looking for, since you are currently using the main form as a toolbar area for buttons meant for the subform.

b) Use a hand-made toolbar, in fact another modal form with the buttons you need. Same idea.

c) For a continuous subform, use the subform's form header or footer to create the [Cancel] button.

d) Instruct the user about the powers of the [Esc] key...

Hope this helps!
Rey Obrero (Capricorn1)Commented:


Private Sub cmdCancel_Click()
    If Me.Dirty Then Me.Undo
    DoCmd.Close acForm, Me.Name

End Sub
Hi HKComputer,
 Also, I found that Me.Undo doesn't delete the subform or mainform
 records.  I'm having to use sql delete statements.  Is this necessary?

Yes.  There is no 'form+subform' level undo.  Once you have moved from the main form to the subform the main form record is saved and vice versa.  

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HKComputerAuthor Commented:
I think your answer peter57r would also render capricorns suggestion useless in my application.  The cancel button is on the main form but I'm needing to undo the subform.  Of course, the subforms beforeupdate event fires before the code does for the cancel button.

Am I thinking correctly?  Is there a better design?
Pigster14IT ConsultantCommented:
How about simply a message box popping up to the user to see if they really want to run this data check? Or however you want to word it.

Yes or No, Ok or Cancel. If they click Yes or Okay, then run code. If the other then pass the code up.

The only problem is if they did do an update to data and clicked cancel and you did not get to do your data check.

However, maybe if they click Yes or OK, it updates a column in the table to say, successfully tested. Then I am not sure what you do with this data, but say the data gets submitted somewhere, you only pull those successfully checked.

Therefore, if a record was suppose to be checked and the user cancelled out of it, it is not marked as successfully checked and they may question, hey why wasn't my data submitted, well you have to click Yes or Ok. It's back on the user.

Just a thought.

If you're getting the error (data validation) in the subreport's Before_Update event why not code your error routine there?  Maybe have the cancel button set a flag (global would be easiest) and have the error code check the flag.  If Cancel was pressed then undo the data changes on the subform, then the do it on main form (which will is where the code will return to after setting the flag, and then firing the Before_Update event on the subform).

I usually use VBA to handle these situations, but the examples above using the .undo might work there just fine.  If not then just write code to revert the subform's previous data.  You might have to create a logging system for this approach to work, but it would accomplish what you want as I read your question.

Good Luck
For this situations I use a temp table that's filled in the OnCurrent of the mainform.
When the Cancel is pressed I undo the changed of the Mainform and do nothing with the temp table.
When Save is pressed I delete the original rows from the original table (normally all detail rows for the mainforms InvoiceID) and Append the new set of records.

Getting the idea ?

Leigh PurvisDatabase DeveloperCommented:
Me.Undo can be quite lightweight.


Docmd.RunCommand accmdUndo

You might want to error handle it (incase there's no undo available - and possible switch setwarnings off around it (incase it is effectively deleting the record by undoing the entry).

On Error Resume Next
With Docmd
    .Setwarnings False
    RunCommand accmdUndo
    .Setwarnings True
End With
HKComputerAuthor Commented:
I appreciate the comment Pigster but I think its not quite userfriendly enough.  Your suggest won't work BPeb because the code in my beforeupdate event runs first, long before the cancel button's code runs.

Nico, I think you're onto something but I'm still trying to grasp it.  My main form would be bound to a temp table?  Not sure how this will help my data validation in the subform.  I want to run those validations but I don't want to run them if the user hits cancel.

I think the solution will be do do all validations in the individual text boxes' Exit event, therefore eliminating the validation code I have for the Form's BeforeUpdate event.
No, I described it for a main form bound to an Invoice with the InvoiceDetails as the subform rows.
Now you can copy the details to the temptable and replace the original rows with the newly manipulated rows when the Save is pressed.
When you want to do "bulk" maintenance on all invoices being just one table, then this approach can be used too, but only when there's no risk that two users are updating the same table, else you can run into concurrency update problems :-(

What's the situation you're dealing with ?

HKComputerAuthor Commented:
It's a multi-user app and just as you stated, I'm working in an expense entry form with subform, much like an invoice.  Subform is just datasheet view.  I must be thickheaded cause I'm not quite getting it yet.
OK, just imagine you take an expense entry and copy the "child rows" for that specific entry into a temp table.
Now the original rows will remain "untouched". When the user wants to save the new edited set by pressing [Save] you first remove al rows from the original table and append the new set.
When the user presses [Cancel] you do nothing as the original rows are still there.
You could even define an Undo / Reset button that's just dropping the rows of the temp table and fills it again with the original rows.

Clearer ?

HKComputerAuthor Commented:
Coming back to this post after a "sabbatical". :-)

I did finally catch on to your suggestion there Nico.  And i really like it.  I'll be using it to make my invoices, repair orders, purchase orders, etc editable instead of forcing the user to close them forever.

However it doesn't totally apply to dilemma I'm facing on my form/subform.

Imagine that the user is editing a record on the "datasheet view subform" on my invoice form.  In the middle of selecting an item number, he decides to cancel.  When he presses cancel:
(A) The beforeupdate event fires in txtItemNo
(B) And/or the exit event fires in txtItemNo
(C) And/or the beforeupdate event fires for the subform

I have been using all three of these events for validation code.  Now the user must first select a valid item number before being allowed to cancel out of the invoice form.  This seems cheesy, to say the least.

I did come up with the idea of coding the delete button to pass a boolean value so that these events can check that value and then exit if its true.  Well, it doesn't take a rocket scientist to see that this will not work.  This boolean value never gets set to true until all three of the above listed events have already taken place.  Correct?

I'm not seeing any work arounds for this in any of the answers listed here.  But I'd still be happy to hear some input on the matter.
I never use a fields OnExit to validate code.
I have one [Save] button with code like:

Private Sub btnSave_Click()
Dim txtMessage As String
On Error GoTo Err_btnSave_Click
    ' init error message
    txtMessage = ""
    ' Check fields in reverse order to set focus to the first
    If Not Len(NZ(Me.Description)) > 0 Then
        txtMessage = "Description empty ?" & vbCrLf
    End If
    If Not Len(NZ(Me.Severity)) > 0 Then
        txtMessage = "No Severity?" & vbCrLf & txtMessage
    End If
    If Not Len(NZ(Me.Type)) = 0 Then
        txtMessage = "Recordtype empty ?" & vbCrLf & txtMessage
    End If
    ' Check error found
    If Len(txtMessage) > 0 Then
        MsgBox txtMessage
        Exit Sub
    End If

    Exit Sub

    MsgBox Err.Description
    Resume Exit_btnSave_Click
End Sub

This will allow one point where all checking is done and having related fields is no problem as the user indicated by pressing save that (s)he's ready with entering the data. In your case you can even add a loop for processing the subform rows, but normally in the subform the form's beforeupdate can be used to check the subform's row that has been changed.
It's the general approach for windows programs to allow a user to enter all fields (and in this case also detail rows) in any sequence and do nothing as long as no [Save] is pressed and return to the old situation when [Cancel] is pressed.

Just make a small form and try :-)

HKComputerAuthor Commented:
I'd like to clean this one up.  I'll post my findings here.  First, I'd like to analyze harfang's post above with probably the best recommendations for this problem.

>>a) Use a custom toolbar. This is really what you are looking for, since you are currently using the main form as a toolbar area for buttons meant for the subform.
HK> This is probably the best solution.  I did find some others that I'll comment on later here.

>>b) Use a hand-made toolbar, in fact another modal form with the buttons you need. Same idea.
HK> I'm not sure but it seems to me that my subform's BeforeUpdate event will fire before switching focus to the modal toolbar form.  Once again, the user will be stuck with a partial entry, (incorrect data) that he cannot cancel out of until he fills in the correct data.

>>c) For a continuous subform, use the subform's form header or footer to create the [Cancel] button.
HK>This is a very good suggestion.  I haven't gotten accustomed to the idea of using a continuous subform for my invoice details but I can't say that I know of any reason that it won't work.  I think it may require a little more screen space, especially if I opt to use the header or footer for controls.

>>d) Instruct the user about the powers of the [Esc] key...
HK>This one I agree with but most of my applications are used on runtime versions of Access.  I can't say for sure but I don't know if they can use the [Esc] key like you can in a full install of Access.  I'll have to experiment with this one.

Here is the conclusion I have come to.  It seems to work best not to use the form's beforeUpdate event for validation routines.  Validation can be done on the textboxes exit event, but the results are much the same.  

Really, one of the best solutions I've found is to loop through the subforms records when the user clicks the save button and perform the validations in the loop.  This allows the user to cancel at any time without getting stuck in some kind of exit event validation or beforeupdate event validation.  

One little enhancement: the exit event validations and beforeupdate validations can output the error message to a label or text box on the main form, maybe in a place you would call the "Message Center".  This way the user knows there is a problem but he will not be forced to deal with unless he leaves it there and then presses the save button.


Thank you for the feedback. I wish we would get that more often.

b) About when a record is updated: The auto-save exists only when switching between a main form and its subform. It dos not occur when switching between independant forms. However, a toolbar seems more appropriate than a form imitating  one.

d) [Esc] works for runtime versions, along  with all the other normal navigation, selection and editing keyboard shortcuts. The runtime version only eliminates the so-called "special keys" like showing the database window or switching  to VB.

More generally, you raise an interesting point about data validation. On one end of the range, you find data entry screens where everything needs to be correct before the entry is accepted, and where you have a validation button with double confirmation (think about on-line banking sofware), on the other end you find screens where all fields are optional and unchecked (think about a survey made through Excel sheets).

The problem is that you also need valid data in predictable format for the rest of your application to run well. So the question is: at which point do you perform the valiation? For example, let's take a simple case: you need an amount and a currency. If you perform no checking, you will end up with: $1000, 1'500 CHF, 1.400,00 €, 123 456 Yen, 123,456 JPY, 3000 $U, etc...

Your choices are simple: perform validation during data entry (currency field and currency combo), or just accept anything and analyze it later. With the risk that you will not be able to resolve some issues, when the user is no longer available for clarifications...

I realize that I'm starting a real debate here, although that was not my intention (^v^)

I merely wanted to point out that your last ideas to remove as much as possible all validation messages has its cost. You will have to deal with incomplete or plain wrong data in all other features of your database (calculations, reporting, etc.).

Anyway, thanks again and good luck with your application!

HKComputerAuthor Commented:
You do pose some interesting questions.  I think it is evident that some validations must be performed before the user can save the record (or go on to the next record) in order to ensure accuracy.  A second field the user enters data into might depend on the first, therefore, validation on the first is "mission critical".

My concerns with validation are that I don't want it to be a hindrance to productivity, a frustration.  In cases where it truly is of ultimate importance, then let it be a frustration.  Accuracy wins.  But for the fastest data entry, a user who uses the application all day long will be more productive if they don't find themselves in badly designed loops that force them to to enter valid data before even being allowed to cancel the entry.  As posted above, if the user knows how to use the [Esc] key, a lot of this problem can be solved.  I do have one comment on one of your comments:

>I merely wanted to point out that your last ideas to remove as much as possible all validation messages has its cost. You will have to deal with incomplete or plain wrong data in all other features of your database (calculations, reporting, etc.).

HK> I think I was meaning that validation would still be performed but only at the very end of the invoice creation/modification.  There is one problem with this that I do not like.  The user could theoretically enter 15 lines of invalid data, only to find out at the very end that it is not valid.  Seems to me that a "message center" could help aleviate this issue.
Normally a check is performed per form and/or row.
A row represents a consistent set of fields and a user will understand to have to "return" to a previous row when there's an error.
When the rows of the subform are all correct the complete check for both mainform and datasheet can be performed when necessary.
However as Access will store changes for the subform "row wise", an additional action (temp table) will be needed when you want to offer the user a cancel/undo of a main/subform combination.


I don't have anything to add, except to say that I like your global approach of GUI design. You clearly give a high priority to the user's point of view, with I find very professional.

Leigh PurvisDatabase DeveloperCommented:
There are users to consider??
<slaps forehead>
HKComputerAuthor Commented:
Some of my "curious" (in my own words) ideas about GUI design come from working day in and day out for the last number of years with a Foxpro application that has many poorly designed forms.  After learning how easy (or sometimes how hard) it is to fix up these little annoyances, I've been striving to keep my own forms and applications free of bad behaviour.  The Foxpro application I refer to has the problem that I originally posted about.  When a user attempts to add an item to an invoice, and enters an item that is not in the inventory table, he recieves an Exit validation error message that says the part number is incorrect.  This is all fine, except that when you attempt to cancel, you cannot cancel until you enter a correct part number.  I think deleting your original input so that the field is blank might allow you to cancel without this problem, an easy fix, but still annoying.

So I have been debating, do I allow part numbers that are not in the database to exist on invoices, or validate them on the subform's beforeupdate event, or scream at the user on the save buttons routine.  Hmmmm....

What I chose to do is have an inventory search screen come up that shows all results in a continuous form that are "like" the user's input that was not a correct part number.   So in essence, entering an incorrect part number will bring up a search screen.  If the inventory file is quite large, this will likely cause performance problems and I'll need to give the user a way to disable this feature and just get an error message instead.  And an easy way too.  Probably a check box on the form that is "connected" to an .ini file on the users local hard disk.

I have started making it standard to use a "Message Center" label that gives the user lots of feedback about what it is going on.  It reports that he is "Editing..." when the form is dirty but NewRecord is false.  And it reports that he is editing a NewRecord when NewRecord = True.  The only problem here is that I'm testing for Dirty on the form's timer event.  And I'm testing every half second.  This too can cause performance issues.  So you finally have to decide how many features you can put in and not lose too much on the performance side.  A difficult decision it is.

I'm not really happy unless my forms' beviour is uniform accross the app and I prefer that it is not odd or strange behaviour to both experienced and inexperience computer users.  This is a very high ideal.  And difficult to truly reach.

I do love the help i get on EE and appreciate all the input.  Ultimately I do make decisions sometimes that are against the recommendations I recieve here.  What I design is not just databases, they are database applications that are used by dummies.  These are not experience Access users.  So you have to make it look and feel simple, like Quickbooks.  Well, I don't model after QB.  Much of their behavior frustrates me.  Today I noticed that I cannot change a C/C transaction that has been entered under the wrong C/C.  It must be deleted and reentered.  (Maybe I'm too dumb to figure out how?!)  This is no small annoyance.

The pursuit of perfection continues. -HK
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.