Track Changes In Your Database

Rupert Bennett

Active Member
Joined
Nov 20, 2002
Messages
276
I don't remember where I picked up this function to track changes, but I like it because it tracks changes in every form to every data entry control and keeps the changes in one centralized table.
The function works well for me, but only in cases where I am adding a new record. If i make changes to an existing record, i get an error message that says:

2424 The expression you entered has a field, control, or property name that Employee Attendance can't find.
Employee Attendance is the name of my Database.

The function is called from each form as follows:
Call Audit_Trail(Me, "EmployeeID", EmployeeID.Value). Of course the field name would change depending on which form you are auditing.

Here is the code:

Code:
Public Function Audit_Trail(MyForm As Form, UniqID_Field As String, UniqID As String)
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 ccnt As Control
Dim sUser As String

Dim strSQL As String
Const cQUOTE = """" 'Thats 2 quotes in sequence

Dim action, nullval As String
nullval = "Null"

sUser = Environ("UserName") 'get the users login name

'If new record, record it in audit trail and exit function.
If MyForm.NewRecord = True Then
action = "*** New Record ***"
'Broken down into 4 separate variables for ease of view and troubleshooting
strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, [Action])"
strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & action & cQUOTE & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Exit Function
End If

Dim changecnt As Integer
changecnt = 0

'Check each data entry control for change and record old value of the control.
For Each ccnt In MyForm.Controls

Select Case ccnt.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If ccnt.Name Like "*" & "txt" & "*" Then GoTo TryNextCCNT 'Skip AuditTrail field.
If (ccnt.Value <> ccnt.OldValue) Or _
(IsNull(ccnt.Value) And Len(ccnt.OldValue) > 0 Or ccnt.Value = "" And Len(ccnt.OldValue) > 0) Then
changecnt = changecnt + 1
End If
End Select

TryNextCCNT:
Next ccnt

If changecnt > 0 Then
gstrReason = InputBox("Reason for change(s)?", "Reason for change(s)?")
End If

'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 Like "*" & "txt" & "*" Then GoTo TryNextControl 'Skip AuditTrail field.
If ctl.Value <> ctl.OldValue Then
action = "*** Updated Record ***"

'Broken down into 4 separate variables for ease of view and troubleshooting
strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action], Reason)"
strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", " & cQUOTE & ctl.OldValue & cQUOTE
strSQL = strSQL & ", " & cQUOTE & ctl.Value & cQUOTE & ", " & cQUOTE & action & cQUOTE & ", " & cQUOTE & gstrReason & cQUOTE & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'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
action = "*** Added Info to Record ***"

'Broken down into 4 separate variables for ease of view and troubleshooting
strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action])"
strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", " & cQUOTE & nullval & cQUOTE
strSQL = strSQL & ", " & cQUOTE & ctl.Value & cQUOTE & ", " & cQUOTE & action & cQUOTE & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'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
action = "*** Removed Info to Record ***"

'Broken down into 4 separate variables for ease of view and troubleshooting
strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action], Reason)"
strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", " & cQUOTE & ctl.OldValue & cQUOTE
strSQL = strSQL & ", " & cQUOTE & nullval & cQUOTE & ", " & cQUOTE & action & cQUOTE & ", " & cQUOTE & gstrReason & cQUOTE & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If

End Select

TryNextControl:
Next ctl

Exit_Audit_Trail:
Exit Function

Err_Audit_Trail:
If Err.Number = 2001 Then 'You canceled the previous operation.
'do nothing
Else
Beep
MsgBox Err.Number & " - " & Err.Description
End If
Resume Exit_Audit_Trail

End Function

Can anyone see what I am doing wrong and help me modify the code to work with my forms? the code works perfectly with the example that came with the code , even when new fields are added to the tables and forms.
I am using Access 2003.
Thanks for your help.
Rupert
 
Hello Denis,

I called the function Call Loghanges(Patient_Hospital_ID) where LogChanges is the name of my function.

Thanks again Denis!

Jake
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi Jake, that appears to be OK but the code would be in a module. what did you name the code module?
The function code does not belong in a form (you will restrict it to only working in that form and nowhere else), and the module cannot have the same name as the function of you will get a conflict.

Denis
 
Upvote 0
Hello Denis,

How was your weekend? Again thank you so much for taking the time replying to my query it is very much appreciated. And my apology for my ignorance.

OK I renamed my code module to LogChangesBas and my function remains the same.

What do you mean by this The function code does not belong in a form (you will restrict it to only working in that form and nowhere else) what I did since I have a form with tabs I called my function in each tab on Before Update event.
 
Upvote 0
Hi Jake, just clarifying...

1. Code can live in 3 main areas in an Access database.
Modules are for code that you want to use anywhere in the database, so general functions like the one I provided belong in these modules.
Class modules are for objects (or classes) that you create. They are not for general code, but the functions they contain can be used throughout the database too.
Forms and Reports have their own modules too. They are a special kind of class module (class modules have a lot of event-based code) but Sub and Function routines in forms are only available when (1) you remove the Private keyword and (2) the form or report is open.

In your case the function can easily be used by the form, if the function lives in a standard module. Just don't use the same name for a routine and its parent module.

Denis
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top