Audit Trail

[boblarson]

Try these:

 Function fGetWinUserName()
    fGetWinUserName = VBA.Environ("UserName")
End Function      ' fGetWinUserName
 
 Function fGetComputerName()
    fGetComputerName = VBA.Environ("COMPUTERNAME")
End Function      ' fGetComputerName

Note: can use Environ("UserName") or Environ("COMPUTERNAME") but not for all access versions.

This is even better -
http://www.mvps.org/access/api/api0008.htm

 

[aftershokk]

Thanks to All!!!!

I used ("UserName") and it worked!!!

You are the best!

 

[fenhow]

Lyn Worked But..

I was having a problem with auditing and foud Lyn's solution. It worked to a point however here is what I am facing.

I have one main form and one sub form. The audit works great on the sub form but on the main form I get this error

"Compile Error: Invalid use of property" When I remove the following code my audit on the subform works but of course not on the main form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

Call AuditTrail(Me)

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit
End Sub

I guess I am asking how can I run an audit on the main form and the subform at the same time?

Thanks
Fen How

 

[fenhow]

Help Please?

I may have posted this in another thread by mistake. Forgive me if if this is the case.

I was having a problem with auditing and foud Lyn's solution. It worked to a point however here is what I am facing.

I have one main form and one sub form. The audit works great on the sub form but on the main form I get this error

"Compile Error: Invalid use of property" When I remove the following code my audit on the subform works but of course not on the main form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

Call AuditTrail(Me)

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit
End Sub

I guess I am asking how can I run an audit on the main form and the subform at the same time?

Thanks
Fen How

 

[boblarson]

Call AuditTrail(Me)

I may be off here, but should it not be:

Call AuditTrail(Me.Name)

 

[fenhow]

I tried that, it seems to halt on:Call Audit Trail

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo Form_BeforeUpdate_Err

Call AuditTrail(Me.Name)

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit
End Sub

Fen

 

[Banana]

For those with a mature database and too lazy to add the needed fields, here's a shotgun approach:

Private Sub AddAuditTrailField()

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim SkipTable As Boolean
Const SystemObject As String = "MSys"

Set db = CurrentDb

For Each tbl In db.TableDefs
    SkipTable = False
    If SystemObject <> Left(tbl.Name, 4) Then
        For Each fld In tbl.Fields
            If fld.Name = "MemAuditTrail" Then
                SkipTable = True
            End If
        Next fld
        
        If Not SkipTable Then
            Set fld = tbl.CreateField("MemAuditTrail", dbMemo)
            tbl.Fields.Append fld
        End If
    End If
Next tbl

Set fld = Nothing
Set tbl = Nothing
Set db = Nothing

End Sub

I say shotgun because it will add field to *all* tables that doesn't have it, whether you need it or not.

Now, if table had a tag property or something like that....

 

[sqaengineer]

Hi All,

I am using infopath to connect to a Access database for an application. I would like to track the changes done to a record using the infopath form. Would like the changes to be tracked in a seperate table and display them using infopath when the record is currently viewed.

access and infopath are 2007 versions.

any ideas on how to do the same.

Thanks much

 

[aftershokk]

I have installed audit trail but when I edit an existing data field and then try to move to the next record I get a 3251 error - Operation is not supproted for this type of object

help!

 

[Gabriello]

I'm having the same problem with error 3251

I added the Audit Track which work great on the zipped file. But when I update the record on my db I get an error 3251. Does anyone know what that means? And especially how to correct it?

Gabriel

 

[Gabriello]

Hi, Were you able to figure out the error?
Gabriello

 

[boblarson]

Since I don't use this, I'm not completely sure about this, but putting error 3251 into Google yielded many results including this:

http://support.microsoft.com/kb/289675

If you Google it, it might help you figure out the problem.

 

[ed_the_unlucky]

wow

i'm absolutely blown away at how giving the regulars are here! when my friend suggested that i google for problems i'm experiencing, i had no idea i'd hit such a gold mine of information!

i'm learning access (2003 but really only learned 97) and am working on a project for which i need an audit trail (big surprise i'm sure).

unfortunately, i'm a VBA novice and fear i've bitten off more than i can chew: i know enough to know that i don't know enough.

my issue: i'm trying to write audit data to a set of tables mimicking the main tables, rather than popping it into a memo field. but it seems to me that the code presented here should do the trick so long as i point it to the correct tables/fields, correct?

i've spent the past few hours trying to coax an append query to write unbound fields in a form to an audit table. needless to say i'm more than mildly frustrated. i really am trying to learn this for myself, but the gap b/n the knowledge i possess and the tools (such as this thread) appears pretty darned vast!

uncle gizmo: thank you for posting the code that grabs the windows username--much obliged! i just happened to be looking for something that would do that!

 

[Purpleswanage]

Hi! Being relatively new to VBA this piece of code was a God Send to find and works brilliantly. Thank you.
The fields I am tracking are linked to tables which have two columns. The first is the unique ID and the second being the 'description'. The audit trail report returns the changed values by their unique ID what I am wondering is whether is is possible for the report to show the description? I.E Instead of returning 'Originator == Previous Value was 1' it returns 'Originator == Previous Value was John Smith'
Hope this makes sense and thanks in advance for any help you can give.
Helen

 

[khwaja]

Last one was fairly old post so I am hoping someone can help. I have the issue raised by Lyn and he changes Lyn has suggested are not consistant with the code. Could ghudson pl review and amend the code to allow for a sub form can be used for audit trail. Thanks.

 

[valheru]

Hi All

I created an audit trail which works completely different to all suggestions in this post which I thought I would share with you.

The forms I create are not linked directly to a recordset, so any adding, editing etc takes place through code which is triggered for instance when the user clicks add. Using the .oldvalue only works for forms which are linked directly to a record source.

What I have done is create a routine that when a person calls up patient information in the system, each field value is loaded into it's own variable. When a user changes something on the form and clicks the update button, each field is compared to the value stored in the variable. Any changes are then recorded in an audit trail table.

I know this is time consuming, especially when you have forms with large number of fields, but it was the only way i could get around it without linking directly to a specific record source.

 

[khwaja]

Thanks. I can possibly choose selected fields which are nmore important and then use your technique. Does your system recod all historical changes made to a record? If so, do you archive them outside the database? Otherwise DB size would inflate quite a bit. If you could kindly share your code with me, I will really appreciate. You can email me on akhwaja_at_woolworths.com.au.

 

[filfil]

I'm not sure if someone post such solution, but here how I got my two levels forms Audit Trail working:

I used ghudson's Audit Trail + the changes offered by Lyn Mac in page 1, and hooi's hint in page 3,

In the main form, i used to get "Operation is not supported for this type of object." error, and the audit trail field tells that admin made changes, but does not not specify the changes. the problem was that I'm using a select statement in the Record Source of the main form instead of the table because i need to take data from two tables. the only field from the other table is Emp_ID. so, in the module of audit trail i changed this line:
If ctl.Name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.

to:
If ctl.Name = "tbAuditTrail" Or ctl.Name = "EmpID" Then GoTo TryNextControl 'Skip AuditTrail field.

and so far it works fine ^^

 

[Darth Vodka]

damn i only just discovered this thread after figuring the blumming thing out on my own

bravo to ghudson and lyn

here's what i currently have

Sub AuditTrackObject(strWhatHappened As String, varRecord As Variant)
    On Error GoTo ErrorPlace
    
    Dim rs                  As Recordset
    Const AUDIT_TABLE       As String = "tbl_AuditLog"
    Dim strFrom             As String
    Dim strTo               As String
    Dim strField            As String
    
    strFrom = Nz(Screen.ActiveControl.OldValue, "Null")
    strTo = Nz(Screen.ActiveControl.Value, "Null")
    strField = Nz(Screen.ActiveControl.Name, "Null")
    varRecord = Nz(varRecord, "Null")
    
    If IsMissing(strFrom) = False And IsMissing(strTo) = False Then
        If strFrom = strTo Then
            Exit Sub
        End If
    End If
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM " & AUDIT_TABLE)
        rs.AddNew
        rs.Fields("Username").Value = NetworkID()
        rs.Fields("ActionDescription").Value = strWhatHappened
        rs.Fields("From").Value = strFrom
        rs.Fields("To").Value = strTo
        rs.Fields("Field").Value = strField
        rs.Fields("Record").Value = varRecord
        rs.Fields("WGName").Value = CurrentUser
        rs.Fields("Database").Value = Right(CurrentProject.FullName, 50)
        rs.Fields("Machine").Value = Environ$("computername")
        rs.Update
        rs.Close
    Set rs = Nothing
    Exit Sub
    
ErrorPlace:
    Set rs = Nothing
    
    With Err
        'ErrorLog .Number, .Description & " field" & strField & " record " & varRecord, "modAudit", "Auditracking"
    End With
    
End Sub

works fine, but i have to stick it on all the objects...didn't even think of the BeforeUpdate of the form itself

 

[khwaja]

Could someone or ghudson kindly summarise the info with some steps so that we could use the code contributed.