Audit Trail code not working

M

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I am trying to use the following code for audit in an access database,but nothing is being added to the tables. Am I missing something?

Code: 
[FONT=Calibri][SIZE=3][COLOR=#000000]Option Compare Database[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Option Explicit[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Private Const conMod As String = "ajbAudit"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Private Declare Function apiGetUserName Lib "advapi32.dll"Alias _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    "GetUserNameA"(ByVal lpBuffer As String, nSize As Long) As Long[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]Function NetworkUserName() As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'On Error GoTo Err_Handler[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Returns the network loginname[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim lngLen As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim lngX As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim strUserName As String[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    NetworkUserName ="Unknown"[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    strUserName = String$(254, 0)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    lngLen = 255&[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    lngX =apiGetUserName(strUserName, lngLen)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    If (lngX > 0&) Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        NetworkUserName =Left$(strUserName, lngLen - 1&)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End If[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Exit_Handler:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Exit Function[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Err_Handler:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Call LogError(Err.Number,Err.Description, conMod & ".NetworkUserName", , False)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Resume Exit_Handler[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Function[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]Function AuditDelBegin(sTable As String, sAudTmpTable As String,sKeyField As String, lngKeyValue As Long) As Boolean[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'On Error GoTo Err_AuditDelBegin[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Write a copy of the recordto a tmp audit table.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'sTable = name of table to beaudited.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'sAudTmpTable = the name ofthe temp audit table.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'sKeyField = name ofAutoNumber field in table.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'lngKeyValue = number in theAutoNumber field.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'True if successful.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Call from a form's Deleteevent. Example:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '   CallAuditDelBegin("RET_Accumulated", "audTmpRETAccumulutated","Exam_ID", Me.Exam_ID)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Must also call AuditDelEndin the form's AfterDelConfirm event.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim db As DAO.Database           ' Current database[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim sSQL As String               ' Append query.[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    'Append record to the tempaudit table.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set db = DBEngine(0)(0)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    sSQL = "INSERT INTO" & sAudTmpTable & " ( audType, audDate, audUser ) "& _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        "SELECT 'Delete' ASExpr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable &".* " & _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        "FROM " &sTable & " WHERE (" & sTable & "." &sKeyField & " = " & lngKeyValue & ");"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    db.Execute sSQL,dbFailOnError[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Exit_AuditDelBegin:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set db = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Exit Function[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Err_AuditDelBegin:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Call LogError(Err.Number,Err.Description, conMod & ".AuditDelBegin()", , False)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Resume Exit_AuditDelBegin[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Function[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]Function AuditDelEnd(sAudTmpTable As String, sAudTable As String,Status As Integer) As Boolean[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'On Error GoTo Err_AuditDelEnd[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Purpose:    If the deletion was completed, copy thedata from the[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '                temp table to the autit table.Empty temp table.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Arguments:  sAudTmpTable = name of temp audit table[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            sAudTable = name of audit table[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            Status = Status from the form'sAfterDelConfirm event.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Return:     True if successful.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Usage:      Call from form's AfterDelConfirm event.Example:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '                CallAuditDelEnd("audTmpInvoice", "audInvoice", Status)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim db As DAO.Database           ' Currrent database[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim sSQL As String               ' Append query.[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    ' If the Delete proceeded, copythe record(s) from temp table to delete table.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    ' Note: Only"Delete" types are copied: cancelled Edits may be there as well.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set db = DBEngine(0)(0)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    If Status = acDeleteOK Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        sSQL = "INSERT INTO" & sAudTable & " SELECT " & sAudTmpTable &".* FROM " & sAudTmpTable & _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            " WHERE ("& sAudTmpTable & ".audType = 'Delete');"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        db.Execute sSQL,dbFailOnError[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End If[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    'Remove the temp record(s).[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    sSQL = "DELETE FROM" & sAudTmpTable & ";"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    db.Execute sSQL,dbFailOnError[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    AuditDelEnd = True[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Exit_AuditDelEnd:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set db = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Exit Function[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Err_AuditDelEnd:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Call LogError(Err.Number,Err.Description, conMod & ".AuditDelEnd()", False)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Resume Exit_AuditDelEnd[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Function[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]Function AuditEditBegin(sTable As String, sAudTmpTable As String,sKeyField As String, _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    lngKeyValue As Long,bWasNewRecord As Boolean) As Boolean[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'On Error GoTo Err_AuditEditBegin[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Purpose:    Write a copy of the old values to temptable.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            It is then copied to the true audittable in AuditEditEnd.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Arugments:  sTable = name of table being audited.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            sAudTmpTable = name of the tempaudit table.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            sKeyField = name of the AutoNumberfield.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            lngKeyValue = Value of the AutoNumberfield.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            bWasNewRecord = True if this was anew insert.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Return:     True if successful[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Usage:      Called in form's BeforeUpdate event.Example:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '                bWasNewRecord = Me.NewRecord[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '                CallAuditEditBegin("tblInvoice", "audTmpInvoice","InvoiceID", Me.InvoiceID, bWasNewRecord)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim db As DAO.Database           ' Current database[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim sSQL As String[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    'Remove any cancelled updatestill in the tmp table.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set db = DBEngine(0)(0)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    sSQL = "DELETE FROM" & sAudTmpTable & ";"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    db.Execute sSQL[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    ' If this was not a newrecord, save the old values.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    If Not bWasNewRecord Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        sSQL = "INSERT INTO" & sAudTmpTable & " ( audType, audDate, audUser ) "& _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            "SELECT'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " &sTable & ".* " & _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            "FROM "& sTable & " WHERE (" & sTable & "." &sKeyField & " = " & lngKeyValue & ");"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        db.Execute sSQL,dbFailOnError[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    AuditEditBegin = True[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Exit_AuditEditBegin:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set db = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Exit Function[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Err_AuditEditBegin:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Call LogError(Err.Number,Err.Description, conMod & ".AuditEditBegin()", , False)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Resume Exit_AuditEditBegin[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Function[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]Function AuditEditEnd(sTable As String, sAudTmpTable As String,sAudTable As String, _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    sKeyField As String,lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'On Error GoTo Err_AuditEditEnd[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Purpose:   Write the audit trail to the audit table.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Arguments:  sTable = name of table being audited.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            sAudTmpTable = name of the tempaudit table.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            sAudTable = name of the audittable.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            sKeyField = name of the AutoNumberfield.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            lngKeyValue = Value of theAutoNumber field.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '            bWasNewRecord = True if this was anew insert.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Return:     True if successful[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Usage:      Called in form's AfterUpdate event.Example:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    '                CallAuditEditEnd("tblInvoice", "audTmpInvoice","audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim db As DAO.Database[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim sSQL As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set db = DBEngine(0)(0)[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    If bWasNewRecord Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        ' Copy the new values as"Insert".[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        sSQL = "INSERT INTO" & sAudTable & " ( audType, audDate, audUser ) " &_[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            "SELECT 'Insert'AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable &".* " & _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            "FROM "& sTable & " WHERE (" & sTable & "." &sKeyField & " = " & lngKeyValue & ");"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        db.Execute sSQL,dbFailOnError[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Else[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        ' Copy the latest editfrom temp table as "EditFrom".[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        sSQL = "INSERT INTO" & sAudTable & " SELECT TOP 1 " & sAudTmpTable& ".* FROM " & sAudTmpTable & _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            " WHERE ("& sAudTmpTable & ".audType = 'EditFrom') ORDER BY " &sAudTmpTable & ".audDate DESC;"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        db.Execute sSQL[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        ' Copy the new values as"EditTo"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        sSQL = "INSERT INTO" & sAudTable & " ( audType, audDate, audUser ) " &_[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            "SELECT 'EditTo'AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable &".* " & _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            "FROM "& sTable & " WHERE (" & sTable & "." &sKeyField & " = " & lngKeyValue & ");"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        db.Execute sSQL[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        ' Empty the temp table.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        sSQL = "DELETE FROM" & sAudTmpTable & ";"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        db.Execute sSQL,dbFailOnError[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    AuditEditEnd = True[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Exit_AuditEditEnd:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set db = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Exit Function[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Err_AuditEditEnd:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Call LogError(Err.Number,Err.Description, conMod & ".AuditEditEnd()", , False)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Resume Exit_AuditEditEnd[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Function[/COLOR][/SIZE][/FONT]

I then call each function into the form:
Code: 
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub Form_AfterDelConfirm(Status As Integer)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    CallAuditDelEnd("audTmpMainRegData", "audMainRegData", Status)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub Form_AfterUpdate()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    CallAuditEditEnd("main_reg_data", "audTmpMainRegData","audMainRegData", "[Exam ID]", Nz(Me.Exam_ID, 0),bWasNewRecord)[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub Form_BeforeUpdate(Cancel As Integer)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    bWasNewRecord = Me.NewRecord[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    CallAuditEditBegin("main_reg_data", "audTmpMainRegData","[Exam ID]", Nz(Me.Exam_ID, 0), bWasNewRecord)[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub Form_Delete(Cancel As Integer)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    CallAuditDelBegin("main_reg_data", "audTmpMainRegData","[Exam ID]", Nz(Me.Exam_ID, 0))[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

Thank you
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
I imagine these
CallAuditDelEnd are typos and are really Call AuditDelEnd
AFAIC, it's not possible to shed much light on your problem since you don't provide much in the way of information. Sure, there's lots of code, but if there's nothing obvious then it could be anything. To be honest, I wouldn't give your code much more than a quick look because I've got so little to go on. You don't say if there are any error messages or if the code breaks (and if so, on what line). You don't say if it's one sql execution that doesn't work, or all of them. You don't say if you stepped through your code and monitored the variables. I think I can say that because I'm a little bit familiar with your posts and seem to recall you know of these things. Sorry if I'm wrong.

Maybe debug.print your sql variable to the immediate window, copy it, paste into a new query in sql view and switch to datasheet view. That won't show much when it's an append query, but at least if it a) doesn't complain, and b) shows that multiple rows (they'll be empty) would be added, then it indicates it would work. If it complains, it should highlight the offending part, or sometimes next to it. If you see no rows, then the query returns nothing based on the inputs you're providing. Time then to step through your code and check the variable values.


 
Upvote 0
Hello,

My apologies. I looked at it again and there were somefields on my table that were not being recognized.
It works now.
Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

CPGDeveloper
  • Question Question
Error 91 with Execute, Works Fine with Docmd.RunSQL?
Replies
4
Views
829
CPGDeveloper
CPGDeveloper
P
  • Question Question
Access - Write to an audit log
2
Replies
10
Views
2K
baderms
B

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top