Issues with audit logger

[newguy86]

I setup an audit logger to log any changes made to records in access. The issue I am having is that whenever the logger runs it is only catching at most 4 changes at a time, but if I step through my code it will log every single change made.

If anyone can help me figure this out it would be greatly appreciated.

Here is my code:

Public Function WriteAudit06(frm As Form, lngID As String) As Boolean
On Error GoTo err_WriteAudit06

    Dim ctlC As Control
    Dim ctlCName As String
    Dim ctlCOldValue As String
    Dim fldF As Field
    Dim strSQL As String
    Dim bOK As Boolean
    Dim strFormID As String
    Dim strUserID As String
    
    bOK = False
    strFormID = "09"
    DoCmd.SetWarnings False
    
    strUserID = GetUserName_TSB
    
    ' For each control.
    For Each ctlC In frm.Controls
        If TypeOf ctlC Is TextBox Or TypeOf ctlC Is ComboBox Or TypeOf ctlC Is CheckBox Then
            ctlCName = ctlC.Name
            If IsNull(ctlC.OldValue) Then
                ctlCOldValue = "Null"
            Else
                ctlCOldValue = ctlC.OldValue
            End If
            If ctlC <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
                If Not IsNull(ctlC) Then
                    strSQL = "INSERT INTO tblDbLog (RecordID, UserID, DbID, FrmID, ActivityID, FieldChanged, FieldChangedFrom, FieldChangedTo, DateOfHit) " & _
                        "SELECT" & "'" & Form_frmIMStationsNewRequestLog.RequestNumber & "', " & _
                        "'" & strUserID & "', " & _
                        "'" & "06" & "', " & _
                        "'" & strFormID & "', " & _
                        "'" & "01" & "', " & _
                        "'" & ctlCName & "', " & _
                        "'" & Replace(ctlCOldValue, "'", "''") & "', " & _
                        "'" & Replace(ctlC, "'", "''") & "', " & _
                        "'" & Now() & "'"
                    'Debug.Print strSQL
                    DoCmd.RunSQL strSQL
                End If
            End If
        End If
    Next ctlC
    
    WriteAudit06 = bOK
    
exit_WriteAudit06:
    DoCmd.SetWarnings True
    Exit Function
    
err_WriteAudit06:
    'MsgBox Err.Description
    Resume exit_WriteAudit06
    
End Function

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[newguy86]

tblDbLog is a linked Access table.

I will look into it but do you have any suggestions on how to go about wrapping them in a single transaction and then committing them all at once?

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[PHV]

No unique index created on DateOfHit ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newguy86]

Sorry for the long delay between responses. Things picked up a little here and I didn't have a chance to work on this.

PHV,
There was no unique index created on DateOfHit because I setup that field to use the Memo Datatype.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!