By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,022 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

SQL problem, please help?

P: 9
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


Expand|Select|Wrap|Line Numbers
  1. Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
  2.     lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
  3.  
  4. On Error GoTo Err_AuditEditBegin
  5.  
  6.     Dim db As DAO.Database           ' Current database
  7.     Dim sSQL As String
  8.     'Dim audReason As String
  9.  
  10.     'audReason = "help"
  11.  
  12.     'Remove any cancelled update still in the tmp table.
  13.     Set db = DBEngine(0)(0)
  14.     sSQL = "DELETE FROM " & sAudTmpTable & ";"
  15.     db.Execute sSQL
  16.  
  17.     ' If this was not a new record, save the old values.
  18.     If Not bWasNewRecord Then
  19.         sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser, audReason ) " & _
  20.             "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " ' & audReason & '" AS Expr4, " & sTable & ".* " & _
  21.             "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  22.             'Debug.Print sSQL
  23.         db.Execute sSQL, dbFailOnError
  24.  
  25.     End If
  26.     AuditEditBegin = True
  27.  
  28. Exit_AuditEditBegin:
  29.     Set db = Nothing
  30.     Exit Function
  31.  
  32. Err_AuditEditBegin:
  33.     Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", , False)
  34.     Resume Exit_AuditEditBegin
  35. End Function
  36.  
Mar 27 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 374
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


Expand|Select|Wrap|Line Numbers
  1. Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
  2.     lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
  3.  
  4. On Error GoTo Err_AuditEditBegin
  5.  
  6.     Dim db As DAO.Database           ' Current database
  7.     Dim sSQL As String
  8.     'Dim audReason As String
  9.  
  10.     'audReason = "help"
  11.  
  12.     'Remove any cancelled update still in the tmp table.
  13.     Set db = DBEngine(0)(0)
  14.     sSQL = "DELETE FROM " & sAudTmpTable & ";"
  15.     db.Execute sSQL
  16.  
  17.     ' If this was not a new record, save the old values.
  18.     If Not bWasNewRecord Then
  19.         sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser, audReason ) " & _
  20.             "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " ' & audReason & '" AS Expr4, " & sTable & ".* " & _
  21.             "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  22.             'Debug.Print sSQL
  23.         db.Execute sSQL, dbFailOnError
  24.  
  25.     End If
  26.     AuditEditBegin = True
  27.  
  28. Exit_AuditEditBegin:
  29.     Set db = Nothing
  30.     Exit Function
  31.  
  32. Err_AuditEditBegin:
  33.     Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", , False)
  34.     Resume Exit_AuditEditBegin
  35. End Function
  36.  
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.

Expand|Select|Wrap|Line Numbers
  1. Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
  2.     lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
  3.  
  4. On Error GoTo Err_AuditEditBegin
  5.  
  6.     Dim db As DAO.Database           ' Current database
  7.     Dim sSQL As String
  8.     'Dim audReason As String
  9.  
  10.     'audReason = "help"
  11.  
  12.     'Remove any cancelled update still in the tmp table.
  13.     Set db = DBEngine(0)(0)
  14.     sSQL = "DELETE FROM " & sAudTmpTable & ";"
  15.     db.Execute sSQL
  16.  
  17.     ' If this was not a new record, save the old values.
  18.     If Not bWasNewRecord Then
  19.         sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser, audReason ) " & _
  20.             "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, '" & audReason & "' AS Expr4, " & sTable & ".* " & _
  21.             "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  22.             Debug.Print sSQL
  23.         db.Execute sSQL, dbFailOnError
  24.  
  25.     End If
  26.     AuditEditBegin = True
  27.  
  28. Exit_AuditEditBegin:
  29.     Set db = Nothing
  30.     Exit Function
  31.  
  32. Err_AuditEditBegin:
  33.     Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", , False)
  34.     Resume Exit_AuditEditBegin
  35. End Function
  36.  
Hopefullt that works,

Joe P.
Mar 29 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.