Issue Creating Audit Trail for fields on a Form

[nika.duncan]

Good morning


I have been trying to use Audit Trail on three forms use to capture data re mail distribution within an organization. The three forms are:

1. MailStatus
2. MailStatusMailMan
3. FrmLogin

The function works for the first two forms listed above with bounded fields but the third form "FrmLogin" where I would want to capture the login name is not feeding the information into the table "tblAuditTrail". Please see table structure below where I added the field highlighted in red "LoginID" to track users who log on to the database. The function used with the audit trail table is also attached. The codes highlighted in orange are the codes I tried to add to include username in the audit table. The other codes listed below were attached to the form "FrmLogin" using the field name "CboUser" which is the field in which the username is stored. This is an unbound field not sure if it makes a difference.

Please any help will be greatly appreciated.

Name Type Size
AuditTrailID Long Integer 4
DateTime Date/Time 8
UserName Text 50
FormName Text 50
LoginID Text 255
Action Text 255
RecordID Text 50
FieldName Text 50
OldValue Text 50
NewValue Text 50

Option Compare DatabaseOption Explicit
'
' ================================================
' Code by Martin Green Email: martin@fontstuff.com
' Visit my Office Tips website @ www.fontstuff.com
' YouTube tutorials www.youtube.com/martingreenvba
' ================================================
'


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
    Dim loginUserID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    loginUserID = Screen.ActiveForm.CboUser
    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
                                   ![LoginID] = loginUserID
                            ![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
                    ![LoginID] = loginUserID
                ![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

Private Sub Command17_Click()

 DoCmd.OpenTable "tblAuditTrail"
 
End Sub




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


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

Nika

[pbaldy]

The OldValue property doesn't not work on an unbound control.

[ItsMe]

You declare loginUserID but you never assign it a value.

EDIT: Never mind, I see where you assigned it...

[nika.duncan]

How would I capture the changes to the unbound field on the form, that is the Login name.

Please

[pbaldy]

Do people log in and out without leaving the app? In apps I've created, they log in when the app starts and stay logged in until they exit.

[nika.duncan]

Yes they stay logged in until they exit because I use the login form to filter data based on department for the other forms that opens once logged in.

[pbaldy]

Then unless I'm missing something, there's no need to audit changes to the user field if it can't/won't change.

[nika.duncan]

What I want to happen is for the name of the person who logged onto the app to be displayed in the table as "LoginID". It does store the username, that is the person that sign in to the computer using the line of code below but the same person that signs onto the computer is not necessarily the same person that logged on to the application.

strUserID = Environ("USERNAME")

I would want to know more so whose logging onto the application and not necessarily the computer. Hope I explained clearly.

Nika

[Xarkath]

Hello,

I am going to take a guess and say it is this line here:

loginUserID = Screen.ActiveForm.CboUser

It is referencing "ActiveForm" but is it really the active form at the time the code processes? You might try refernicing the form by name instead.


My coding skills are not that strong so take this suggestion with a grain of salt. It just feels suspect to me.

Thanks