[AccessD] Audit Trails

To a man with a hammer, everything looks like a nail <g>

I certainly wouldn't try to trigger an audit trail at the control level. The logical place is at the 
time you insert, update and delete a record.   

For the insert, you don't need to store any info about the record other than its PK.

For the delete, you need to store all fields for the record whether they are displayed on the 
deleting form or not.

In neither of these cases do you need to iterate through controls.  It's only the Edit audit that 
needs iterate through the fields exposed to controls on the editing form and record which 
ones have changed.

There is a lot to be said tor using three different audit tables for the three different cases.
(That's exactly the way I've done it in the past in SQL Server using triggers).

-- 
Stuart


On 18 Jul 2009 at 16:48, jwcolby wrote:

> Sounds like a case for a class, sinking the events for the control.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Rocky Smolin wrote:
> > Just Googled Access Audit Trail and got a couple of hits.  This KB seems to
> > be the most popular: http://support.microsoft.com/kb/197592
> > 
> > Looks like I may have to roll my own for edits - but not too hard - the
> > OldValue is available in the BeforeUpdate event.  So there I could call a
> > Public function, pass the form name, cycle through the controls looking for
> > controls that are bound, and create the appropriate audit trail records for
> > the changed fields. 
> > 
> > It looks, however, like I'd need a second function for the New records added
> > to the table and another function for Deletes.
> > 
> > Rocky
> > 
> >  
> > 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
> > Sent: Saturday, July 18, 2009 7:40 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: [AccessD] Audit Trails
> > 
> > Dear List:
> > 
> > I am developing an application where the user needs to record changes to
> > fields in several tables - table name, field name, old value, new value,
> > date/time of change, user name.
> > 
> > What is the best approach to this? 
> > 
> > Is .OldValue available after the record has been updated?  If so I could
> > loop through the bound fields checking for changes and add an audit trail
> > record if changed. 
> > 
> > It would be nice to have this audit trail function packaged in one function
> > that could be called form anywhere in the app since several tables will have
> > the requirement.
> > 
> >  
> > 
> > MTIA
> > 
> >  
> > 
> > Rocky
> > 
> >  
> > 
> > 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com