Opinion on Code - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Opinion on Code

Ben Perry

13 hours ago

I apologize for breaking a rule here and absolutely understand if this goes unanswered or rejected to post. I am working on an auditing procedure and have come up with the code below. I am hoping someone with more experienced eyes on it could tell me their opinion on whether or not this is acceptable or clean code. I will say it appears to do what I am wanting so there is a win. Additionally, if anyone wants to use it feel free I can provide table structure and calls if needed.

Details

Public Sub AuditChanges(frm As Form, Optional RecordIDField As String, Optional Description As String)

    Dim UserID As Long
    Dim ctl As Control
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim recordID As Variant

    If IsNull(TempVars!UserID) Then
        UserID = 0
    Else
        UserID = TempVars!UserID
    End If

    If RecordIDField = "" Then
        recordID = Null
    Else
        recordID = frm.Controls(RecordIDField).Value
    End If

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("ActivityLogT", dbOpenDynaset)
    
    If Nz(Description, "") <> "" Then
        rs.AddNew
        rs!FormName = frm.Name
        rs!Description = Description
        rs.Update
    End If
    
    For Each ctl In frm.Controls
        If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox) Then
             If ctl.ControlSource <> "" Then
                 If Nz(ctl.Value, "") <> Nz(ctl.OldValue, "") Then
                    rs.AddNew
                    rs!UserID = UserID
                    rs!TableName = frm.RecordSource
                    rs!recordID = recordID
                    rs!FieldName = ctl.ControlSource
                    rs!OldValue = Nz(ctl.OldValue, "NULL")
                    rs!NewValue = Nz(ctl.Value, "NULL")
                    rs!FormName = frm.Name
                    rs.Update
                End If
            End If
        End If
    Next ctl

    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

	Alex Hedley @Reply 12 hours ago
Any Error Handling?

Ben Perry OP @Reply

12 hours ago

The procedure is exactly as it is. I tried to do the error handling in the if blocks. I am testing on a non production database that does not have user tracking. Results so far have been positive. I will watch the video here in the next couple hours and see what needs done. The intention is to be able to pass true data changes (only for now) when applicable but also track who logs in and what forms they are navigating through which is the reason for the 'double' update passing only a description of what is happening. I am calling this from the on load, on unload, and before update events on whatever forms I want tracked.

Next Unseen

New Feature: Comment Live View