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

Can someone help me with changing this code to update for a List Box? I've tried changing "acTextBox" to "acListBox", but this doesn't work. Thanks!

how can we make it track new records too ?

Ive been trying to use this for the last week, whenever I use the code for Text OR Combo Boxes it works like a charm! But if I tell it to track both of them, Text AND Combo. No matter the order, if its Case or a Listing I get a "Runtime Error 3251 Operation Not Supported" and it will still record the text, never the combobox. (Access 2000)

I get a syntax error (missing operator) in query expression.

This seems to happen in the fields with more text than the others.

Anyone have any ideas how to fix this? Why it is happening? thanks!

would this work for MS SQL? If not any suggestions?

I've managed to get this code running on a fairly basic database.

What's annoying me is that it falls over so easily. I share Irprogram's problem of not being able to get this to work with text boxes and combo boxes simultaneously, I cannot get the code for this functionality (supplied in the article) to run. The other issue I'm having which seems a bit odd is that it throws up an error message when a date field is changed (Error 3251 again). The date field on the form is just a text box, so why doesn't the formula just treat it as text?

This works fine for me on any kind of control. However, I have the problem with changing something to a null value, and I can't figure out from what's been posted how to deal with this. Any further clarification would be greatly appreciated!

Change your if statement from: If .Value .OldValue then

To: If (.Value .OldValue) Or (Not IsNull(.OldValue) And IsNull(.Value)) Then

1000000000 Thanks you are the man

I have implemented the above tracking and it works great. I have it call from "before update" on the form. However, it does not track changes to the subform. When I try to call the module from the subform I get an error "type mismatched". how do I tract the subform changes?

if you replace
If .Value .OldValue Then

with the following code:

If (.Value .OldValue) Or ((Not IsNull(.OldValue) And IsNull(.Value))) Or ((IsNull(.OldValue) And Not IsNull(.Value))) Then

it will now track changes in values, changes where a null value is now replaced with a value or if a field's value is now replaced with a null

Hope this helps.

Is it possible to only ask for a reason, when the record in question, has no value..

Because it makes no sense to ask people for a reason, when they just add data..

Cheers!

Hello, I used this successfully on forms where the data is only based on a table. However when the data comes from a query when the code evaluates a field that comes from a related table you get error 3251.

Check out http://kbalertz.com/207836/Error-OldValue-Property.aspx for detail on this issue

The solution to this is that you must setup a clone record set in the OnCurrent operation of the form and then draw the old values from this record set.

That works when you type in the field actual name such as rs![OrderName] but the problem is that this code is made so you don't have to type in each control on each form.

Therefore I want to dynamically set the field name in the rs!FIELDNAME. How is this done I have tried rs!(ctl), I have tried concantenating a string and using that variable FIELDNAME="["&ctl&"]" I have even tried FIELDNAME="rs!["&ctl&"]" nothing seems to work. It must be possible.

here is the current code I have

This is in the AuditTrail code of the form itself:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
For Each ctl In frm.Controls


With ctl
'Avoid labels and other controls with Value property.
ControlName = ctl.Name

If (.ControlType = acTextBox) Or (.ControlType = acComboBox) Or (.ControlType = acOptionGroup) Or (.ControlType = acCheckBox) Or (.ControlType = acMemo) Then 'Or acListBox Then
If .Enabled = True Then
varBefore = rs!(ControlName) ' rs![InstallationPosition] works so the record set seems OK
If .Value

Hi,
Could you help me write a simple event code to track the date changes made to a record in Date_Attend field to a table called StudentDataChange. The fields contained can only be Stud_ID, Date_Attend and new field called DateOfChange? Thanks.

Auto# Stud_ID Stud_Name Date_Attend
1 1234 Robert Smith 12/1/2010
2 2345 Jane Doe 11/30/2010
3 3456 Liz Allen 11/15/2010
4 4567 Doreen Jackson 11/1/2010

I have been using this solution for almost a year and it has worked nicely. I recently implemented this code in a new database. On my form (based on one query) I have about 42 controls. I have 13 tabs on this form. My problem is that the SQL insert statement only gets generated for 30 of the controls. 12 are ignored. The 12 that are ignored reside on 6 different tabs. I ran the documenter for this form so I could check the properties of the fields that work and the fields that do not work. I also compared the properties of the tabs that work and the ones that do not. I can't see any difference in the properties. I tried generating a new justified data entry form with all fields from the query and called the audittrail function from the before update event for the form. I still had a problem with these fields. Then I generated a new data entry form with only 2 fields. I placed the primary key and a new date field, "PAPRTrainingCompleted". PAPR was being overlooked on my production form. When I triggered the before update event to call the audittrail for this form, the SQL statement was generated successfully. Is my problem too many controls on one form?

The code was exiting the subroutine due to a control which triggered the "operation is not supported for this type of object". I had to add a line of code to check for visible, enabled and locked fields.
Here is the line of code..
If ctl.Visible And ctl.Enabled And Not ctl.Locked then

Here is the line of code in context.
For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.

If .ControlType = acTextBox Or .ControlType = acComboBox Or .ControlType = acListBox Then
If ctl.Visible And ctl.Enabled And Not ctl.Locked Then
If .Value .OldValue Or (IsNull(.OldValue) And Not IsNull(.Value)) Or (IsNull(.Value) And Not IsNull(.OldValue)) Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "tblAudit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue, ComputerName, UserDomain, UserProfile) " _
& "VALUES (Now()," _
& cDQ & CurrentUser() & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ", " _
& cDQ & Environ("ComputerName") & cDQ & ", " _
& cDQ & Environ("UserDomain") & cDQ & ", " _
& cDQ & Environ("UserProfile") & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End If
End If
End With
Next

Set ctl = Nothing
Exit Sub

Hi,

I have managed to get all the objects I require added to the audit table except those belonging to a subform which adds a link to an attachment for the desired record in the database.

The form has a text box with the hyperlink in and three buttons: add, open and delete, for each attachment.

I wish to track which attachments are amended or deleted from the record.

Any ideas?

Thanks

Hi, im new to MS access Database, but i want this feature added to my database, is it posible to run on MS Access 2007? and is there someone here who can demo this using MS Access 2007, that would be much appreaciated.

This code is fine but do not work where changes are made in option group controls and in list/combo with multiple value choice. How to improve the code to do it ?
Thanks for your replies.