Cancel BeforeUpdate, form is still dirty.

[GK in the UK]

I want to cancel the attempted setting of a check box from cleared (false) to checked (true), but without getting the form dirty. It doesn't seem possible.


I tested the check box value in the BeforeUpdate event of the check box thinking I could cancel it before it dirtied the form. I wasn't expecting the results I got.


I get -1 on entry of BeforeUpdate, so it's already set. The check box is ticked.
I get -1 after the statement Cancel = true, so it's still set
I get -1 after a control.undo, so it's still set.
I get -1 on exit of BeforeUpdate, so it's still set. The check box is still ticked


But, the check box is then unset and AfterUpdate isn't called.


AfterUpdate does not fire at all and the check box is cleared, which is what I want, but I don't want the form to be dirtied. If I Undo a form, it resets the dirty state, but this does not seem to work with a control.

 

[bob fitz]

AfterUpdate does not fire at all and the check box is cleared, which is what I want, but I don't want the form to be dirtied.

I don't think you can change any value on the form without making it dirty. You can set Dirty to False.

 

[GK in the UK]

The control event named 'BeforeUpdate' seems to be mis-named in that by the time the sub that the event calls runs, the update (checking the tag) has already occurred, and cancelling it doesn't clean the form.

I have an Undo button on my form and it works as expected and 'cleans' the form, so I was kind of expecting the same behaviour for a control.

The issue is that I have a checkbox which can only be checked if certain conditions are met. If it's checked and cancelled, the form remains dirty and I'm getting unwanted saves of the record. I have an Edited timestamp in BeforeUpdate and I didn't want it firing on the change and reversal of check box value.

Someone could open the form, try to tick the checkbox when it shouldn't be, and the record would be re-saved and stamped when nothing had changed.

I've re-jigged my code so that if the condition isn't met, the checkbox is disabled. So far that seems to be a better solution.

 

[The_Doc_Man]

The control event named 'BeforeUpdate' seems to be mis-named in that by the time the sub that the event calls runs, the update (checking the tag) has already occurred, and cancelling it doesn't clean the form.

While not doubting your word, I must ask what makes you think this to be the case, since it represents an extraordinary finding. Either that or you are not describing the event sequence that you think you are describing.

Here is the issue. If the checkbox is bound to the underlying record then checking it will automatically dirty the form. Cancelling the update via the "BeforeUpdate" event prevents the write-back of the form's dirtied data to the underlying record. But that leaves the form hanging unsaved and waiting for any excuse to update. And it is still dirty because cancellation of the event has no effect on the value of an control.

Of course, if the BeforeUpdate is not cancelled then the update occurs, which would lead to a sequence of other events, starting with AfterUpdate and ending with FormCurrent event when the form and record become "current" with respect to one another.

In essence, cancelling the BeforeUpdate event puts the form back to the point just before you would have saved it. Which means that the form (and that control) are both dirty, and the control's .Value and .OldValue still mismatch. Any attempt to navigate will still attempt to do an implicit save, which will trigger the event sequence.

The only way this behavior can be prevented is that the checkbox control cannot be bound or you have to undo the form's changes with a Me.Undo operation. Manually resetting the checkbox doesn't undo the "Dirty" status because other things on the form (such as text boxes or other check boxes) could have also led to the "Dirty" status and Access really can't tell which control made the form dirty. So it doesn't try.

 

[The_Doc_Man]

Pat, the description is confusing. I went back to the initial post and will address the issue that seems to underlie the question.

I want to cancel the attempted setting of a check box from cleared (false) to checked (true), but without getting the form dirty. It doesn't seem possible.

GK, undoing a single control won't undo the form's .Dirty property because that comes from as many sources as you have bound controls on said form. Access knows the form WAS dirty but because of the multiplicity of possible causes of getting dirty, it does not even TRY to remember why it got dirty. Net result? If you are going to manipulate the form's .Dirty property, you are asking for issues.

More specifically, if this form has some bound controls and you use the .Undo on one control, you have not touched the other controls, and unless you have a really bizarre form, it is possible one of them made things dirty. But here is the peril. Suppose you had this case where someone set the control and you did your thing to undo that. BUT some other changes had also occurred. They would ALSO have set the Me.Dirty flag if it had not been previously set.

If you then tried to reset the Me.Dirty flag, it would be possible to navigate away from the current record without saving it. That flag is there for a reason. It is an AGGREGATE flag for actions on ALL of your modifiable controls. You are focusing on one control, and I find it hard to believe that you are going through all of this for a form with one and only one control on it, and a checkbox control at that. So at the moment you are lost in the forest and can only see one tree.

Or at least that is what I am sensing here.

 

[GK in the UK]

Doc, thanks for your input on this. Sorry if my op wasn't clear. It's a complete misunderstanding on my part what BeforeUpdate means and does.

I had thought that BeforeUpdate when fired by a control would give me the ability to not ''update' the field or checkbox and revert to the previous value and form state. I thought, Cancel = true in BeforeUpdate for the control would cancel just that input. The control.undo line was only put in when I could see that Cancel=true wasn't doing what I thought it was supposed to.

You've both explained how Access can't know that it should 'clean' the form on the cancellation of a single control and I get that now. Hence, I scrapped this code and went with the disabling of the control instead.

Doc, I have been going round in circles a bit with 'Order of Events' and (amongst other things) wanting to have a 'Date Added' timestamp as well as a 'DateEdited' timestamp. The form has a sub and of course as soon as a main record has been written (Updated?) by clicking the button to add a record to the sub form, I than had issues where the main form record was getting a DateEdited stamp which I really only wanted on subsequent openings of the form (dealt with that now).

And by the way, I wasn't going through all of this for just one control on a form, I have a number of controls (fields) bound to the record. It was always the case that I didn't want to change the state of the form on the change of a checkbox which was then cancelled.

I dunno, maybe I'm over thinking all of this. Some of these issues are down to a culture change from my procedural programming days I think. Thank you for your help. If you think that was a big misunderstanding maybe I've got bigger ones in store ...

 

[GK in the UK]

Ah, but do you have LastChangedDT visible in a bound control on the form? because it seems that if that is so, it will raise an error if I try to Cancel BeforeUpdate if LastChangeDT is assigned in BeforeUpdate.

There's another knock-on issue. I have an Undo button on my form. It works perfectly until the user blanks out the field that must have some text. At that point the focus is returned to the offending field and the only thing the user can do to re-instate the original value is to type something in, go to the next field, then press Undo. Undo cannot be pressed from the field. It's messy and non-intuitive.

At the point that the cursor returns to the field, the user would expect to be able to press Undo and re-instate the old value.

How can I stop BeforeUpdate from being repeatedly called once the first validation test fails ?

 

[GK in the UK]

Cancelling the BeforeUpdate event DOES NOT reset any changed values.

Agreed, I'm not expecting it to.

Personally there are only two instances where I ever use Me.Undo
1. if my security says that the user is not allowed to update anything. Since he cannot update there is no reason for saving anything he typed.
2. If I raised a message asking if he really wanted to save. I usually offer three choices - yes/No/Cancel. With a No answer, I cancel the BeforeUpated event but with the Cancel answer, I undo using Me.Undo AND cancel the BeforeUpdate event.

I also use me.undo in two instances

1. When the user clicks the Cancel (and close) button, BEFORE the record has been saved (so no auto-save of an unwanted record)
2. When the user clicks the Undo button, when they want to reset all changed fields, BEFORE they hit the save button

The Save button. That's the problem, isn't it ? I didn't mention the Save button. BeforeUpdate can't Cancel the DoCmd.RunCommand acCmdSaveRecord that's behind my Save button. BeforeUpdate can only cancel auto saves by Access, is that correct ?

I've got it solved with just a few lines of code. I have a function fFormIsValid where field validation is done, displays the user message and puts the cursor in the offending field(s). It's called by my Save and Close button, and if the function returns false, I don't call DoCmd.RunCommand acCmdSaveRecord. I'm not Cancelling the BeforeUpdate event and everything works just as I want it to without errors. Cancel, Edit, Undo, Save and Close, all doing exactly what I wanted them to.

Thank you again.

 

[The_Doc_Man]

I have been going round in circles a bit with 'Order of Events'

https://support.office.com/en-us/ar...-objects-e76fbbfe-6180-4a52-8787-ce86553682f9

This is a really good starting point for that important issue. The article differentiates between control and form update events, too, so that can be quite helpful.

wanting to have a 'Date Added' timestamp as well as a 'DateEdited' timestamp.

As to when you would put a timestamp on something, understand this fine point. It DOES NOT MATTER whether you put the timestamp in place when you click a SAVE command button or when you do something in the form's BeforeUpdate event. The way that timestamps are typically written, they are not fine-grained enough to tell the difference in time between those two events.

For example, the Timer() function, which gives you milliseconds since midnight as a LONG, isn't fast enough to resolve the time between those two events. I tried to tell the difference because I was tracing another problem that was event-based with a shared BE file and needed a record of the exact order of when things happened between two users in the same general area. Since our computers were all hooked up to a common timebase server (Network Time Protocol or NTP), times were pretty close to exactly synchronized to the millisecond.

I had a SAVE button for which the x_Click routine did the DoCmd.Save operation, which of course triggered the cascade of Update events. I had timestamp code there, too. I eventually resolved the problem but the timing between the start of the Click event and the Form_Current event that ended the sequence was usually the same millisecond and never more than 2 milliseconds.

In my case I used the BeforeUpdate event to prevent someone from saving by navigation. The requirement at the time was that an explicit SAVE (by command button) was required and that a navigational save was disallowed. I put the timestamp in the button click code.

The moral of that story is, put the timestamp anywhere before the actual save, the time will not be materially different.

 

[GK in the UK]

Hi Doc Man,

The timestamps in my application are really just a guide as to when someone last edited the record, I could probably do without the seconds. I do assign them in the BeforeUpdate event of the main form.

It was slightly complicated in that I wanted the main form to be initially written with just a DateAdded and no DateEdited. DateEdited is only written on subsequent opens and saves. Plus, I wanted DateEdited to reflect changes made to relevant fields on the sub form.

I couldn't rely on Me.NewRecord to know if the main form was on it's first use, as main had to be saved as soon as the user adds a sub form transaction line to get the key to link it to. So I had to set a boolean to know if the main form is on first use.

As to knowing when to stamp, I no longer use Me.Dirty. It was something you touched upon earlier that led me to look at the .OldValue. So I now have code that compares the relevant fields of both the main form and the sub form, and stamps the DateEdited field only if the fields actually change and only if this is a subsequent open and save of the form. Some checkboxes don't count as relevant edits as such, they're just process flags and can be changed without changing the essentials of what the form is about.

 

[GK in the UK]

OK point taken, I already had a sub in the Form_Unload event to force the user to use my Save button but I s'pose you'd call this patching a hole.

It's going to mean the user will need to fix invalid input when they click the Add Line button instead of when they click the Save button but I daresay that won't matter. Clicking Add Line has to save the main record to get the ID to link to.

 

[GK in the UK]

Pat, I really appreciate your continued help.

You are over controlling this.

Seems like it.

The VAST MAJORITY of the validation code belongs in the BeforeUpdate event. The only code that would be in individual control BeforeUpdate events is code that is used to hide or enable controls based on the value of the current control. Notice a pattern here - Control's BeforeUpdate Event, Form's BeforeUpdate event. NO OTHER EVENTs should ever have validation code.

My validation function fFormIsValid is now called by BeforeUpdate.

The main record ALWAYS has to be saved before you can enter a subform record. Access handles this AUTOMATICALLY if only you would let it.

Please tell me how it happens automatically. Code to save the main record is behind the AddNewLine button. I commented it out, and it broke. The main record was dirty but it wasn't saved when DoCmd.OpenForm for the sub ran and when I tried to save the line I got a run time error 3101 saying there was no record in the main table.

Access is a Rapid Application Development Tool. It does things it's own way. You can alter this to a certain degree but you will tie yourself in knots as you seem to have done.

I sometimes put Save buttons on forms but they are mostly to make people comfortable. All they do is save the record. So whether they press my save button or not, the record gets saved when Access decides it must be saved and trust me, Access KNOWS when a record is dirty.

Yes. The Save and Undo buttons seemed like a good idea at the time but I've revised my thinking and in the spirit of embracing the way Access does things I've dropped them. I'm keeping the Edit button to toggle the .enabled status of the fields but I now have a Cancel button and a Close button. The Cancel button does me.undo and closes (but with a warning if the record is dirty). Close does what it says. Access can do the save management.

The thing you need to be careful with is making sure that YOUR code doesn't dirty a record. This seems to be the problem you are trying to fix. Your ChangeDate keeps getting updated and you don't think it should. It is getting changed by your own code in an event that shouldn't be changing it. So things like the ChangedBy and ChangedDT would go in the BeforeUpdate event.

I'd already fixed this. DateAdded and DateEdited are assigned only in BeforeUpdate of the main form.

You could put it in the Dirty event but I prefer to simply put all the code in a single event so I don't have to go looking around for it.

OnDirty only had code to enable or disable three buttons. Two of them have gone so I'm left with just the AddNewLine button, enabled when the main form is dirty.

For pop up forms that are acting like subforms, you NEVER dirty the record before the user does. Your code that sets the Foreign Key goes into the BeforeInsert event. You only need to set this value for new records. The FK already exists for existing records so you don't set the FK in the Dirty event or heaven forbid the form's Open or Current events.

OK, I had the code for the FK in BeforeUpdate with a test for Me.NewRecord. I've put it in BeforeInsert and dropped the if.. line.

I'm left with just a single line of code with DoCmd.RunCommand acCmdSaveRecord, the one behind the AddNewLine button. As it is my application will not run without it. I may as well leave it in but the purist in me says if there's a way to do without it I'd like to know.

Putting the validation code in BeforeUpdate seems to have put me back in the situation of being unable to cancel execution. Here's what I'm seeing:

User clicks Close on form with invalid data. It's dirty.
BeforeUpdate is called, which calls validation code. Validation sub pops up a MsgBox to notify and does a field.SetFocus.
BeforeUpdate is cancelled.
Record doesn't save but the form closes !

I found this of yours from 2002:

Both the BeforeUpdate and Unload events can be Cancelled but the Close cannot. So do your editing in the BeforeUpdate event and cancel the event with (Cancel = True) if you don't want the record to be saved. If you also want to prevent the form from being closed if the user was trying to save bad data, you'll need a global variable that you can check from the Unload event to use so you can set the Cancel parameter if necessary.

So I put a Form_Unload event in. Added a bAllowClose = false in BeforeUpdate.
In Form_Unload, I've got: If Not bAllowClose Then Cancel = True
Now I get:
Run-time error '2169' You can't save the record at this time.
DoCmd.Close acForm, Me.Name is highlighted.

So ... how do I get the cursor in the invalid field and stop the form from closing ? An error trap ? Ugly.