Audit Trail help - Passing values to audit table.

[gmazza via AccessMonster.com]

Hi there,
I have searched many of the audit trail posts and still have yet to see my
issue, so maybe someone could help.

I have an audit trail table that has the following records: audID, audType,
audUser, FieldName and NewValue.
I need to track it so if a user makes a change to a field, the name of the
field on the form goes into the FieldName field in the table, and the change
that was made goes into the NewValue field in the table.

Yes I know I will have 1 record for each field being updated which will
result in many records in my audit table for 1 record change, but this is
what is needed.
How can I go about getting the field name, and the updated value, only if it
was updated, and then written to the audit table?
It would act the same for an Insert, a record for each field in the table.
For a delete there would be 1 record, audType being 'Delete', FieldName would
be blank, and NewValue would say 'Record has been deleted'

Thanks in advance!

 

[PJFry]

I'm right with you until:
It would act the same for an Insert, a record for each field in the table.
For a delete there would be 1 record, audType being 'Delete', FieldName would
be blank, and NewValue would say 'Record has been deleted'

We can get to the rest of the question after we take care of this.

I always encourage users to approach whole record deletions very carefully.
Once it's gone, that's it. Consider instead using an active/inactive flag.
Set your query to only read active records. This allows you to use the same
audit table that we will use for the rest of the form to record the
deactivation. It also removes the need to record the creation of a record.
A simple dtmCreate field that has an default value of Now() does the trick.
It's just one less step. The user never needs to see it.

Give me your thoughts on that and we can get started.

 

[gmazza via AccessMonster.com]

Thanks for your reply!
What I mean by acting the same for an Insert is on an Insert, I need my audit
table to look like this:
1st record: Created By: Create Date: audType: Field Name: New Value:
pjfry Apr 9, 2009 Insert First Name
Joe

2nd record: Created By: Create Date: audType: Field Name: New Value:
pjfry Apr 9, 2009 Insert Last Name
Blow

etc.

Then for a delete, there would only be 1 record looking like this:
1 record: Created By: Create Date: audType: Field Name: New Value:
pjfry Apr 9, 2009 Delete blank
'Record has been deleted'

To me, this isn't the best way to take care of Inserts, Updates, or Deletes,
but it is my requirement, and although I have argued against it, this is how
I'm supposed to get it done.

I totally agree with you, a flag for deletes is the way to go, how often are
you really deleting records, so its not like there will be tons of them. Plus
how many times are deletes either needed back, OR at least the information of
what was deleted is needed. I'm just doing what is told and expect to change
it in the future as needed

Thanks for your help on this, I assume I need to do some looping in a module,
then grab the actual name of the field, then grab the new value put in, and
record it to the table. I just don't know where it all goes or where to call
it from, or how to grab that info,

I'm right with you until:
It would act the same for an Insert, a record for each field in the table.
For a delete there would be 1 record, audType being 'Delete', FieldName would
be blank, and NewValue would say 'Record has been deleted'

We can get to the rest of the question after we take care of this.

I always encourage users to approach whole record deletions very carefully.
Once it's gone, that's it. Consider instead using an active/inactive flag.
Set your query to only read active records. This allows you to use the same
audit table that we will use for the rest of the form to record the
deactivation. It also removes the need to record the creation of a record.
A simple dtmCreate field that has an default value of Now() does the trick.
It's just one less step. The user never needs to see it.

Give me your thoughts on that and we can get started.

Hi there,
I have searched many of the audit trail posts and still have yet to see my

[quoted text clipped - 16 lines]

Thanks in advance!

 

[PJFry]

The insert issue:

You are wanting to insert two rows into the audit table, one for Joe, one
for Blow. But that won't be two records, correct? That should be one record
with two fields.

Assuming that is case, lets start with this code, which you will use on your
After Update even on what ever field you are working on:

Dim sSQL As String

sSQL = "INSERT INTO tAudit (audType,audUser,FieldName,NewValue) VALUES " & _
"('Change','" & fOSUserName() & "','FName','" & Me.FName.Value & "')"

DoCmd.RunSQL sSQL



fOSUserName() is a function that is commonly used to grab the users network
ID. If there a different value you want to use, just replace the fOSUsername.

So what this will do is record what the audType (Change)is, the person
making the change (fOSUserName()), the field being changed (FName) and the
New Value (Me.FName.Value).

Let's start with this and build up.
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

Thanks for your reply!
What I mean by acting the same for an Insert is on an Insert, I need my audit
table to look like this:
1st record: Created By: Create Date: audType: Field Name: New Value:
pjfry Apr 9, 2009 Insert First Name
Joe

2nd record: Created By: Create Date: audType: Field Name: New Value:
pjfry Apr 9, 2009 Insert Last Name
Blow

etc.

Then for a delete, there would only be 1 record looking like this:
1 record: Created By: Create Date: audType: Field Name: New Value:
pjfry Apr 9, 2009 Delete blank
'Record has been deleted'

To me, this isn't the best way to take care of Inserts, Updates, or Deletes,
but it is my requirement, and although I have argued against it, this is how
I'm supposed to get it done.

I totally agree with you, a flag for deletes is the way to go, how often are
you really deleting records, so its not like there will be tons of them. Plus
how many times are deletes either needed back, OR at least the information of
what was deleted is needed. I'm just doing what is told and expect to change
it in the future as needed

Thanks for your help on this, I assume I need to do some looping in a module,
then grab the actual name of the field, then grab the new value put in, and
record it to the table. I just don't know where it all goes or where to call
it from, or how to grab that info,

I'm right with you until:
It would act the same for an Insert, a record for each field in the table.
For a delete there would be 1 record, audType being 'Delete', FieldName would
be blank, and NewValue would say 'Record has been deleted'

We can get to the rest of the question after we take care of this.

I always encourage users to approach whole record deletions very carefully.
Once it's gone, that's it. Consider instead using an active/inactive flag.
Set your query to only read active records. This allows you to use the same
audit table that we will use for the rest of the form to record the
deactivation. It also removes the need to record the creation of a record.
A simple dtmCreate field that has an default value of Now() does the trick.
It's just one less step. The user never needs to see it.

Give me your thoughts on that and we can get started.

Hi there,
I have searched many of the audit trail posts and still have yet to see my

[quoted text clipped - 16 lines]

Thanks in advance!

 

[gmazza via AccessMonster.com]

Thanks for your reply!
The thing is I do need 2 separate records, 1 for Joe and 1 for Blow. I only
have 2 fields on the table that I need from the form, that being the
FieldName and the NewValue. So the 1st record will have First Name(for the
FieldName column), then Joe(for the NewValue column), 2nd record having Last
Name, then Blow.

Let me know if we're on the same page and then we can go from there
Thanks!

The insert issue:

You are wanting to insert two rows into the audit table, one for Joe, one
for Blow. But that won't be two records, correct? That should be one record
with two fields.

Assuming that is case, lets start with this code, which you will use on your
After Update even on what ever field you are working on:

Dim sSQL As String

sSQL = "INSERT INTO tAudit (audType,audUser,FieldName,NewValue) VALUES " & _
"('Change','" & fOSUserName() & "','FName','" & Me.FName.Value & "')"

DoCmd.RunSQL sSQL

fOSUserName() is a function that is commonly used to grab the users network
ID. If there a different value you want to use, just replace the fOSUsername.

So what this will do is record what the audType (Change)is, the person
making the change (fOSUserName()), the field being changed (FName) and the
New Value (Me.FName.Value).

Let's start with this and build up.

Thanks for your reply!
What I mean by acting the same for an Insert is on an Insert, I need my audit

[quoted text clipped - 52 lines]

 

[gmazza via AccessMonster.com]

Hey PJFry,
Just wondering if you are still helping me with this?
Thanks.

The insert issue:

You are wanting to insert two rows into the audit table, one for Joe, one
for Blow. But that won't be two records, correct? That should be one record
with two fields.

Assuming that is case, lets start with this code, which you will use on your
After Update even on what ever field you are working on:

Dim sSQL As String

sSQL = "INSERT INTO tAudit (audType,audUser,FieldName,NewValue) VALUES " & _
"('Change','" & fOSUserName() & "','FName','" & Me.FName.Value & "')"

DoCmd.RunSQL sSQL

fOSUserName() is a function that is commonly used to grab the users network
ID. If there a different value you want to use, just replace the fOSUsername.

So what this will do is record what the audType (Change)is, the person
making the change (fOSUserName()), the field being changed (FName) and the
New Value (Me.FName.Value).

Let's start with this and build up.

Thanks for your reply!
What I mean by acting the same for an Insert is on an Insert, I need my audit

[quoted text clipped - 52 lines]