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.

--
Regards,

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



"gmazza via AccessMonster.com" wrote:

> 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!
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200904/1
>
>

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.



"gmazza via AccessMonster.com" wrote:

> 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,
>
>
>
> PJFry wrote:
> >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!
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200904/1
>
>