Results 1 to 10 of 10

trying to create an audit trail system with a HOWTO

  1. #1
    databasewolfy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    10

    trying to create an audit trail system with a HOWTO

    I am currently following this guide on how to create an audit trail system for my database.

    http://www.fontstuff.com/access/acctut21.htm

    I am not too knowledgable with VBA and macros. My current hangup is the attached dialog box.

    Now, the guide tells me this:

    IMPORTANT: The example shows "EmployeeID" as the parameter value. Change this to the name of the field that identifies the current record, usually the Primary Key field although you can use any field that uniquely identifies the record.

    I have Call AuditChanges("ID") set to the form's BeforeUpdate parameter.



    All of my tables have ID as the primary key set to autonumber. I am under the assumption this should monitor all of the fields named ID in these tables. So I rename EmployeeID to ID, but get the same error with the updated table name.

    The guide also says to compile the following to check for errors.

    Code:
    Sub AuditChanges(IDField As String)
        On Error GoTo AuditChanges_Err
        Dim cnn As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim ctl As Control
        Dim datTimeCheck As Date
        Dim strUserID As String
        Set cnn = CurrentProject.Connection
        Set rst = New ADODB.Recordset
        rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
        datTimeCheck = Now()
        strUserID = Environ("USERNAME")
        For Each ctl In Screen.ActiveForm.Controls
            If ctl.Tag = "Audit" Then
                If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                    With rst
                        .AddNew
                        ![DateTime] = datTimeCheck
                        ![UserName] = strUserID
                        ![FormName] = Screen.ActiveForm.Name
                        ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                        ![FieldName] = ctl.ControlSource
                        ![OldValue] = ctl.OldValue
                        ![NewValue] = ctl.Value
                        .Update
                    End With
                End If
            End If
        Next ctl
    AuditChanges_Exit:
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    AuditChanges_Err:
        MsgBox Err.Description, vbCritical, "ERROR!"
        Resume AuditChanges_Exit
    End Sub
    I did so, nothing happened. Now the option is greyed out.

    Any help would be much appreciated.
    Attached Thumbnails Attached Thumbnails callauditchanges_employeeID.PNG  

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    Quote Originally Posted by databasewolfy View Post
    I have Call AuditChanges("ID") set to the form's BeforeUpdate parameter.
    It sounds like you have that directly in the properties window; it needs to be in VBA code:

    http://www.baldyweb.com/FirstVBA.htm

    Also make sure you don't have it somewhere else with the old field name.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    don't put quotes around your "ID", that's a number field per your description

    call auditchanges(ID)

    further your function should be


    sub AuditChanges (IDfield as long)

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    Quote Originally Posted by rpeare View Post
    don't put quotes around your "ID", that's a number field per your description

    call auditchanges(ID)

    further your function should be


    sub AuditChanges (IDfield as long)
    No, you're misunderstanding how the value is used. It's the name of the field, not the value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    databasewolfy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    10
    Quote Originally Posted by pbaldy View Post
    It sounds like you have that directly in the properties window; it needs to be in VBA code:

    http://www.baldyweb.com/FirstVBA.htm

    Also make sure you don't have it somewhere else with the old field name.
    That link is very helpful. Thanks. I think I may have pasted the code into the wrong type of object? Unsure why this comes up. Also, I scrolled up. I restored the "s around the ID, but still get the same compile error.


    Click image for larger version. 

Name:	callauditchange2.PNG 
Views:	16 
Size:	15.6 KB 
ID:	39476

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    It needs the quotes. The sub needs to be in a standard module, and the module can't have the same name as the sub.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    databasewolfy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    10
    Quote Originally Posted by pbaldy View Post
    It needs the quotes. The sub needs to be in a standard module, and the module can't have the same name as the sub.
    My dumb ass had the project named as AuditChanges. Woops. Thanks! Works now.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    It's a common mistake when you first start using public subs/functions; we've all done it. Glad you got it sorted. Are you using the version at the bottom to record deletions? If so, there's a flaw in it and it records the wrong ID.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    databasewolfy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    10
    I am attempting the bottom portion of that howto now.

    "Microsoft Access can't find the field "ID" referred to in your expression."

    What field should this be referring to?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,719
    As far as I know, the primary key field. The way he uses it, I think the control has to have the same name.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Audit Trail Help
    By HelpDesk in forum Access
    Replies: 9
    Last Post: 08-07-2015, 06:13 AM
  2. Create Audit trail of sorts
    By craig1988 in forum Programming
    Replies: 2
    Last Post: 03-30-2015, 02:00 AM
  3. Replies: 2
    Last Post: 02-09-2013, 12:39 PM
  4. Audit Trail
    By bigroo in forum Programming
    Replies: 4
    Last Post: 02-22-2012, 07:55 PM
  5. audit trail
    By slimjen in forum Forms
    Replies: 1
    Last Post: 10-13-2011, 01:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums