Audit Trail

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!
 
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
 
Hi, Were you able to figure out the error?
Gabriello
 
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


I understand there are several different files you could have downloaded, please could you provide a link to the file you actually downloaded.
 
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!
 
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
 
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.
 
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.
 
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.
 
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 ^^
 
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

Code:
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

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

here is the summary of it:

1st: use the following code to create a new module, call it (dAuditTrail):

Code:
Option Compare Database
Option Explicit

Public Function Audit_Trail()
On Error GoTo Err_Audit_Trail
    
'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
    
    Dim MyForm As Form
    Dim ctl As Control
    Dim sUser As String
    Set MyForm = Screen.ActiveForm
'    sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
'    sUser = Environ("UserName") 'get the users login name
    sUser = CurrentUser '=Admin if you are not using Access security with user workgroups and permissions
    
    'If new record, record it in audit trail and exit function.
    If MyForm.NewRecord = True Then
        MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"
        Exit Function
    End If
    
    'Set date and current user if the form (current record) has been modified.
    MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"
    
    'Check each data entry control for change and record old value of the control.
    For Each ctl In MyForm.Controls
    
    'Only check data entry type controls.
    Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ctl.Name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
            'If new and old value do not equal
            If ctl.Value <> ctl.OldValue Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value
            'If old value is Null and new value is not Null
            ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & ctl.Value
            'If new value is Null and old value is not Null
            ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null"
            End If
    End Select
    
TryNextControl:
    Next ctl
    
Exit_Audit_Trail:
    Exit Function
    
Err_Audit_Trail:
    If Err.Number = 64535 Then 'Operation is not supported for this type of object.
        Exit Function
    ElseIf Err.Number = 2475 Then 'You entered an expression that requires a form to be the active window
        Beep
        MsgBox "A form is required to be the active window!", vbCritical, "Invalid Active Window"
    Else
        Beep
        MsgBox Err.Number & " - " & Err.Description
    End If
    Resume Exit_Audit_Trail
    
End Function
2nd: in your table, create a new memo field, call it (AuditTrail).

3rd: in your form add the new memo field, call it (tbAuditTrail) and make it 'Locked'.

4th: in the BeforeUpdate event of the form, write the following code:

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err
    
    Call Audit_Trail
    
Form_BeforeUpdate_Exit:
    Exit Sub
    
Form_BeforeUpdate_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume Form_BeforeUpdate_Exit
    
End Sub
i hope i didn't miss any part ^^"..

BUT, if you have a form with a subForm, do all the above and the following changes:
http://www.access-programmers.co.uk/forums/showpost.php?p=173439&postcount=15
but instead of 'frm', write 'MyForm' and it should work.

and if the parent form is based on more than one table, do the following changes:
http://www.access-programmers.co.uk/forums/showpost.php?p=713245&postcount=171

I attached the sample provided by ghudson with the required changes in the module, and added a form with subForm with the Audit Trail, I hope it helps ^^..

and yes, THANKS everyone for this nice discussion and the ones who helped for your help.
 

Attachments

Last edited:
Many thanks for your detailed reply. I will go through this and see how it works. Will get back to you, if there are any issues.
 
How do you implement this?

paste that code (the whole thing) into a new standard module, name the module something like modUsers and then whenever you need it you call it like:

Code:
If fOSUserName = "Whatever" Then ...


or

Code:
Me.YourTextBox = fOSUserName
 
I'm still not sure how you could integrate this into the audit trail for the auditor's name.

Sorry, I just got massively busy and I don't have time to dig into it myself. I'm hoping you might have just a quick explanation!! :(
 
Hello im using the Audit trail and is working great although as i am using a drop down combo box for employees I want to show the names in the audit trail and not the employee ID's as shown below

New Record added on 18/07/2008 12:15:10 by Admin;
Changes made on 18/07/2008 12:16:18 by Admin;
EmployeeID: Changed From: 1, To: 2

1 = Dave
2 = Nick

I want to show it as:

New Record added on 18/07/2008 12:15:10 by Admin;
Changes made on 18/07/2008 12:16:18 by Admin;
EmployeeID: Changed From: Dave, To: Nick
 
Hi All

Just wanted to post this ref. audit trails. I have been banging my head on this one, though as always the simplest solution seems to prevail!!

I kept getting the runtime error 3251, after much deliberation I have found the reason, two of my forms are built on tables and one on a query. Yes, you guessed it the table forms run the audit trail fine and the query one gave me the error. I don't understand the logic (should be able to write to the query!) though at least I now know what the Access problem is.

Just though it may help anyone in the future.
 

Users who are viewing this thread

Back
Top Bottom