Persistent form.Dirty flag

[The_Doc_Man]

I'm pulling out what little hair I have left on this problem. Environment is VBA vers 6.5, Access v 11 SP3, WinXP SP3

Got a Form with a sub-form. Sub-form is locked because it is not updated from user action through that sub-form's controls - they are all locked. Some control buttons on the parent trigger a code sequence to do an .AddNew for the sub-form's recordset via VBA activities. The code does the .AddNew, fields get loaded in the record, .Update, .Close, and then I requery the sub-form.

The button that updates the sub-form saves the current record after it does some change auditing. I used the button wizard to build the skeleton and then edited the code to include the auditing. So I get to the point where I have done some VBA to update the sub-form and I requery the sub-form. The new record shows up fine.

Now I try to save the parent form with an updated status to match the last status code stored in the sub-form. (No, not storing a computed value... the sub-form new record holds the action status from the current status of the parent, not the other way around.) Once I requery the child record, essentially a detailed journal of action for the parent record, I want to also save anything updated on the parent. So I let the VBA code do what the button wizard created for saving records.

These two lines of code are driving me bonkers because they prove I don't understand the form Dirty flag. Before I enter this code, the form was dirty because I updated some data on the parent. SO I save it with...

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    bFrmSaysDirty = Me.Dirty

When I put a breakpoint on the second line of code, i.e. after a call that should have saved the record and cleaned the form, it says the form is still dirty.

OK, you say, why do I care? Because the Form_BeforeUpdate event fires AGAIN when I try to navigate to a different record or when I try to close the form even if the navigate is the very next action after I click the COMMIT button. I have trap code in the BeforeUpdate event that is looking for a flag that says "OK to Update because the COMMIT button was used" But I have already updated the underlying and therefore reset the COMMIT-used flag by the time I try to select a new record or perform the close.

What this tells me is that a DoCmd to save a record doesn't reset the form's dirty flag. I didn't think that was possible.

I cannot post the DB because of DoD security issues. I'm just hoping that someone has seen a "persistent" form.Dirty flag before and remembers why it happens. I'm going totally nutso for this form. Has anyone seen this kind of behavior before? If so, what causes it?

 

[KenHigg]

I admit I skipped down to the real question and was wondering if you checked the dirty attribute in the immediate window as you step past the docmd line?

 

[The_Doc_Man]

Ken, that's what I'll check Monday morning. But basically, it was dirty beforehand because I had updated a bound control in order to make it possible to even use the COMMIT button I built.

Just to clarify, I'm making the form as close to idiot-proof as I can. The form's buttons appear and vanish based on context. You always see the HELP button and there is a button that opens a form to file a trouble report. Those two are always enabled. Of the remainder, either two or three can be seen. If the form is dirty, you can see COMMIT and CANCEL buttons. If the form is clean, you can see CREATE, REMOVE, or CLOSE flags. To even be able to see the COMMIT button, the form had to have been updated and therefore dirty. But I will open the immediate window to track things.

I don't THINK the sub-form can possibly be dirty because I had just done a Requery of the sub-form. The new record shows up exactly as planned. I would have thought that a Requery would reset any sub-form .Dirty flags.

 

[The_Doc_Man]

OK, found it (more or less) but I'm confused even more.

I had a bit of code that reloaded a box on the display with the same exact value that was in it. (Inadvertently left that code there). What really confuses me is that if you look at the definition of "dirty" in a control, it is that the .Value and .OldValue properties differ. They didn't, yet the form was dirty.

Anyway, I got rid of the offending code and tracked it back by single-stepping. The dirty flag clears as expected most of the time, though some stuff still confuses me. But I've given up on trying to test for complex cases and now just test the dirty flag and the flag that remembers whether I just used one of the command buttons.

If I try to close a dirty form or navigate away from it, I can't - which is what I wanted. Guess maybe I was overthinking it, though I still don't see what was wrong conceptually once I took out that one bit of stray code. In any case, I've got it clean enough to get into beta test with it. One more big form and I'm done.

 

[Ron_dK]

Maybe this might help : post #6:

http://www.access-programmers.co.uk/forums/showthread.php?t=109744&highlight=form.Dirty

 

[gemma-the-husky]

docman - just a thought, as I know you know your stuff

but i always get concerned when anyone expects data in a parent form to be dependent on something in a subform. this just seems wrong.

items in a subform should be functionally dependent on the parent - not the other way round. if you have some setting in the subform affecting the parent - this to me seems like a normalisation problem.

 

[The_Doc_Man]

Ron, thanks for the reference, but it doesn't apply. The problem isn't with the sub-form misbehaving in any way that I can find.

Gemma, I may have said it wrong but the sub-form DEFINITELY depends on the parent.

Flow of the COMMITME_CLICK code :

Test for "don't do it" conditions and jump to the Exit_COMMITME_CLICK code if any of those conditions is true.

Call the field-level auditing code that remembers field updates (before the underlying record goes away and the .Value/.Oldvalue properties get changed by the coming save.)

Do the save using exactly the code that would have been produced by the button wizard. Of course, before I do that form operation to save the record, I put in all of that preliminary code, but when all is said and done, it is just a call with an acSaveRecord as the appropriate parameter.

Now look at an unbound text box and a bound combo box. The combo box shows the current status of the thing I'm tracking - a trouble-report internal to this project. The unbound text box is the action I just took.

Open a recordset to the same query as is used in the sub-form, .addnew, and store the values from the parent for current status, date, the person doing the update, and the action from that unbound text box. .Update the recordset (I'm using DAO) and .Close it. Then do a .Refresh of the sub-form. This works because on the form, the sub-form is read-only, no locks.

It is after that .Requery event that I found the spurious "dirty" flag even though it was (1) not data added through the form's GUI and (2) a .Requery ought to resolve all issues of a dirty sub-form and (3) Nothing on the parent form depends on the content of the sub-form. To the contrary, there is a TR ID number on the parent that is the link to select the sub-records of the sub-form. The sub-form depends on its parent.

I guess I have to chalk this up to experience, but if it happens that you reload a control with its own value, it still can make a form dirty - even though .Value and .Oldvalue are still the same. To me, it is bizarre. It means that the help for the .Dirty property isn't telling the whole story somehow.

Ah, well, time to move on.

 

[Banana]

Do the save using exactly the code that would have been produced by the button wizard. Of course, before I do that form operation to save the record, I put in all of that preliminary code, but when all is said and done, it is just a call with an acSaveRecord as the appropriate parameter.

A silly test, perhaps and very unlikely to help you any further but... Sometime I find it convenient to be more explicit in naming which form I want to save than trusting DoCmd to know which the active form is. So I typically do this:

Me.Dirty = False

Which AFAIK has the same effect as the DoCmd methods but allows to point to a form instance explicitly.

Open a recordset to the same query as is used in the sub-form, .addnew, and store the values from the parent for current status, date, the person doing the update, and the action from that unbound text box. .Update the recordset (I'm using DAO) and .Close it. Then do a .Refresh of the sub-form. This works because on the form, the sub-form is read-only, no locks.

I would point out that this is more likely to cause more headaches than its worth. My experience has been that it's almost always preferable to use the Recordset object and manipulate it than to run a separate query/recordset/whatever. Few reasons:

1) Regardless of the source, it will appear to the server as two different connections. Whether it comes from the same client is immaterial - the fact that it's a different connection may be sufficient to block the 2nd connection if there is a lock.

2) One should take the locking behavior with a grain of salt. With a ODBC source, the locking behavior is completely ignored, defaulting to that server's usual locking behavior. Then even with Access as the source, when it says "No Locks", what it really means is optimistic locking. Normally, that's not a problem... at least until one tries to modify data across two different connections.

3) Though Access claims to support record-level locking, it will fall back to page-level locking depend on circumstances. So even if the original connection did have record-level locking, it only takes one attempt at a page-level locking to fire back with a conflict.

For those reasons, I'm inclined to avoid running any kind of update queries against the same block of data that is in use by other objects (e.g. forms in this case). If I can, I use the forms' Recordset and manipulate data directly so it's all in a single connection.

This also simplify so many things: my edits to the Recordset's fields does not necessary require a Requery, setting the Dirty flag - it's as if I was at the user's keyboard and interrupting the user to type in some of my changes.

I guess I have to chalk this up to experience, but if it happens that you reload a control with its own value, it still can make a form dirty - even though .Value and .Oldvalue are still the same. To me, it is bizarre. It means that the help for the .Dirty property isn't telling the whole story somehow.

You say you checked the .Value and .OldValue.... Did you look at the Recordset.Fields(...).OriginalValue for the same field that's the source of the control?


HTH

 

[The_Doc_Man]

Banana, no I didn't look at .OriginalValue because to be honest, that one is "out of sight out of mind" most of the time. But that's a good suggestion.

As to the other stuff about separate recordsets for sub-forms... The sub-form doesn't show every field and I've had trouble with playing with the Form.Recordset properties directly due to events firing in the middle of other events when I do something to change a form state. Believe it or not, I've actually single-stepped through a case where the record in Me.Recordset points to the wrong record based on what I was able to pick up from it using Me.Recordset![TRID] to pick up the Trouble-Report ID number.

 

[Banana]

FWIW, just because there's no need to show fields on a form, doesn't mean it should be excluded from the form's recordset if it can help with the background processing.

You're right that manipulating the Recordset directly will fire other events - that's expected behavior. To be able to navigate or do other stuff without firing the UI events, you'd need to use RecordsetClone or Recordset.Clone (depending on whether you're using DAO or ADO). The only time we should be manipulating Recordset is when we also want to have "UI effects".

The part about getting wrong value stumps me, though....

 

[The_Doc_Man]

Yes, it stumped me beyond belief, too. Me.Recordset![fieldname] ought to give me the correct underlying value after I've done a save, but somehow it does not. And I don't recall an auto-navigate implied by a save, either. It was a real head-scratcher, but I've decided to move on to my next problem. Trust me, I've still got my share.

 

[gemma-the-husky]

out of interest, i am SURE i have had the same issue, and had combo boxes not remember the old value - so that if control<>control.oldvalue reported false, when it should have reported true.

I am sure i had to deal with it by using a variable, and saving the value on entry to the control.

i had no idea there may be another value (the originalvalue banana referred to)

---------------

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
bFrmSaysDirty = Me.Dirty

here's another thought.

temporarily put record selectors on BOTH the form/subform that this is trying to manage. you will then see which form entity this code is actually affecting, as the record selector will change from a pencil to a triangle.



----------------
with respect to the other point, then about the form/subform relationship, i still cannot see why any value in the subform should affect anything in the main form - ie your original point was that the setting in the main form was dependent on which subform record happened to be active - which sounds like a normalisation issue to me, as the parent shouldnt care about the subform. - HOWEVER - one way of achieving what you want, might be to just save the active subform record (key)somewhere - so that your mainform, or programme can retrieve any info it needs from the record directly.

 

[The_Doc_Man]

Dave, I must have awkwardly stated it earlier. The entry I make to the recordset underlying the child form depends on the status selected from the parent form's combo box, not the other way around.

The only reason I use a combo box on the child form is so I can define the status code (as the bound column) and have the translation of the status code show up as the column that is NOT zero width.

 

[dfenton]

I can't say I've been following this thread closely, but I just want to say that I *never* edit the underlying recordset of a form. I don't see the point! Just edit the form. The main reason I wouldn't do it is because I don't have any control of the relationships between the form's recordset and the edit and display buffers.

And, of course, I don't use DAO to update the table when I've got it loaded in the form. The form is for editing the data, and that's the single interface you should use for editing the data in code, just as you do in the UI.