Audit Changes New Record Missing Primary Key

[dgreen]

I'm using the code and table structure pulled together by Joseph G. Njoroge (https://community.spiceworks.com/topic/1961724-audit-trail-for-access-database).

My issue is that it isn't picking up the AutoNumber Primary Key (Name_ID) for new records when writing the NEW row to the tblAuditTrail. Thoughts on modifying the code to get this key piece of information?

Here's how it currently looks, with the RecordID field showing blank for the NEW records. I want to pick up the autonumber that is generated when you leave the record.

I've tried moving the NewRecord portion of the code to the AfterUpdate but that didn't result in a change.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Call AuditChanges("Name_ID", "NEW")
Else
Call AuditChanges("Name_ID", "EDIT")
End If
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then Call AuditChanges("Name_ID", "DELETE")
End Sub

 

[theDBguy]

Hi. I think it's best to step through the code to find the cause of the problem. Can you post a demo version of your db?

 

[dgreen]

Give me a little time. Can do. Thanks for the help.

Hi. I think it's best to step through the code to find the cause of the problem. Can you post a demo version of your db?

 

[MajP]

I am assuming the Autonumber is not saved yet. Is this a linked SQL table or regular Access table? I am pretty sure if this is a SQL backend that number is not available until the record is saved. I doubt you can do this in the beforeupdate event, but what happens if you
If Me.NewRecord Then
me.dirty = false
Call AuditChanges("Name_ID", "NEW")
Else

 

[dgreen]

The table is a linked SharePoint List. The Autonumber doesn't get generated until I leave the row/record.

I am pretty sure if this is a SQL backend that number is not available until the record is saved.

When I added your me.dirty code it errored saying I can't do this in a Before Update function.

I tried Form_AfterInsert() and Form_AfterUpdate() with the below code.... Both failed.... with error Object doesn't support this property or method.

Since my issue is likely linked to where the new record is going to be created at which is a SharePoint List, what would be the next best approach?

 

[MajP]

So you cannot get the key until after you save the record, but then the record is not a new record. Maybe you can do something like making a global variable.
Public IsNewRecord as boolean

then in the before update
If Me.NewRecord Then
isNewRecord = true
Else

In the after update
If IsNewRecord Then
Call AuditChanges("Name_ID", "NEW")
isNewRecord = false
end if

 

[dgreen]

Here's the code as it stands now. When I compile, it errors on the Form_BeforeUpdate(), saying the procedure declaration doesn't match description of event or the procedure having the same name. I don't see another Form_BeforeUpdate() in this form's vba, so what's causing this issue?

Option Compare Database
Option Explicit

Public IsNewRecord As Boolean

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then Call AuditChanges("Name_ID", "DELETE")
End Sub

Private Sub Form_AfterUpdate()
On Error GoTo errhandler
If IsNewRecord Then
Call AuditChanges("Name_ID", "NEW")
IsNewRecord = False
End If
errhandler: Call LogError
End Sub

Private Sub Form_BeforeUpdate()
On Error GoTo errhandler
If Me.NewRecord Then
IsNewRecord = True
Else
Call AuditChanges("Name_ID", "EDIT")
End If
errhandler: Call LogError
End Sub

 

[MajP]

The before update does not have the proper signature should have a BeforeUpdate(Cancel as Integer)

 

[dgreen]

Ok. Compiles.... But different error. Error # 0? You may have gotten me past one problem and into the next.

 

[dgreen]

The new record is now showing up in tblAuditTrail with the Primary Key as a NEW record. See below. The top record is a result of your code modification. Need to figure out why the other error is happening.

 

[MajP]

you do not have exit sub before your error handler. So it runs every time.

 

[dgreen]

It's working right now.... Thank you for the work around to deal with New Records being created using a SharePoint List's autonumber as the Primary Key. Here's the code for the record.

Option Compare Database
Option Explicit

Private Sub Form_AfterDelConfirm(Status As Integer)
'https://community.spiceworks.com/topic/1961724-audit-trail-for-access-database
    On Error GoTo errhandler
    If Status = acDeleteOK Then Call AuditChanges("Name_ID", "DELETE")
    End If
    Exit Sub
errhandler:         Call LogError
End Sub

Private Sub Form_AfterUpdate()
'https://community.spiceworks.com/topic/1961724-audit-trail-for-access-database
    On Error GoTo errhandler
    If IsNewRecord Then
        Call AuditChanges("Name_ID", "NEW")
        IsNewRecord = False
    End If
    Exit Sub
errhandler:         Call LogError
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
'https://community.spiceworks.com/topic/1961724-audit-trail-for-access-database
'https://www.access-programmers.co.uk/forums/threads/audit-changes-new-record-missing-primary-key.310140/#post-1676707
    On Error GoTo errhandler
    If Me.NewRecord Then
        IsNewRecord = True
    Else
        Call AuditChanges("Name_ID", "EDIT")
    End If
    Exit Sub
errhandler:         Call LogError
End Sub

Then here is the AuditChanges module

Option Compare Database
Option Explicit

Sub AuditChanges(IDField As String, UserAction As String)
'https://community.spiceworks.com/topic/1961724-audit-trail-for-access-database

    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")
    Select Case UserAction
        Case "EDIT"
            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
                            ![Action] = UserAction
                            ![RecordID] = Screen.ActiveForm.Controls(IDField).value
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![NewValue] = ctl.value
                            .Update
                        End With
                    End If
                End If
            Next ctl
        Case Else
            With rst
                .AddNew
                ![DateTime] = datTimeCheck
                ![UserName] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![Action] = UserAction
                ![RecordID] = Screen.ActiveForm.Controls(IDField).value
                .Update
            End With
    End Select
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err: Call LogError
    MsgBox "Error has been logged", vbOKOnly, "Error"
    Resume AuditChanges_Exit
End Sub

And finally here's the code and path to LogError

Option Compare Database
Option Explicit

Public Function LogError()  'Log error information to tblErrorLog.
'https://www.techrepublic.com/blog/microsoft-office/how-to-log-errors-in-microsoft-access/

Dim strDescription As String
Dim strSQL As String
Dim strMsg As String 'string for message box

strDescription = Chr(34) & Err.Description & Chr(34)
strSQL = "INSERT INTO tblErrorLog (ErrDate, CompName, UsrName, " _
    & " ErrNumber, ErrDescription, ErrModule)" _
    & " VALUES(#" & Now() & "#, '" & Environ("computername") _
    & "', '" & CurrentUser & "', " & Err.Number _
    & ", " & strDescription & ", '" & VBE.ActiveCodePane.CodeModule & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
    strMsg = "An unexpected situation arose in your program." & vbCrLf & _
        "Please write down the following details:" & vbCrLf & vbCrLf & _
        "Error Module: " & VBE.ActiveCodePane.CodeModule & vbCrLf & _
        "Error Number " & Err.Number & vbCrLf & strDescription & vbCrLf & vbCrLf & _
        "Unable to record because Error " & Err.Number & vbCrLf & strDescription
    MsgBox strMsg, vbCritical, "LogError()"
End Function

 

[dgreen]

@MajP - I claimed success too early NEW and DELETE are working but the EDIT function errors but in the end puts the value into the tblAuditTrail.

Private Sub Form_BeforeUpdate(Cancel As Integer) 'https://community.spiceworks.com/topic/1961724-audit-trail-for-access-database 'https://www.access-programmers.co.u...cord-missing-primary-key.310140/#post-1676707 On Error GoTo errhandler If Me.NewRecord Then IsNewRecord = True Else Call AuditChanges("Name_ID", "EDIT") End If Exit Sub errhandler: Call LogError End Sub

If Me.NewRecord then ' this value is showing as 0.
IsNewRecord = true 'this value is showing as false. Using the F8 it skips this part of the vba and moves to the ELSE below.
Else
Call AuditChanges("Name_ID", "EDIT" ' it hits this line and logs the value but then it moves into the errhandler. Captures Err# 438, Object doesn't support this property or method.
End If
Exit Sub
errhandler: Call LogError
End Sub

 

[dgreen]

@MajP Here's a mock database that is having issues.
Current issue: New and Deleted records error and don't get captured. Changed record don't error but also don't get captured. Once this gets resolved, then I'm hoping when I implement with my SharePoint Linked List, I'll be up and running.

I'm using Joseph Njoroge's Audit Trail for Access Database (https://community.spiceworks.com/topic/1961724-audit-trail-for-access-database) and Smilycoder's Error tool (https://thesmileycoder.com/crash-reporter/) to capture the program errors.

Not sure what I'm doing wrong.

 

[dgreen]

Thoughts from anyone on the attached file and troubleshooting?

 

[theDBguy]

Thoughts from anyone on the attached file and troubleshooting?

Hi. I just downloaded your file. When I open it, I am presented with a form. What steps do I need to do to see the problem? Thanks.

 

[dgreen]

Add a new record to the tasks on the right side of the form.
Delete a record on the tasks.
Edit a record on the tasks.

In all 3 cases, tAuditTrail won't get updated. In the top 2 cases there will also be an error.

Hi. I just downloaded your file. When I open it, I am presented with a form. What steps do I need to do to see the problem? Thanks.

 

[theDBguy]

Hi. Thanks for the steps. The error message says:

Error 2465 has occured
Microsoft Access can't find the field 'taskauto' referred to in your expression.

I suspect the table does not get updated because there was an error. Have you checked what "taskauto" is it complaining about missing?

 

[dgreen]

Taskauto is the primary key and is an autonumber in the t_Taskers table. Based on what I'm tracking, to get the tblAuditTrail to populate you call the primary key in the call function.

Call AuditChanges("taskauto", "DELETE")
Call AuditChanges("taskauto", "NEW")
Call AuditChanges("taskauto", "EDIT")

 

[theDBguy]

Taskauto is the primary key and is an autonumber in the t_Taskers table. Based on what I'm tracking, to get the tblAuditTrail to populate you call the primary key in the call function.

Call AuditChanges("taskauto", "DELETE")
Call AuditChanges("taskauto", "NEW")
Call AuditChanges("taskauto", "EDIT")

Hi. I was playing with it before I saw your last reply. See if you can play with the attached modified version of your db. I'll try to check it again using the new info you just provided and let you know if I have any updates after that. Cheers!