Results 1 to 10 of 10
  1. #1
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42

    Question Audit Trail Help

    So I have been using Allen Browne's http://allenbrowne.com/AppAudit.html to try and create an Audit table for track changes to one table. My issue is that my primary key is text, not an auto number. I tried modifying the code so that it changed the call variables to strings but it fails when trying to execute the sSQL segment. It throws this error:



    Run-time error '3075':

    Syntax error (missing operator) in query expression '(Devices.Device ID = FMB620)'.
    Here is the code that I am running, I am hoping that there is just something simple I can change in order to use a string instead of a long variable. Any help is appreciated!

    Code:
    Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
        lngKeyValue As String, bWasNewRecord As Boolean) As Boolean
    'On Error GoTo Err_AuditEditBegin
        'Purpose:    Write a copy of the old values to temp table.
        '            It is then copied to the true audit table in AuditEditEnd.
        'Arugments:  sTable = name of table being audited.
        '            sAudTmpTable = name of the temp audit table.
        '            sKeyField = name of the AutoNumber field.
        '            lngKeyValue = Value of the AutoNumber field.
        '            bWasNewRecord = True if this was a new insert.
        'Return:     True if successful
        'Usage:      Called in form's BeforeUpdate event. Example:
        '                bWasNewRecord = Me.NewRecord
        '                Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
        Dim db As DAO.Database           ' Current database
        Dim sSQL As String
    
    
        'Remove any cancelled update still in the tmp table.
        Set db = DBEngine(0)(0)
        sSQL = "DELETE FROM " & sAudTmpTable & ";"
        db.Execute sSQL
    
    
        ' If this was not a new record, save the old values.
        If Not bWasNewRecord Then
            sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
                "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            db.Execute sSQL, dbFailOnError
        End If
        AuditEditBegin = True
    
    
    Exit_AuditEditBegin:
        Set db = Nothing
        Exit Function
    
    
    'Err_AuditEditBegin:
    '    Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", , False)
    '    Resume Exit_AuditEditBegin
    End Function
    
    
    
    
    Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, _
        sKeyField As String, lngKeyValue As String, bWasNewRecord As Boolean) As Boolean
    'On Error GoTo Err_AuditEditEnd
        'Purpose:    Write the audit trail to the audit table.
        'Arguments:  sTable = name of table being audited.
        '            sAudTmpTable = name of the temp audit table.
        '            sAudTable = name of the audit table.
        '            sKeyField = name of the AutoNumber field.
        '            lngKeyValue = Value of the AutoNumber field.
        '            bWasNewRecord = True if this was a new insert.
        'Return:     True if successful
        'Usage:      Called in form's AfterUpdate event. Example:
        '                Call AuditEditEnd("tblInvoice", "audTmpInvoice", "audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
        Dim db As DAO.Database
        Dim sSQL As String
        Set db = DBEngine(0)(0)
    
    
        If bWasNewRecord Then
            ' Copy the new values as "Insert".
            sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
                "SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            db.Execute sSQL, dbFailOnError
        Else
            ' Copy the latest edit from temp table as "EditFrom".
            sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
                " WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
            db.Execute sSQL
            ' Copy the new values as "EditTo"
            sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
                "SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            db.Execute sSQL
            ' Empty the temp table.
            sSQL = "DELETE FROM " & sAudTmpTable & ";"
            db.Execute sSQL, dbFailOnError
        End If
        AuditEditEnd = True
    
    
    Exit_AuditEditEnd:
        Set db = Nothing
        Exit Function
    
    
    'Err_AuditEditEnd:
    '    Call LogError(Err.Number, Err.Description, conMod & ".AuditEditEnd()", , False)
    '    Resume Exit_AuditEditEnd
    End Function

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you have embedded spaces in your field and/or object names, Access will complain and error.
    You must enclose names with embedded spaces within "square brackets" ([ ] .. eg [My Field Name]).
    So Device Id, should be [Device ID].

    Also, '(Devices.Device ID = FMB620)', is a string/text data type and the value has to be within quotes.

    (Devices.[Device ID] = "FMB620").

    Since you have shown us Allen's function code, and not your calling code, we don't immediately know which variable/line is the issue.

  3. #3
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42
    Quote Originally Posted by orange View Post
    If you have embedded spaces in your field and/or object names, Access will complain and error.
    You must enclose names with embedded spaces within "square brackets" ([ ] .. eg [My Field Name]).
    So Device Id, should be [Device ID].

    Also, '(Devices.Device ID = FMB620)', is a string/text data type and the value has to be within quotes.

    (Devices.[Device ID] = "FMB620").

    Since you have shown us Allen's function code, and not your calling code, we don't immediately know which variable/line is the issue.
    I see, I missed the square brackets in my calling code. Thanks for catching that!

    Running it again however I get this error message instead.

    Run-time error "3061":

    Too few parameters. Expected 1.
    With the debug bringing me back to db.Execute sSQL, dbFailOnError Line.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Add a line

    Debug.Print sSQL before this line
    db.Execute sSQL, dbFailOnError

    and comment the ' db.Execute sSQL, dbFailOnError line for this test by adding the '

    This should show you what Access has understood sSQL to be.

    Error 3061 is often the result of a misspelled name.

  5. #5
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Place 'Option Explicit' on the top of your code under where it normally says 'Option Compare Database' and it will catch spelling error for you. Just make sure you dimension each variable.

  6. #6
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42
    Quote Originally Posted by orange View Post
    Add a line

    Debug.Print sSQL before this line
    db.Execute sSQL, dbFailOnError

    and comment the ' db.Execute sSQL, dbFailOnError line for this test by adding the '

    This should show you what Access has understood sSQL to be.

    Error 3061 is often the result of a misspelled name.
    So I ran the test and it kicked the same error now at this db.Execute sSQL in the second function:

    Code:
    Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, _
        sKeyField As String, lngKeyValue As String, bWasNewRecord As Boolean) As Boolean
    'On Error GoTo Err_AuditEditEnd
        'Purpose:    Write the audit trail to the audit table.
        'Arguments:  sTable = name of table being audited.
        '            sAudTmpTable = name of the temp audit table.
        '            sAudTable = name of the audit table.
        '            sKeyField = name of the AutoNumber field.
        '            lngKeyValue = Value of the AutoNumber field.
        '            bWasNewRecord = True if this was a new insert.
        'Return:     True if successful
        'Usage:      Called in form's AfterUpdate event. Example:
        '                Call AuditEditEnd("tblInvoice", "audTmpInvoice", "audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
        Dim db As DAO.Database
        Dim sSQL As String
        Set db = DBEngine(0)(0)
    
    
        If bWasNewRecord Then
            ' Copy the new values as "Insert".
            sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
                "SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            db.Execute sSQL, dbFailOnError
        Else
            ' Copy the latest edit from temp table as "EditFrom".
            sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
                " WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
            db.Execute sSQL
            ' Copy the new values as "EditTo"
            sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
                "SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
      --------->db.Execute sSQL​ <---------------------------------------------------------------------------------------------------------------------------------------------------------
            ' Empty the temp table.
            sSQL = "DELETE FROM " & sAudTmpTable & ";"
            db.Execute sSQL, dbFailOnError
        End If
        AuditEditEnd = True
    
    Exit_AuditEditEnd:
        Set db = Nothing
        Exit Function
    
    
    'Err_AuditEditEnd:
    '    Call LogError(Err.Number, Err.Description, conMod & ".AuditEditEnd()", , False)
    '    Resume Exit_AuditEditEnd
    End Function
    So I tried putting another Debug.Print sSQL test with this code and it runs without any errors but does not update the audit table as the change occurs. If I try and run the code without the Debug.Print test it gives me the previous 3061 error.

  7. #7
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42
    Quote Originally Posted by nick404 View Post
    Place 'Option Explicit' on the top of your code under where it normally says 'Option Compare Database' and it will catch spelling error for you. Just make sure you dimension each variable.
    Already in the Allen Browne's original code.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Did you get anything printed in the immediate window?
    If not, I suggest you compact and repair, then zip your database, then attach the zip file to a post.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    Three things:

    1) You changed an argument in the function from a Long to a String: lngKeyValue As String (I would have renamed it also to "sKeyValue"... but that is me)
    Code:
    Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, _
        sKeyField As String, lngKeyValue As String, bWasNewRecord As Boolean) As Boolean
    Since "lngKeyValue" is a string, it needs to be delimited with quotes (you didn't):
    Code:
    <snip>
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
    <snip>
    Code:
    <snip>
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = '" & lngKeyValue & "');"
    <snip>



    2) You also have two functions in your SQL that I would concatenate:
    Code:
    <snip>
                "SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
    <snip>
    I would construct the string like:
    Code:
    <snip>
                "SELECT 'Insert' AS Expr1, " &  Now() & " AS Expr2, " &  NetworkUserName() & " AS Expr3, " & sTable & ".* " & _
    <snip>

    My $0.02.......

  10. #10
    HelpDesk is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jun 2015
    Posts
    42
    Quote Originally Posted by ssanfu View Post
    PMFJI,

    Three things:

    1) You changed an argument in the function from a Long to a String: lngKeyValue As String (I would have renamed it also to "sKeyValue"... but that is me)
    Code:
    Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, _
        sKeyField As String, lngKeyValue As String, bWasNewRecord As Boolean) As Boolean
    Since "lngKeyValue" is a string, it needs to be delimited with quotes (you didn't):
    Code:
    <snip>
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
    <snip>
    Code:
    <snip>
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = '" & lngKeyValue & "');"
    <snip>



    2) You also have two functions in your SQL that I would concatenate:
    Code:
    <snip>
                "SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
    <snip>
    I would construct the string like:
    Code:
    <snip>
                "SELECT 'Insert' AS Expr1, " &  Now() & " AS Expr2, " &  NetworkUserName() & " AS Expr3, " & sTable & ".* " & _
    <snip>

    My $0.02.......
    Steve, this was exactly what was going on and what I couldn't figure out how to change. Added the quotes where necessary and now it works like a charm! Thanks!

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

Similar Threads

  1. Audit Trail
    By zburns in forum Access
    Replies: 4
    Last Post: 07-27-2015, 08:49 AM
  2. Having an audit trail
    By ryanmce92 in forum Modules
    Replies: 5
    Last Post: 06-03-2015, 02:29 PM
  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
  •  
Other Forums: Microsoft Office Forums