SQL problem, please help?

[Tomino]

Hello,

I am having a problem with SQL. I tried to find a solution but after 4 days, my head is a spinning and I really don't see the solution anymore. Is there somebody here who can help me please?

I try to make an audit trail function (better is to say that I found some code on the net and try to adapt it). The code itselves without my interference works perfect, every time the user changes something in a form, the audit trail writes the old as well the new values to a table.

What I wanted to do is complete the audit trail with a function where the user has to give a reason for every change he/she does. I created an extra field in the tempaudit and audit table named "audReason" and I want to place the reason of change in this field.

There's always some kind of error but the last one I am close (I think)...(Syntax error (missing operator) in query expression is the last error I got from this code)


Thanks a lot for your assistance and patience!

Tom

Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
    lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
 
On Error GoTo Err_AuditEditBegin
 
    Dim db As DAO.Database           ' Current database
    Dim sSQL As String
    'Dim audReason As String
 
    'audReason = "help"
 
    '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, audReason ) " & _
            "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " ' & audReason & '" AS Expr4, " & sTable & ".* " & _
            "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            'Debug.Print sSQL
        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
 

[PianoMan64]

Hello,

I am having a problem with SQL. I tried to find a solution but after 4 days, my head is a spinning and I really don't see the solution anymore. Is there somebody here who can help me please?

I try to make an audit trail function (better is to say that I found some code on the net and try to adapt it). The code itselves without my interference works perfect, every time the user changes something in a form, the audit trail writes the old as well the new values to a table.

What I wanted to do is complete the audit trail with a function where the user has to give a reason for every change he/she does. I created an extra field in the tempaudit and audit table named "audReason" and I want to place the reason of change in this field.

There's always some kind of error but the last one I am close (I think)...(Syntax error (missing operator) in query expression is the last error I got from this code)


Thanks a lot for your assistance and patience!

Tom

Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
    lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
 
On Error GoTo Err_AuditEditBegin
 
    Dim db As DAO.Database           ' Current database
    Dim sSQL As String
    'Dim audReason As String
 
    'audReason = "help"
 
    '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, audReason ) " & _
            "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " ' & audReason & '" AS Expr4, " & sTable & ".* " & _
            "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            'Debug.Print sSQL
        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
 

Here is the code corrected for the syntax error.
Hopefully that fixes any issues you were having.
Without having the table and form that you're working with, kind of hard to test.

Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
    lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
 
On Error GoTo Err_AuditEditBegin
 
    Dim db As DAO.Database           ' Current database
    Dim sSQL As String
    'Dim audReason As String
 
    'audReason = "help"
 
    '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, audReason ) " & _
            "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, '" & audReason & "' AS Expr4, " & sTable & ".* " & _
            "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            Debug.Print sSQL
        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
 

Hopefullt that works,

Joe P.