audit trail

[clayton ledford]

Hello,

I have seen the main write-up on doing an audit trail, but i'm not sure that
I have the knowledge to implement it in an existing form.

I have a Form linked to a master table with one field: Account. My form
has a sub-form (datasheet) with the following:

Main_Form

Account (Pk)
Date_Added (pk)
Approved_Status
Pending_Status

So, the sub-form could show multiple records for the same account. Users
update only the "status" fields. I want to create an audit trail on the
"status" changes. I created a separate table with the following fields:

Audit_Table

Account (pk)
Change_Date_Time (pk)
Field_Changed
Change_Result

I want to add something to the after update event of the fields for
"Approved_Status" and "Pending_Status" that populates the audit table with
the appropriate information.

Thanks in advance for any guidance you can provide.

 

[Gina Whipp]

Clayton,

You could use an Append query... However, I am unclear of where you get
Change_Result from as it is not in the first table you listed. It is also
unclear to me as to exactly what you are trying to *keep track of*? Is it
that the field was changed and to what? Then how would Change_Result be
known at that time? Other then that field using an Append query sounds like
your best bet.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[clayton ledford]

Hello,

Sorry i should clarify - these are not my real table names and fields...
mine are slightly more complex. I can usually implement a solution based on
a generic answer. I have another field to track the user where i'll be using
the function fOSUsername() i added in another module. I'm tracking
specifically WHO made updates and when, as the status of an account changes
frequently.

For my example: The status fields each have various options. The
Change_Result field in the audit table is just to track what the user
selected as the status when they made the change. I was on the right track
with append queries, but I cannot get it to include the data in the account
field or the status fields, as the query does not recognize
Me.Approved_Status as an object to query.

Here's an example (in the after-update event of the field).

Sqlchange = "INSERT INTO Audit_Table (Account, Field_Changed, Change_Result)
SELECT Me.Account, 'Approved_Status', Me.Approved_Status"
db.execute(Sqlchange)

Is there some additional code i can use to get it to recognize the specific
record being updated other than the ME function?

Thanks again for your help.

 

[Gina Whipp]

Clayton,

Perhaps the confusion comes in because Me. is referring to the form and then
the field (after the Me.). Are you running this from a form and is that
field on the form?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[roccogrand]

Clayton,

There's an excellent discussion of audit-enabling Access applications in the
Access Cookbook by Ken Getz et.al.

I have used the procedure a few times in my applications and would say that
this section is worth the price of the book, if you need to do a lot of cool
things with Access. (I can't say that the process will work with Access 2007
- I have not implemented audit trails in the past couple years).

There is easy-to-follow instructions that will walk you through the process.

Unfortunately I can't find my copy at the moment or I would try to help more.

HTH

David

 

[clayton ledford]

This question can be closed. I was able to figure out a solution on my own.
In case someone reads this...

I created an un-bound text box on the form (not visable to user). For any
field that is changed, the text box is set to match that value. Then, you
can run an append query to add to your audit table:

Me.Text3 = Me.Approved_Status
Sqlchange = "INSERT INTO Audit_Table (Account, Field_Changed, Change_Result)
SELECT [Forms]![My_Form]![Account], 'Approved_Status',
[Forms]![My_Form]![Text3]"
db.execute(Sqlchange)

It's a little time-consuming to add code to the after-update of every field,
but it's exactly what i was trying to accomplish with my audit.

Thanks anyway for your help guys.

Clay

 

[Armen Stein]

I created an un-bound text box on the form (not visable to user). For any
field that is changed, the text box is set to match that value. Then, you
can run an append query to add to your audit table:

Hi Clayton,

This technique tracks every change on the form, even before they are
committed to the database. This will sometimes result in incorrect
audit entries.

For example, if your user types in a value (say "ABC") then tabs to
the next field, "ABC" will be written to your audit table. Then they
can go back and change the value to "DEF" and it will also be written
to the audit table, even though "ABC" was never actually updated in
the database.

Even worse, after making a change, the user could undo their changes
using the Esc key, then close the form. Now your audit table will
have an entry that was never made in the database.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Gina Whipp]

Clayton,

Thank you for the update, however, I have to agree with Armen OR is that
what you were/are aiming for?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

This question can be closed. I was able to figure out a solution on my
own.
In case someone reads this...

I created an un-bound text box on the form (not visable to user). For any
field that is changed, the text box is set to match that value. Then, you
can run an append query to add to your audit table:

Me.Text3 = Me.Approved_Status
Sqlchange = "INSERT INTO Audit_Table (Account, Field_Changed,
Change_Result)
SELECT [Forms]![My_Form]![Account], 'Approved_Status',
[Forms]![My_Form]![Text3]"
db.execute(Sqlchange)

It's a little time-consuming to add code to the after-update of every
field,
but it's exactly what i was trying to accomplish with my audit.

Thanks anyway for your help guys.

Clay

Clayton,

There's an excellent discussion of audit-enabling Access applications in
the
Access Cookbook by Ken Getz et.al.

I have used the procedure a few times in my applications and would say
that
this section is worth the price of the book, if you need to do a lot of
cool
things with Access. (I can't say that the process will work with Access
2007
- I have not implemented audit trails in the past couple years).

There is easy-to-follow instructions that will walk you through the
process.

Unfortunately I can't find my copy at the moment or I would try to help
more.

HTH

David