[AccessD] Watching data

Arthur,

It's never easy and this is especially not easy.  ;-)  A user can start to
edit a record and hit escape.  A user can start to edit a record and have
validation fail to allow the save, or the user can have a nice clean edit /
save.  Even in the case of a clean save, by the time AfterUpdate occurs the
previous value isn't available.  Thus you have to save the old value to a
temp location in BeforeUpdate and then save the old and new value to the log
table in AfterUpdate.

Now, what about forms where the form has more than one editable table
represented?  I have very few of those but I have one in particular where
the user wanted data from about 4 different (related) tables all available
in one screen and all editable.  So what is the PK?  In fact, what is the
table?  What about controls bound to queries with aliased fields where the
name was changed by the developer in the underlying query?  I HATE "one of a
kind, do it with code in this specific form, for this specific form"
solutions.  I prefer to take the time to figure out how to make it work
across a broad spectrum of situations if possible.

I asked awhile back if it was possible to "drill down" through a bound
control to find the table / field name of the field that a control is bound
to and was told it is indeed possible.  It seems that to do it right, any
changed CONTROLS would have to be "drilled down" to discover their source,
then that info (table / field name) as well as old/new values recorded in
the log file.

It just so happens that I have a form class and a control class for every
data aware control so I could have my form class' form events poll the
control classes to see which controls have changed, do the "drill down"
however that may occur, and record the results.  Or, every control class
could be taught how to record changes in the Change Log and then the form
would poll all the control classes telling them to LogChanges (if any).  I
think it would be easier to teach the control classes to automatically
record changes, then have the form just poll the control classes for old/new
values if they have changed, and have the form class do the logging.  In
either case, if I could figure out the drill down thing and get this
working, in essence I could turn on logging with a switch as the form loads.
In it's Open even tell the form class to turn on logging for this specific
form.

If you don't have a framework such as mine how do you do this?  For that
matter, if you DO have a framework such as mine, how do you do this?  ;-)
Curious minds want to know.

John W. Colby
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Arthur Fuller
Sent: Saturday, February 28, 2004 5:05 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Watching data


How interesting and convenient that you should raise this issue, as I am
facing a similar problem on an inherited app. While I am free to
restructure it, I also don't want to destroy everything done by previous
developers (not least for political reasons, but also because I want to
deliver the solution as inexpensively as possible). So...

The table of interest contains several fields which can be changed, and
parallel fields recording the date they were last changed. Obviously I
can add some code to each of the first set's AfterUpdate event and
record the date. But I'm thinking that it would be way cooler to do it
all in the form's AfterUpdate event instead. IIRC I don't think that I
have ever used this event in all my years of Access programming.

>From this event, can you determine which fields have changed? For
example, the user changes five fields. Can I somehow obtain a list of
the changed fields? Maybe I want the BeforeUpdate event instead? Ideally
what I would like to do is let the user change anything, then write the
changes to an AuditTrail table or somesuch, having a structure something
like this:

Table
PK
FieldName
PreviousValue
NewValue
DateTime

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Saturday, February 28, 2004 4:04 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Watching data


Well... the data isn't normalized to that extent, i.e. I don't have an
"Address table".  A claimant has a single address, and that is embedded
directly in the Claimant table.  Thus a timestamp would only tell me
that the claimant table had changed, not that the address portion of the
table had changed.  Maybe she got married and changed her name?  The
Date of Birth was corrected?  The name was mis-spelled and the
mis-spelling was corrected?

John W. Colby
www.ColbyConsulting.com