Funtion code to log field changes into a table

[edson]

Fellows, I got the following function code from someone in the net and adapted the code to meet my needs. Unfortunately I lost contact with him.
As you can see the purpose of this function code is to register into the TblLogChanges every single record exclusion or record change made in any form field of my project when the form is active. In other words if the user changes for example the value of the field Color in the form FrmX from Yellow to Black, the TblLogChanges will have added a line indicating: FrmX, type of change (record change or record exclusion), RecordId (in my case CADID), Current User, Date, and a complimentary string showing Field Color old value=Yellow --> Field Color new value=Black.
It works fine when the field “old” value is populated (not null).
However, if the feld old value is null, meaning, if the field value is originally null, or yet if the field is originally empty, and I fill it up with some data, then the code does not add an indicative line into the table TblLogChanges.
See function code below.
FYI the function is called in both Before Update and Delete events of the form with the following syntaxes: =logChangeFrmCadastro("E") for record exclusions, and =logChangeFrmCadastro("A") for record changes.
I have failed so far to make it happen. Reason is I'm able to read and understand most written syntax but still not good enough to create my own codes since I do not know to use the range of VBA commands and syntaxes available yet. This is a knowledge gap I'm doing my best to fill in.
In the mean time does anybody could tell me please what function code changes are required so the function be able to add an indicative line into TblLogChanges when the user changes a form field from “empty/nothing” to something?
Thank you.

Option Compare Database
Option Explicit
 
Function LogChangeFrmCadastro(strTipo As String)
On Error Resume Next
 
Dim db As Database, rslog As Recordset
Dim frm As Form, I As Integer
Dim strLog As String
 
Set db = CurrentDb
Set rslog = db.OpenRecordset("TblLogChanges")
Set frm = Screen.ActiveForm
   
For I = 0 To frm.Controls.Count - 1
    If TypeOf frm.Controls(I) Is TextBox Or TypeOf frm.Controls(I) Is ComboBox Or TypeOf frm.Controls(I) Is CheckBox Or TypeOf frm.Controls(I) Is OptionGroup Or TypeOf frm.Controls(I) Is ListBox Then
        If strTipo = "E" Then
            If strLog = "" Then
                strLog = frm.Controls(I).Name & " " & frm.Controls(I).Value
            Else
                strLog = strLog & "," & frm.Controls(I).Name & ":" & frm.Controls(I).Value
            End If
        Else
            If frm.Controls(I).OldValue <> frm.Controls(I).Value Then
                If strLog = "" Then
                     strLog = strLog & "CADID = " & frm.Controls("CADID") & "," & frm.Controls(I).Name & ":" & frm.Controls(I).OldValue & " --> " & frm.Controls(I).Value
                Else
                    strLog = strLog & "," & frm.Controls(I).Name & ":" & frm.Controls(I).OldValue & " --> " & frm.Controls(I).Value
                End If
            End If
        End If
    End If
Next
   
rslog.AddNew
rslog("strNomeForm") = frm.Name
rslog("strTipoLog") = strTipo
rslog("mmolog") = strLog
rslog("strUser") = CurrentUser
rslog("dtLog") = Now
On Error Resume Next
rslog.Update
rslog.Close
db.Close
 
End Function

[June7]

Maybe have to use a text substitute, like:

Nz(frm.Controls(I).OldValue, "Null")

or

Nz(frm.Controls(I).OldValue, "nothing")


Look at Allen Browne's code http://allenbrowne.com/AppAudit.html

[edson]

Hello June7. Happy New Year.
Text substitute did not work either. Also have tried " If Isnull (strLog) Then... " which also failed.
I took a look at the Allen Browne's code. Actually you suggested it to me some time ago. However it is too complex for me and it has several restrictions. It seems to me that the one above is more simplist and would fulfill my needs, except so far for the field changes from null/nothing to something.
Any other ideas please?
Thank you.

[June7]

Where does the code fail - on the If Then condition?

Another thread with similar question http://stackoverflow.com/questions/1...trols-within-v

If frm.Controls(I).OldValue & "" <> frm.Controls(I).Value & "" Then

[edson]

Thank you once more June7. You have helped me a lot. I got my problem solved adapting your suggestion for me to read http://stackoverflow.com/questions/1...trols-within-v
By the way, I fixed the issue of registering a field data change from nothing (null) to something and vice versa through if conditions. Also I replaced data source by the FrmName. It works as a charm.
I would like to offer this following code enhancement as a single contribution to others who may be interested on that matter, so if you feel that the enhancement is valid and smart enough, feel free to publish it. I believe it would be more reliable for the readers if you publish it. You have a lot of more credit than I do. Thanks again.

[code]
Sub AuditTrail (frm As Form, RecordID As Control)
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim StrSQL As String
On Error GoTo ErrHandler

For Each ctl In frm.Controls
With ctl
If RecordID.Value >= DMax("PrimaryKeyFieldName", "TableName") Then
GoTo MOVENEXT
Else
If .ControlType = acTextBox Or .ControlType = acComboBox Or .ControlType = acCheckBox Or .ControlType = acOptionGroup Or .ControlType = acListBox Then
If IsNull(.Value) And IsNull(.OldValue) Then
GoTo MOVENEXT
Else
If IsNull(.OldValue) Then
varBefore = ""
varAfter = .Value
strControlName = .Name
GoTo INSERTION
Else
If IsNull(.Value) Then
varBefore = .OldValue
varAfter = ""
strControlName = .Name
GoTo INSERTION
Else
If .Value = .OldValue Then
GoTo MOVENEXT
Else
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
INSERTION:
StrSQL = "INSERT INTO " _
& "TblAudit (EditDate, User, RecordID," _
& " FrmName, SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & CurrentUser & cDQ & ", " _
& cDQ & RecordID.Value & cDQ & ", " _
& cDQ & frm.Name & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
End If
End If
End If
End If
End If
End If
End With
MOVENEXT:
Next
Set ctl = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub
[code/]

[June7]

It's published by inclusion in this thread. Just a note, the CODE tags not typed properly so the indention was lost. The / goes first, like /CODE.