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
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?
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?
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.
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.
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
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 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.
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
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.
