[AccessD] Watching data

Arthur Fuller artful at rogers.com
Sat Feb 28 16:05:12 CST 2004


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

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


Hi John

Wouldn't it be much easier to add timestamps and track these? If the
timestamp of an address is newer than that recorded for the case,
"something else" has changed the address.

/gustav


> I need a system for watching specific data fields in specific tables 
> for changes.  For example, if the Policy holder address changes, the 
> claimant address changes, the Payment location (address) changes etc.

> If ANY of these change then I need to gather the information and at 
> the end of the
day
> email a report to the client (the insurance company) spelling out the 
> changes, what object the fields belonged to (Claimant, Policy Holder
etc.).

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list