Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access Audit trail

Posted on 2009-02-18
18
Medium Priority
?
446 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:kgregar
  • 10
  • 8
18 Comments
 
LVL 6

Expert Comment

by:Paul Cook-Giles
ID: 23673801
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
 

Author Comment

by:kgregar
ID: 23674549
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
 

Author Comment

by:kgregar
ID: 23675651
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:Paul Cook-Giles
ID: 23676071
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
 
LVL 6

Expert Comment

by:Paul Cook-Giles
ID: 23687493
Did the ChangeTracking code work for you?

Paul
0
 

Author Comment

by:kgregar
ID: 23696027
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
 
LVL 6

Expert Comment

by:Paul Cook-Giles
ID: 23696049
Can you tell me what you did?  Are you getting any error messages?

P
0
 

Author Comment

by:kgregar
ID: 23696097
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
 
LVL 6

Expert Comment

by:Paul Cook-Giles
ID: 23696661
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
 

Author Comment

by:kgregar
ID: 23714928
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
 
LVL 6

Expert Comment

by:Paul Cook-Giles
ID: 23715939
What, exactly, does the error message say?  And the debug button is taking you to this line:
frmName = Screen.ActiveForm.Name
Right?

Paul

0
 

Author Comment

by:kgregar
ID: 23715978
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
 
LVL 6

Expert Comment

by:Paul Cook-Giles
ID: 23716027
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
 

Author Comment

by:kgregar
ID: 23716058
I made a change and tabbed through the record, even went to a new record and the same error.  
very strange.
0
 

Author Comment

by:kgregar
ID: 23716062
do I need to have any addtional fields in the records for the changes or all changes just record to the new table?
0
 

Author Comment

by:kgregar
ID: 23716071
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
 
LVL 6

Accepted Solution

by:
Paul Cook-Giles earned 1000 total points
ID: 23716299
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
 

Author Closing Comment

by:kgregar
ID: 31548367
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question