Discussion on:

A simple solution for tracking changes to Access data

Have you had problems with users entering or changing data and introducing mistakes? Will this means of monitoring data changes be helpful in curbing some of the problems?

You are not kidding.

A need for functionality like this is a big hint to use a fully fledged DBMS.
I'd advise taking the hint.

which means that you'd have to build in a separate archival/backup system for it so that you can store the data elsewhere and clear the table periodically.

Heaven forbid that anyone should propose a clever and workable solution to getting some "real RDBMS" functionality out of, , dare I say it?... Microsoft Access. Of course, the table will grow -- c'mon, pal, gimme a break.

So my database has a main data entry form that contains several subforms. I don't understand it, but the code works in some of hte subforms and not others. The only real difference is that the id (Primary key) is called something different - assume this is the problem, but doesn't make sense. I can't change the field name since this data is exported to others that need that fieldname for data analysis. Anyone have this similar problems?? Any ideas on how to fix this? Thanks!

I pasted this code into my database and got a
ByRef Argument Type Mismatch Error. The Help screen seemed to suggest that type was important but I do not know where the problem is. As you can probably guess, I am new to VBA.

User Error - that is all it was. The ID number needed to have exactly what was in the properties box for that ID. In this case tablename_ID. Also I discovered that I needed to add the code to call the subprocedures to my subforms as well. Thanks for the code. It is more efficient IMHO than adding code to each control.

I'm getting an error 'You entered an expression that has no value. 2427'. Any idea what that means?

Please add below line code after acOptionGroup, acComboBox to fix this error:

Select Case ctl.ControlType
Case acTextBox, acCheckBox, acOptionGroup, acComboBox
If .ControlType = acTextBox Then 'THIS IS THE CODE

I'm having a problem with it... Added it to a DB I have seemed to be working fine... BUT.... Was causing major headaches when I went in to add and edit this DB.. Then after I had finished.. I keep getting error message...."error "Operation is not supported by this type of object. 3251.........." This error stops after removing the code for the audit trail... Anyone have any ideas why????

Sorry folks, I am new to Access Database and I am very interested with the audit database. I try to follow the step here but it's not working. Please help. Some question as below:

1. The Audit Table should be created as the new MDB or it's had to be in the same database?
2. How can I check the "Me identifier"?

Hi Susan Harkins,
The article has really helped me a lot. Thanks for the same.
The solution you mentioned tracks the RecordID(Primary Key). The code works fine with table having single Primary Key.
Could you please let me know what would be the changes in the code if the table is having two or more Primary Key.
Thanks in advance

Regards

Pranav C Lunavat

Can anyone tell me if this code works when a value has been removed and the field is blank?

I need to track CheckBoxes on a form where the values have changed from being check or unchecked. This code doesn't seem to allow for that? I tried changing the "If .ControlType = acTextBox Then" to "If .ControlType = acCheckBox Then" but it gives me the following error: Object doesn't support this property or method (Error 438) Anyone have any ideas as to how to track value changes for checkboxes on a form as well as text boxes, comboboxes, etc..? Any help would be greatly appreciated.

bbuchert,

It seems that you may be looking at the wrong thing. Here is a little sample that shows a textbox on a form to display the value displayed by the checkbox:

Private Sub Form_Current()

If Me.chk1.Value = 0 Then
Me!txtCheckBoxStatus = "The value displayed by the checkbox is 'False'."
ElseIf Me.chk1.Value = -1 Then
Me!txtCheckBoxStatus = "The value displayed by the checkbox is 'True'."
End If

End Sub

Of course, the checkbox is bound to a Yes/No data type field in the forms's record source
If you test for the value of the checkbox then you should be OK.

I hope this helps --

The original code from the article is looking only at textboxes -- you will need either adapt this code to work with checkboxes or write a second procedure to handle checkboxes separately ( I would probably just add some code to handle the checkboxes) I hope this helps --

I am trying to use the Select Case option that was described below the Main code posted in the "A simple solution for tracking changes to Access data" artical. The strange thing here is the .ControlType value is returning a value of "100" instead of "109" for a textbox or "106" for a checkbox? After doing some research I found out that the acLabel constant is "100". I'm not making any changes to a label on my form? Does this make any sense?

I modified the original article's code as an example -- this is probably not the way I would do it in "real life", but it might help to make things a little clearer:

Sub AuditTrail(frm As Form, recordid As Control)
'Track changes to data.
'recordid identifies the pk field's corresponding
'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler
'Get changed values.
For Each ctl In frm.Controls

With ctl

Select Case .ControlType

Case acTextBox

If .Value .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If

Case acCheckBox

If .Value .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If

End Select

End With

Next

Set ctl = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub


I hope this helps...

Hey.
I would like my audittrail to track the access user name logged in, rather than the user logged into the pc.
Is anyone able to help me please?
Thanks heaps.
Phil.

I love this code and am very thankful to whomever posted it.

There is one thing I did notice about it though that I wish someone would fix.

When a user makes "any" changes to existing data they are prompted to save changes and if they choose "no" it posts whatever changes that took place in the field to the Audit table...

As if the changes really did take place!

This is not really necesarry seeing that no changes really were saved to the database!

If anyone has a fix please email me:

pcgotoguy at gmail.com