Access Audit trail

I am trying to audit changes on a database done through a form.  I am finding all sort of examples but this particular form is using a Query to pull data from 2 different tables.  One table has the field to pull the criteria, the other table has the data to be changed.  Is this why I cannot get an updates field to show the changes made?  I have borrowed code that was posted on other entries on EE and Microsoft.  the update is just not working, this latest code that I have tried also has errors and I am very frustrated.  I attached a sample of what I am working with if anyone can help at all.
InvDB.mdb
kgregarAsked:
Who is Participating?
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Well, I'm stumped.  The query/table difference shouldn't matter;  I tested it on my form (which was based directly on a table) with both a SQL statement as the form's record source, and a named query as the source, and the code ran perfectly on both.

Are you calling the function in the code in your field's OnExit event, like this:

Private Sub FieldName_Exit(Cancel As Integer)
    TrackChanges
End Sub

?

Paul
0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
This solution requires a ChangeLogTb (with fields ChangeOnForm, ChangeInField, ChangeRecordKey, and ChangeDtTm).  Create a global variable ("MyKey") as a string, and in the OnCurrent event of the form, populate that variable with whatever values constitute the key for the record(s) you're working on.  Your records will need to have a CreateDt field that is populated when the record is created.  Create the procedure "TrackChanges" using the code below, and call the procedure in the OnExit event of the field(s) whose changes you want to track.

Because I don't care about changes made when the record is created, or corrections to data made in the first 24 hours, I have the code set to track changes more than 1440 minutes after the record is created.  If you want to track changes 10 minutes or an hour or whatever after the record is created, modify the intMinutes variable.

Let me know if you have any problems or questions with this.

Paul

<><><>
Public Function TrackChanges()
    On Error GoTo HandleError
    Dim frm As Form, ctl As Control, intMinutes As Integer, frmName As String, ctlName As String
    Set frm = Screen.ActiveForm
        frmName = Screen.ActiveForm.Name
    Set ctl = Screen.ActiveControl  'identifies the active form and field
        ctlName = ctl.Name
    intMinutes = 1440 '1440 minutes in a 24 hour day
   
If ctl.OldValue <> ctl Then         'compares the old value to the new value, and, if different,
    If DateDiff("n", frm.CreateDt, Date) > intMinutes Then    'and if the record is more than a day old
                                                              'proceeds to record the changed value.
        DoCmd.SetWarnings False
            DoCmd.RunSQL "INSERT INTO ChangeLogTb (ChangeOnForm, ChangeInField, ChangeRecordKey, ChangeDtTm, " & _
                "ChangeBy, ChangeFrom, ChangeTo)" & _
                "SELECT '" & frmName & "', '" & ctlName & "', '" & MyKey & "', #" & Now() & "#, '" & _
                strLoggedOnID & "', '" & ctl.OldValue & "', '" & ctl & "';"
        DoCmd.SetWarnings True
   
    End If
End If

ExitFunction:
     Exit Function
HandleError:
DoCmd.SetWarnings True
MsgBox "ChangeTracking TrackChanges Error " & Err.Number & " (" & Err.Description & ");  Line " & Erl

EndFunction:
    End Function
0
 
kgregarAuthor Commented:
I noticed the requirement for createDt field, The data that is populating the database originally is downloaded from a host provider, imported into the database and then the changes should be tracked at that point.   I am not sure that createDt field is available unless I use the end of month date in that field as well?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kgregarAuthor Commented:
I actually got them to agree to move the data to SQL, from there I am thinking we can use the SQL logs to track changes to the data?  I am still going to use access as the front end forms but just link the tables to access.  that should work?
0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
If you comment out (or just delete) the second If statement ("If DateDiff("n", frm.CreateDt, Date) > intMinutes Then") and it's corresponding "End If", the code should record all changes to data.  I added the age condition so I didn't see entries for changing "farnk" to "frank" to "Frank".  Let me know if you need any help with the modification.

Paul
0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Did the ChangeTracking code work for you?

Paul
0
 
kgregarAuthor Commented:
I am still trying to get it in.  I must be doing something wrong somewhere.  I think it will do what I need if it will just work.
0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Can you tell me what you did?  Are you getting any error messages?

P
0
 
kgregarAuthor Commented:
I set the table up ChangeLogTB, I added a Module called TrackChanges and I have a form called frmTPfdReview (which is the data I want to track).   when I close the form I get the debug message.
Option Compare Database

Public Function TrackChanges()
    On Error GoTo HandleError
    Dim frm As Form, ctl As Control, intMinutes As Integer, frmName As String, ctlName As String
    Set frm = Screen.ActiveForm
        frmName = Screen.ActiveForm.Name
    Set ctl = Screen.ActiveControl  'identifies the active form and field
        ctlName = ctl.Name
    intMinutes = 1440 '1440 minutes in a 24 hour day
   
If ctl.OldValue <> ctl Then         'compares the old value to the new value, and, if different,
    If DateDiff("n", frm.CreateDt, Date) > intMinutes Then    'and if the record is more than a day old
                                                              'proceeds to record the changed value.
        DoCmd.SetWarnings False
            DoCmd.RunSQL "INSERT INTO ChangeLogTb (ChangeOnForm, ChangeInField, ChangeRecordKey, ChangeDtTm, " & _
                "ChangeBy, ChangeFrom, ChangeTo)" & _
                "SELECT '" & frmName & "', '" & ctlName & "', '" & MyKey & "', #" & Now() & "#, '" & _
                strLoggedOnID & "', '" & ctl.OldValue & "', '" & ctl & "';"
        DoCmd.SetWarnings True
   
    End If
End If

ExitFunction:
     Exit Function
HandleError:
DoCmd.SetWarnings True
MsgBox "ChangeTracking TrackChanges Error " & Err.Number & " (" & Err.Description & ");  Line " & Erl

EndFunction:

End Function


It highlights the 5th line down and nothing populates the change table.  Sorry so dense, it may be something easy!

0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Ah-hah!  I think I know what the problem is.  While you're looking at your code in the VBA window, click Tools, References.  A window named "References = YourDatabaseName" will open, and you'll see a list of Reference Libraries.  Select

Microsoft DAO 3.6 Object Library

and click OK.  Go back to your form view, and try running it again.  Let me know!
0
 
kgregarAuthor Commented:
It was checked already.
If I have the form open and change the data in it, it should populate the new table with the changes?
I am still getting the error on the same line.
thanks
0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
What, exactly, does the error message say?  And the debug button is taking you to this line:
frmName = Screen.ActiveForm.Name
Right?

Paul

0
 
kgregarAuthor Commented:
I make a change to one of the fields then close the form and get Run-time error '2474' The expression you entered requires the control to be in the active window.  End  Debug
 the debug button takes me to:      Set ctl = Screen.ActiveControl

thanks again
0
 
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
OK, got it.  The code requires that the form you used to change the data be open while it is running, so it can compare the CurrentValue property to the OldValue property.  Click into another field on the same form (or use the tab key);  that should let the code run.  Let me know if it suceeds!

Paul

0
 
kgregarAuthor Commented:
I made a change and tabbed through the record, even went to a new record and the same error.  
very strange.
0
 
kgregarAuthor Commented:
do I need to have any addtional fields in the records for the changes or all changes just record to the new table?
0
 
kgregarAuthor Commented:
oh and does it matter that the form is working with data pulled from a query?  I bet that is the problem?  the form should just be on the table itself?
0
 
kgregarAuthor Commented:
I appreciate your help with this, I unfortunately was not able to spend the time needed on this and have been pulled to another project in the meantime.  I will get back to the maybe next week but you deserve the points, thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.