Mysteriously dirty form (that is not dirty) (1 Viewer)

First, let me put this in perspective: :banghead:

OK, now that we have that out of the way, my question is really whether anyone has ever seen this before.

Ac2007 database. My form has a bound text-box to a text-type underlying field in a Dynaset query to a Jet/Access back-end file. The form is set for "tab moves to next field, wrap-around" as opposed to "tab skips to next record after last field of current form". Users have to use a COMMIT button to save data, a CANCEL button to undo form changes. If you see the COMMIT button, you can never close the form because CLOSE vanishes - but you can CANCEL changes and CLOSE becomes available again.

I have the form trapped to a fare-thee-well to audit log or otherwise protect my users from their clumsiness and other mistakes. I also have update events to audit-log when someone does a COMMIT or tries to do some other action. I thought I had solved the problem of detecting changes in a field. However, this time I've found a case that has me scratching my head.

The problem came to light when a user inadvertantly entered a stray space character at the end of the existing text in a particular text box as described above. I'm really not worried about the sequence of keystrokes and mouse clicks that got the user to that point. I'm worried about the characteristics of that point.

What happens is that the form appears to be dirty (Me.Dirty = True if you use Debug.Print in the immediate window). But I manually contrived the case where the only difference was that I added a space to the tail end of a text field. When I did that, my internal auditing system compared .Value to .OldValue and didn't log anything - because the .Value and .OldValue were the same. Well, that's crazy because I know I added the spaces, and besides, the form's Dirty flag went TRUE on tabbing out of that field. So I know for a fact it saw the changes. But in the Locals window, the .Value and .OldValue strings were exactly the same. In other words, the string content and length BOTH matched. The locals window showed the .Value and .OldValue as quoted strings, neither having spaces at the end of the stuff inside of the quotes.

Now, what is confusing the issue is that the code acts like things are now dirty and the form's Dirty flag agrees. When I try to close the form, the BeforeUpdate event fires, implying that it thinks the form has changed. But in the only field I actually touched, the .Value and .OldValue are NOT different. Further, when the particular text box is still in focus, the .Text property agrees with (both) the .Value and .OldValue contents, including that it somehow lost the trailing spaces.

I try to close the form and it fires the BeforeUpdate event, but it is supposed to Cancel the close event in that case - but it doesn't. It lets me do the close because it somehow realizes that the form really wasn't dirty.

If I type a printing character in that field, even just a period or any other punctuation mark, it does what I expect it would do. In other words, the form really IS dirty and the trapping that I do prevents me from closing the form until I commit or cancel the change. So in normal use, the code works exactly as expected. But trailing spaces confuse the hell out of it.

Has anyone seen this kind of behavior before? It seems that Access makes the form DIRTY first, but then realizes that adding spaces at the end of the text box really didn't change anything so it sort of "calls off" that change. By that time, it is too late and the form is still dirty - but you can't tell what was changed by comparing old and new values.

Therefore, I repeat... :banghead:

Well, the article makes it clear that this is actually expected behavior. But now the problem is, how do I call off the Me.Dirty flag in that case? Because that flag is going to cause the form to try to update a non-existent change.

Guess I'll just have to note that there is no "real" change and allow the BeforeUpdate code to pass the operation "silently" because it will not do anything useful anyway. Not what I wanted to hear, but it isn't impossible. Just a royal pain in the toches.

Thanks for the link, Spike!

My advice would be to sleep on it.

You wrote:

I try to close the form and it fires the BeforeUpdate event,

Click to expand...

which it of course does only because it sees the form as dirty.

but then

but it is supposed to Cancel the close event in that case - but it doesn't. It lets me do the close because it somehow realizes that the form really wasn't dirty.

Click to expand...

which kind of no longer makes sense (without seeing the exact code supposedly cancelling), because the event was triggered by the "dirtiness" of the form.

Spike,

I haven't reached complete understanding of that closure behavior exactly, but what has to happen next is that I need to single-step my way through the BeforeUpdate event and do some local-window browsing.

You are right, it doesn't totally make sense. But then again, my favorite coffee mug, inspired by the fact that I work with the U.S. Government, is from Lewis Carroll's Alice In Wonderland: "It would be so nice if something would make sense for a change." I'm so accustomed to things not making sense these days that by the time I retire, I'll be perfectly ready to tacking USA Medicare paperwork.

As to sleeping on it, I can't completely ignore it. I have to modify my event code to be silent if my own flags tell me the form is clean but the form's Dirty flag is set. The last piece of this puzzle for me is to prevent the pop-up box I use to tell people to not try to close my forms without a prior COMMIT or CANCEL operation. If the change falls into this category, I have to figuratively sleep on it - but unfortunately, I cannot programmatically sleep on it. I have to take action to silence the alarm.

I guess what really bothers me isn't that Access eats trailing spaces in text-oriented fields. It is that KNOWING it eats trailing spaces, Access lets the form get dirty anyway in that case.

Ok

interesting

i just tried adding spaces to a text field, moving off the text box, and testing the length. trailing spaces appear to be stripped - when you reenter the end of field is at the end of existing text - so i guess old value and new value are going to be the same.

i suppose that makes sense, otherwise a "blank" field would often be a string of spaces, rather than a zls

now although it trims the textbox, it does however set the dirty flag to true, as can be seen by record selectors

probably a bug (undocumented feature!), when you think about it.


----

actually thinking about it, it occurs to me that you get exactly the same behaviour if you actively enter the same value into a text box that is already there. (btw not just a textbox - you get the same behaviour if you re-select the existing value in a combobox - obviously you cannot get the same behaviour in a check-box because clicking it toggles it automatically)

the record is still dirtied even though the control has been edited to the same value.

---
bit of a pain - you could test every control before saving the audit change if you thought it worthwhile. maybe better than saving a "nothing changed" audit entry - it depends if you are timestamping edits, I suppose. If so you are better saving the audit entry, even if nothing changed - at least it gives you traceability.

----
one other thing. since access thinks it is dirty - even though it has been edited to the same value, you may get the "another user has changed the record prompt."

so finally - if you decide you don't want to audit this because it wasn't a real change then presumably a simple undo (i tend to use sendkeys "{esc}") will safely undirty the record.

Dave,

My solution was easier than the analysis. Sort of like the old Warner Brothers Cartoon recipe for hassenpfeffer... first you must catch the rabbit.

I had to revisit my code quite a bit to assure this, but I have verified it. EVERY field visits some routine that tests for data change, sometimes specific to control type and other times a generic routine. But every mutable control IS tested. Because of both data change auditing and user action auditing requirements, I keep a "form state" flag and a "current control state" flag in those test routines. I can choose to audit (or not to audit) based on the change flags. Remember, this is a U.S. government site. You keep records of EVERYTHING. Even in the bathroom, you aren't finished until the paperwork is done.

In my lost focus routine for bound fields, I look for a data change and if there is none, I don't set the form to the "Dirty" state. If my user tries to either navigate or use the CLOSE button and this condition occurs, then Access will fire the Form_BeforeUpdate event. Previously, the code just tested for "form dirty" (my flag) and if it was dirty, you got a message box tweaking you to COMMIT or CANCEL your changes first - unless the update was triggered by the COMMIT code, which set a separate "Used COMMIT" flag. When this anomaly showed up, my flag said NO but the form said YES and that was my problem.

Now, I've added a third test. If you navigate or close and this anomaly occurs, the BeforeUpdate event fires as before. I now test for whether you used COMMIT. If not, then I test for whether I see Access's Me.Dirty true and my software Dirty flag false. I now silently audit-log that case (for statistical purposes). The third case is that you didn't commit a real data change and I still tweak you for that attempt.

You want to go bloody nuts over something? In an UNBOUND field, where you use a variable to hold a value that will be used during the COMMIT action but not as a field value, the same change-detection code DETECTS and RETAINS the trailing spaces in that case. I.e. these spaces persist in unbound fields. So to my way of thinking, what is happening isn't that the text box eats the trailing spaces, but rather it is the attempt to update the underlying copy of the recordset does the munching.

As to the "another user has changed the record" prompt, I've done some rather ugly things where needed to safeguard against that case. Fortunately, in MOST cases, I can get away with optimistic locking. For the cases where the binding is loose (I.e. boxes are bound via rowsource but not by a recordsource, I use RunSQL and some fancy-schmancy SQL to run minimum-time queries. There is no buffer locking the back end records until the SQL gets executed, and after that SQL is run, the recordset frees up again. Everything is designed so that if funding ever becomes allocated, we could use something like SQL Server as a backend. We are already about 75% straight SQL as it is, so the last part of the conversion would be trivial.

sounds like you got it all sorted. auditing changes is a tricky thing. in normal "accounting type" secenarios there are lots of changes that you just don't bother auditing - like a phone number, or a contact name say.

I can see it makes a lot more problems if you audit everything. if you audit after a control changes, the users might subsequently cancel the change - if you audit the whole record change, it might be hard to see which bit actually did change - and then you get this case, where nothing changed!

anyway, glad you got it fixed.

I gave a loose impression, there, Dave. The data audit would occur by a control scan if the Form_BeforeUpdate does NOT cancel the event. But the routines called on LostFocus might change the visualization of the control so that it is in the user's face that something changed. A control-action audit occurs if they COMMIT but not if they CANCEL. Also no audits if they click my customized HELP or the "File Trouble Report". Well, the calling form doesn't audit that but the called form does so - again if the user clicks COMMIT on the FileTR form.

Can't tell you how many "gotchas" I've had to manage because of Access and its little ... shall we say "idiosyncrasies." Not to mention Outlook, Word, and Excel - all of which are used as application objects at various times in this sucker.

Thanks for your interest, Dave.