Start Free Trial

asked on

Creating an Audit Trail

Hello,

I am creating an access db that will be used by several people. The purpose of the db is to store contact information for several different positions within a great number of lines of business. What I want to be able to do is keep an audit trail of each time someone goes in and updates a contact. I would want the user id of the person making the change, the date of the change, and the name that the contact was changed too. What is the easiest way of going about doing this?

Thanks,
John

look at this example:

http://www.itsgemeiner.com/service/EE/recordchange_w2.mdb

you find here a complete example, which takes care about all cahnges including new and deleted records

Regards, franz

Rey Obrero (Capricorn1)

check here

How to create an audit trail of record changes in a form in Access
http://support.microsoft.com/?kbid=197592

Create a table tblAudit:

ChangeId ---> Autonumber
Contact ----> Text
UpdatedBy ----> Text
UpdatedTime ----> Date

This code goes in the after update event of Contact text box:

Private Sub Contact_AfterUpdate()
CurrentDb.Execute "Insert into tblAudit (Contact, UpdatedBy, UpdatedTime) values ('" & Me!Contact & "', Environ('UserName'), Now())"
End Sub

The easiest way to do this is to create a separate table that will hold your changes data. Lets say that you have a Contact form set up with the following fields: ID,Contact Name and Phone . You will need another table (tblAudit)that has: ID, Change Creator, Change Date, Name Changed To.

Now here is the code you could use. :

These first two pieces should be put in a separate module called UserNm . This will be used to get the NT User name.

Private Declare Function GetUserNameA Lib "advapi32.dll" _ '--------------This and the Get User
( _
ByVal lpBuffer As String, _
nSize As Long _
) As Long

Public Function GetUserName() As String
Dim strName As String * 255
GetUserNameA strName, 255
GetUserName = Left$(strName, InStr(strName, Chr(0)) - 1)
End Function

This is the meat of it.Put this on the AfterUpdate event of your form:

Sub Audit()

Dim db as ADODB.Connection
Dim rst as New ADODB.Recordset

Set db = CurrentProject.Connection

'------Get values from form
strID=me.ID
strChangeCreator= UserNm.GetUserName
strNameTo=me.Contact Name
strDate=Date

'----Build recordset for the table to add to (tbl,Audit)
strSQl = " SELECT tblAudit.ID, tblAudit.Change Creator, tblAudit.Change Date, tblAudit.Name Changed To FROM " &_ " tblAudit"

rst.Open strSQl,db,adLockOptimistic,adOpenKeyset

'''-----Update table with values from form
With rst
.Addnew
.Fields(0)=strID
.Fields(1)=strUSer
.Fields(2)=strDate
.Fields(3)=strNameTo
.Update
End With

End Sub

ASKER

Nestorio,

Since your answer will be the quickest. I will try it first. However where is this place where I am supposed to paste the code? You say: "This code goes in the after update event of Contact text box:". Where is that?

Thanks,
john

ASKER

franz, thanks for the link. could you please give me a quick overview of how this whole thing works though?

thanks,
john

John,

Edit the form with the Form Designer.

Right click over the Contact text box. From the emergent menu choose Properties. The Properties Windows should open. Select the Events tab, and select the after update event. Click on the button with periods [...]. The module editor will open. You can paste the code there. Check the field names be the actual names.

ASKER

Nestorio,

Think we are almost there. I have the code in and it works up to the point of "enviorn". I am not great with code. What could be the problem there?

Thanks,
John

ASKER

Nestorio,

I get a run-time error '3085': Undefined function environ in expression'

thanks....

ASKER

Nestorio,

I have user id's set up for whoever enters the db. they put there user id into a form prompt and if correct, they are let into the db. not sure if that info. mattered or not.

John,

Is that information about user id stored in a table? Or is there a public variable that holds that value?

ASKER

stored in a table called: tUsers

Ok, does that table hold the current user id? I mean a flag or maybe the table has a unique record with the current user id.

ASKER

yes it does. we use an nb id to get in. in this case i logged into the db with mine.

ASKER

the feild in tusers is called NB_ID

I think tusers table hold all user data that could access the database, but how can I determine who is the users that is currently logged in?

ASKER

i am not sure. would you suggest a different way of user log on in order to capture this information?

ASKER

here is the script i use for the form users are prompted with when they try to log in:

Private Sub butEnter_Click()
Call Validate_LogIn
End Sub

Private Sub txtNBID_LostFocus()
mstrNBID = Trim(txtNBID.Text & " ")
End Sub

Private Function Validate_LogIn()
Dim strMessage As String
On Error GoTo Validate_LogIn_ERR

If Len(mstrNBID) = 0 Then
strMessage = ""
strMessage = "Please enter your NB ID."
MsgBox strMessage, vbCritical, "Error!"
GoTo Validate_LogIn_Exit
Else
If IsNull(DLookup("[NB_ID]", "tUsers", "[NB_ID]=" & "'" & mstrNBID & "'")) Then
strMessage = ""
strMessage = strMessage & mstrNBID & " is not a valid NB ID for this application." & vbCrLf & vbCrLf
strMessage = strMessage & "Please contact the system administrator."
MsgBox strMessage, vbCritical, "Error!"
GoTo Validate_LogIn_Exit
Else
DoCmd.Close acForm, "fLogin", acSavePrompt
DoCmd.OpenForm "fLogOff", acNormal, , , acFormReadOnly, acHidden
DoCmd.OpenForm "ISelector", acNormal, , , acFormEdit, , mstrNBID
strMessage = ""
strMessage = strMessage & "Welcome "
strMessage = strMessage & DLookup("[Name_First]", "tUsers", "[NB_ID]=" & "'" & mstrNBID & "'") & " "
strMessage = strMessage & DLookup("[Name_Last]", "tUsers", "[NB_ID]=" & "'" & mstrNBID & "'") & "."
MsgBox strMessage, vbInformation, "Welcome!"
End If
End If

Validate_LogIn_Exit:
Exit Function

Validate_LogIn_ERR:
MsgBox Error$
Resume Validate_LogIn_Exit
End Function

Ok, let me see...

It seems that the variable wich holds the login information is mstrNBID.

So try changing the code in the after update event to this:

Private Sub Contact_AfterUpdate()
CurrentDb.Execute "Insert into tblAudit (Contact, UpdatedBy, UpdatedTime) values ('" & Me!Contact & "', mstrNBID, Now())"
End Sub

ASKER

it is saying: too few parameters

Sorry, try this:

Private Sub Contact_AfterUpdate()
CurrentDb.Execute "Insert into tblAudit (Contact, UpdatedBy, UpdatedTime) values ('" & Me!Contact & "', '" & mstrNBID & "', Now())"
End Sub

ASKER

I got it to work but instead of giving the nb id that is currently logged in. mine would be nba5r8i. it just puts mstrnbid in the updatedby field....

ASKER

actually, in my last post i was using 'mstrnbid' where you have '" & mstrNBID & "' - that is what is producing the mstrnbid in the updated by field. using the script you just gave me, it produces nothing. no error message and no entry in the tblaudit.

Have you tried the last I've posted?

ASKER

see my last post. in addition to that, i found that after i tried your last post and then switched back to using just 'mstrnbid' the autoid created took into account my attempts to use "'" & mstrNBID & "'. yet, those attempts to use '" & mstrNBID & "' were not recorded in the table...

ASKER

see below:

Change_ID       Business_Unit_Executive Updated_By        Updated_Time
1       Tim Arnoult        mstrNBID        11/9/2004 4:27:09 PM
2       Timm Arnoult        mstrNBID        11/9/2004 4:27:13 PM
3       Tim Arnoult        mstrNBID        11/9/2004 4:27:20 PM
8       Timm Arnoult        mstrnbid        11/9/2004 4:31:07 PM
11       Tim Arnoult        mstrNBID        11/9/2004 4:35:19 PM

Ok, let me test it.
Btw, I will be out for a while.

ASKER

Nestorio, i am leaving for the day. I will catch up with this in the morning. Thanks for your help today.

John

John,

Try defining the variable as public. Put this at the beggining of the module, out of the Subs.

public mstrNBID as String

Use this procedure for the after update (I'm repeating it here)

Private Sub Contact_AfterUpdate()
CurrentDb.Execute "Insert into tblAudit (Contact, UpdatedBy, UpdatedTime) values ('" & Me!Contact & "', '" & mstrNBID & "', Now())"
End Sub

ASKER

Nestorio,

this is the code i have now. it is not working. same thing as before, no error message pops up but there is entry posted to the table either...

Public mstrNBID As String
____________________________________________________________________________________________________
Private Sub Business_Unit_Executive_AfterUpdate()
CurrentDb.Execute "Insert into tbl_Audit (Business_Unit_Executive, Updated_By, Updated_Time) values ('" & Me!Business_Unit_Executive & "', '" & mstrNBID & "', Now())"
End Sub

Just to debug, enter this at the top of the module (if not entered yet)

Option Explicit

and in the after update event, a MsgBox(),

Private Sub Business_Unit_Executive_AfterUpdate()
MsgBox (mstrNBID)
CurrentDb.Execute "Insert into tbl_Audit (Business_Unit_Executive, Updated_By, Updated_Time) values ('" & Me!Business_Unit_Executive & "', '" & mstrNBID & "', Now())"
End Sub

jz1english & Nestorio

you like it the hard way.

why not write the toal record to a history table ?

again: http://www.itsgemeiner.com/service/EE/recordchange_w2.mdb

regards, Franz

ASKER

Nestorio,

this is what i have now. the only thing that changed is that when i change the name in the record, i get a pop up box that with an 'ok' button. no listing in the audit table though.

Option Explicit

Public mstrNBID As String
__________________________________________________________________________________________________
Private Sub Business_Unit_Executive_AfterUpdate()
MsgBox (mstrNBID)
CurrentDb.Execute "Insert into tbl_Audit (Business_Unit_Executive, Updated_By, Updated_Time) values ('" & Me!Business_Unit_Executive & "', '" & mstrNBID & "', Now())"
End Sub

Franz,

I can´t see your database (unrecognized format error). Maybe this is because I'm using Access 97.
Btw, I don´t think my approach is difficult to implement. All we need is to detect where is stored the User Id information.

Regards

I will continue searching.
You can take a look at Franz link (I can´t see the database as explained before).

ASKER

I already have looked at Franz's db and agree with you. I think your approach is easier. We just need that one bit of info.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Got it Nestorio! Thanks again for all your help. I appreciate it...

Glad to have helped :)

ASKER

Hey Nestorio,

I just set a up a new issue in relation to this one. Can you take a look when you get a chance. You know my DB pretty well so I wanted to see what you thought. Thanks...

https://www.experts-exchange.com/questions/21207539/Audit-Trail-for-Form.html