locked
Create a Audit Log in Access 2010 RRS feed

  • Question

  • I need to create a monthly audit log of all changes to our multi-user Access 2010 database. Recording who made the changes would also be helpful.  I read the article with the same title as above as an appropriate solution. Unfortunately, I could not download the proposed solution. The author of the answer was Lawrenece Ellefson and the file he proposed was located at:

    https://app.box.com/s/rzmylpp0raggin9eykxg

    When I try to download this file, it says that it doesn't exist. Is there another place to download this file or does someone have a solution to creating a monthly log of changes to the fields in an Access 2010 database.


    Dean J. Waring

    Monday, October 3, 2016 3:30 PM

Answers

  • Hi Dean,

    I think the demos mainly provide you with an approach to implement. You should then be able to apply the methods presented to any fields you want audited. I am not sure you will find an example that fits exactly what you're looking for. You may have to make some adjustments.

    But to answer your question, I don't see why you can't use the same methods to audit a memo field.

    Just my 2 cents...

    • Marked as answer by DeanJW2006 Monday, October 3, 2016 5:46 PM
    Monday, October 3, 2016 5:08 PM

All replies

  • Hi Dean,

    Maybe you can take a look at this blog or download the demos from UtterAccess' Code Archive.

    Hope it helps...

    Monday, October 3, 2016 3:41 PM
  • Do you know if those examples allow memo field changes to be recorded in the log? It seems that the controls are restricted to non-memo fields.  I have several memo fields that are updated monthly and would need to be able to know what changed.  I've done it with queries in the past by comparing the current monthly entries to an archived month. However, I was interested in a better solution using the change or before update events.

    Dean J. Waring

    Monday, October 3, 2016 4:52 PM
  • Hi Dean,

    I think the demos mainly provide you with an approach to implement. You should then be able to apply the methods presented to any fields you want audited. I am not sure you will find an example that fits exactly what you're looking for. You may have to make some adjustments.

    But to answer your question, I don't see why you can't use the same methods to audit a memo field.

    Just my 2 cents...

    • Marked as answer by DeanJW2006 Monday, October 3, 2016 5:46 PM
    Monday, October 3, 2016 5:08 PM
  • Thank you....I just wasn't sure if the same methods would apply to memo fields. I'll work with those examples, thank you for your time.

    Dean J. Waring

    Monday, October 3, 2016 5:46 PM
  • Hi Dean,

    You're welcome. If you do run into any issues, don't hesitate to let us know, so we can guide you further. Good luck with your project.

    Monday, October 3, 2016 5:51 PM
  • The code samples reference Myform!Updates with Myform Dimmed as Form.  Myform is set to Screen.ActiveForm.  When I insert this into my code, I get a form not recognized.  I suspect that it is because Myform references the main form in the samples while my application has the controls being monitored for changes in a subform.  Not sure how I would adapt this to reference all of the controls on the subform instead. 

    Dean J. Waring

    Tuesday, October 4, 2016 1:21 PM
  • Hi Dean,

    Not sure which demo you decided to try. If you're trying to audit a subform, then you could try modifying the variable declaration of Myform to something like:

    Set Myform = Screen.ActiveForm.Controls(SubformControlName).Form

    Hope it helps...

    Tuesday, October 4, 2016 2:49 PM
  • MyForm probably needs to = Screen.ActiveForm.Name
    Tuesday, October 4, 2016 3:57 PM
  • I tried this:

    On Err GoTo TryNextC
        Dim MyForm As Form
        Dim ctl As Control
        Dim strUser As String
        'Set MyForm = "frmfindingsub"
        Set MyForm = Screen.ActiveForm.Controls("frmfindingsub").Form
        strUser = fOSUserName


    ' Set date and current user if form has been updated.
        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "Changes made on " & Now & " by " & strUser & ";"

    ' If new record, record it in audit trail and exit sub.
        If MyForm.NewRecord = True Then
           MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "New Record """
           Exit Sub
        End If

    ' Check each data entry control for change and record old value of 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 = "Updates" Then GoTo TryNextC ' Skip Updates field.
                
                 If ctl.Value <> ctl.OldValue Then
                    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "     " & ctl.Name & ": Changed from: " & ctl.OldValue & " to: " & ctl.Value
                 End If
           End Select
    TryNextC:
             Next ctl
    End Sub

    Main form=frmFindingsConcerns

    Subform =frmfindingssub

    When I run it, it says it doesn't recognize the subform.


    Dean J. Waring

    Tuesday, October 4, 2016 4:05 PM
  • Hi Dean,

    Is this in a Standard Module? You might try testing one specific form first by hard coding the actual names of the form and subform to make sure the code work and then go back to Screen.ActiveForm when it does.

    Also, make sure you are using the name of the control rather than the form inside it. For example:

    Instead of:

    ' Set date and current user if form has been updated.
         MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "Changes made on " & Now & " by " & strUser & ";"

    Try:

    ' Set date and current user if form has been updated.
         Forms!MainFormName.SubformControlName.Form!Updates = MyForm!Updates & Chr(13) & Chr(10) & "Changes made on " & Now & " by " & strUser & ";"

    Hope it helps...

    Tuesday, October 4, 2016 4:12 PM
  • I can't seem to get it to recognize the controls on the subform even with the actual names hard coded.

    Dean J. Waring

    Tuesday, October 4, 2016 6:41 PM
  • Hi Dean,

    Do you know if your subform control's name is the same or different than the name of the form in its Source Object property? In case they're different, make sure you're using the one shown in the Name property.

    Otherwise, you might consider sharing a small copy of your db with test data, so we can take a look.

    Just my 2 cents...

    Tuesday, October 4, 2016 6:50 PM