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