Hi! I recently used austin06's youtube tutorial on how to add an auditing function to my database. It worked initially, however I decided that I'd go ahead and put the front end of the database on a Navigation Form. Upon this update, I lost the ability to use the auditing function. I know why it's doing that, however I am not savvy with coding so if anyone could assist me I'd be extremely grateful!
Below is the module I created that originally worked:
Below is the code that I applied to each individual form:
This will sound annoying, but there is also one tab on my Navigation Form that leads into another Navigation Form. I imagine this creates a "Subform on a Subform," issue, but if y'all can help I would very much appreciate it! Thank you!
Below is the module I created that originally worked:
Code:
Option Compare Database
Public Function AuditChanges(RecordID As String, UserAction As String)
Dim DB As Database
Dim RST As Recordset
Dim clt As Control
Dim UserLogin As String
Set DB = CurrentDb
Set RST = DB.OpenRecordset("select * from audittrail", dbOpenDynaset)
UserLogin = Environ("Username")
Select Case UserAction
Case "New"
With RST
.AddNew
![DateTime] = Now()
!UserName = UserLogin
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID).Value
.Update
End With
Case "Delete"
With RST
.AddNew
![DateTime] = Now()
!UserName = UserLogin
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID).Value
.Update
End With
Case "Edit"
For Each clt In Screen.ActiveForm.Controls
If (clt.ControlType = acTextBox _
Or clt.ControlType = acComboBox) Then
If Nz(clt.Value) <> Nz(clt.OldValue) Then
With RST
.AddNew
![DateTime] = Now()
!UserName = UserLogin
!FormName = Screen.ActiveForm.Name
!Action = UserAction
!RecordID = Screen.ActiveForm.Controls(RecordID).Value
!FieldName = clt.ControlSource
!OldValue = clt.OldValue
!newValue = clt.Value
.Update
End With
End If
End If
Next clt
End Select
RST.Close
DB.Close
Set RST = Nothing
Set DB = Nothing
End FunctionBelow is the code that I applied to each individual form:
Code:
Option Compare Database
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Call AuditChanges("VendorID", "New")
Else
Call AuditChanges("VendorID", "Edit")
End If
End SubThis will sound annoying, but there is also one tab on my Navigation Form that leads into another Navigation Form. I imagine this creates a "Subform on a Subform," issue, but if y'all can help I would very much appreciate it! Thank you!
