Solved AuditChanges not capturing ID for NEW additions (1 Viewer)

[BusyBusyJr]

Novice programmer here so any assistance is appreciated! Have a strange problem here where AuditChanges isn't working quite right after migrating backend DB to
SQL. Front End remains in Access. The below AuditChange module is not pulling the PrimaryKey for any new record being created. It is however pulling the correct PrimaryKey for any EDIT actions being captured. Anyone have an idea where my problem may be. SQL tables are configured properly and all forms/sub-forms work as intended.

Sub AuditChanges(IDField As String, UserAction As String)
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:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit
End Sub

 

[theDBguy]

Hi. Welcome to AWF!

Which event are you using for auditing new records? There is a major difference between when Access and SQL Server generates an Autonumber or Identity field, which may be causing your issue.

 

[BusyBusyJr]

Thanks for the reply. I'm calling the module when the form(s) are closed by

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

Primary Keys are set in SQL and the Identity Specification configured correctly I believe.

 

[theDBguy]

Just as a test, try using the AfterUpdate event to see if the behavior changes.

 

[Pat Hartman]

Just as a test, try using the AfterUpdate event to see if the behavior changes.

No need for a test.

When the BE is SQL Server et al, the autonumber is not generated until the record is saved. That happens between the form's BeforeUpdate event and the form's AfterUpdate event. Therefore, the record has not yet been saved in the BeforeUpdate event.

Also, since the New Record flag will be false by the time the AfterUpdate event runs, you need to make yourself a note in the form's BeforeUpdate event and use that value latter in your log rather than checking the New Record flag.

 

[BusyBusyJr]

Thanks for the inputs. Module Code left as was. Created new hidden field on each form for the next ID in sequence and called it out in the event. Things work as intended.

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

 

[theDBguy]

Thanks for the inputs. Module Code left as was. Created new hidden field on each form for the next ID in sequence and called it out in the event. Things work as intended.

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

Hi. Glad to hear you got it sorted out. I don't know how you're generating the "next ID;" but I know in Access, Autonumber field values are not guaranteed to be sequential. It might be the case as well with SQL Server. Good luck!

 

[Pat Hartman]

Thanks for the inputs. Module Code left as was. Created new hidden field on each form for the next ID in sequence and called it out in the event. Things work as intended.

Apparently you don't bother with validation code in the form's BeforeUpdate event so who knows what even gets saved. Wait until you start seeing "empty" records show up.