Checking for a "Dirty" subform

[Guest]

I've got a form that views single records in a table, and the navigation
buttons are turned off. There's a subform with a datasheet view of a related
table, where there can be several records associated with the one record in
the main form.

The main table has a "last updated" field, and I've set the form up so that
when the user clicks on the OK button, if the form is dirty, the last updated
field is filled in before the form closes. I want to be able to do the same
thing if any records have been added or delete from the subform.

I finally figured out (I think) how to check to see if the subform is dirty.
I added the code, but the last updated field wasn't getting filled
in/modified, so I thought I was doing something wrong. After thinking about
it for a while, I realized that it was probably working fine, it was just
that the subform wasn't actually dirty by the time the OK button was being
clicked, as the new record had already been written into the associated table.

I have to guess that this isn't a bizarre scenario, and that there's
probably some standard way of dealing with it. If anyone can shove me in the
right direction, I'd appreciate it.

Thanks,
Rich

 

[Allen Browne]

The safest and most flexible solution would be to add a LastUpdated
date/time field to the subform table as well. Then use the BeforeUpdate
event of the subform to keep this up to date. (Presumably that's what you do
in the main form.)

On the main form, you can display the most recent LastUpdated value from the
subform's table for the related records with:
=DMax("[LastUpdated]", "MySubformTable",
"MyForeignKeyID = " & Nz([ID],0))

From there it is easy to show the more recent of that and the main form's
LastUpdated value.

A major advantage of doing it that way is that you can trace who updated
exactly which record when. You could use the AfterUpdate event of the
subform to write the value back onto the main form (and immediately save it
does not get undone), but then you really don't know if the main form record
was changed or not.

 

[Steve Schapel]

Rich,

Would it serve your purposes to use the Before Update event of the
subform, to modify the last updated field on the main form?

Me.Parent!last_updated = Date
(or whatever it is)

I guess that would mean that if you changed data in the subform, and
then changed data on the main form, the last updated field would get
processed twice... but, what the heck.

 

[Guest]

Thanks, Allen and Steve!

While being able to keep track of who modified what when is good in theory,
for this particular application it would be overkill: there are only a
handful of users, and the users are actually "groups," like "OfficeStaff" and
"OfficeAdmin," so it wouldn't be possible to tell if it was Bob or Carol or
Ted or Alice that made the change.

To set the LastUpdated date in the main form, I check to see whether or not
the form is dirty when the OK button is clicked, I don't use the BeforeUpdate
event. If I use the BeforeUpdate event, won't the date field get filled in
(and stored) even if the user clicks the "Cancel" button instead? Ah, but if
the "Cancel" button is clicked the changes are undone, and that would include
the date change, wouldn't it?

Using the BeforeUpdate event of the subform seems like a good idea for
setting the date on the main form. I'm curious, though: I'm used to using
the AfterUpdate event when deciding to do something; is there a compelling
reason for using BeforeUpdate vs. AfterUpdate, or vice versa?

Thanks again,
Rich

 

[Allen Browne]

It makes sense to use the AfterUpdate event of the subform to update the
field on the main form, and force the save.

It makes no sense to use the AfterUpdate event of the main form to instantly
dirty it again. That requires another save, which triggers Form_AfterUpdate
again, which dirties it again, and now it needs saving again. Ad infinitum.