Audit TextBox changes in Access for

[gcarpenter]

This code doesn't add a new entry in the audit table, but logs changes. Here is the code that isn't adding the new data entered into the textbox. Any help greatly appreciated.

For Each ctl In .Controls

' inspect only data-bound controls

Select Case ctl.ControlType

Case acTextBox, acComboBox, acCheckBox

If Nz(ctl.ControlSource, "") > "" Then

' if changed, record both old & new values

If Nz(ctl.OldValue, "") <> Nz(ctl, "") Then

rs.AddNew

rs!FormName = .NAME

rs!MyTable = MyTable

rs!MyField = MyField

rs!MyKey = MyKey

rs!ChangedOn = Now()

rs!FieldName = ctl.NAME

If ctl.ControlType = acCheckBox Then

rs!Field_OldValue = YesOrNo(ctl.OldValue)

rs!Field_NewValue = YesOrNo(ctl)

Else

rs!Field_OldValue = Left(Nz(ctl.OldValue, ""), 255)

rs!Field_NewValue = Left(Nz(ctl, ""), 255)

End If

rs!UserChanged = UserName()

rs!CompChanged = CompName()

rs.Update

End If

End If

End Select

Next ctl

End With

rs.Close

Set rs = Nothing

Set db = Nothing

 

[Gasman]

Walk through your code?
Hard to see much there, no indentation as no code tags.
Cannot see how you even get the correct rs record to update?

 

[gcarpenter]

Option Compare Database

Option Explicit

 

Sub TrackChanges(F As Form)

    Dim ctl As Control, frm As Form

    Dim MyField As String, MyKey As Long, MyTable As String

    Dim db As DAO.Database, rs As DAO.Recordset

    On Error Resume Next

    Set frm = F

    Set db = CurrentDb

    Set rs = db.OpenRecordset("tbl__ChangeTracker")

    With frm

        MyTable = .Tag

        ' find the primary key & its value, based on the Tag

        For Each ctl In .Controls

            If ctl.Tag = "PK" Then

                MyField = ctl.NAME

                MyKey = ctl

                Exit For

            End If

        Next ctl

        For Each ctl In .Controls

            ' inspect only data-bound controls

            Select Case ctl.ControlType

                Case acTextBox, acComboBox, acCheckBox

                    If Nz(ctl.ControlSource, "") > "" Then

                        ' if changed, record both old & new values

                        If Nz(ctl.OldValue, "") <> Nz(ctl, "") Then

                            rs.AddNew

                            rs!FormName = .NAME

                            rs!MyTable = MyTable

                            rs!MyField = MyField

                            rs!MyKey = MyKey

                            rs!ChangedOn = Now()

                            rs!FieldName = ctl.NAME

                            If ctl.ControlType = acCheckBox Then

                                rs!Field_OldValue = YesOrNo(ctl.OldValue)

                                rs!Field_NewValue = YesOrNo(ctl)

                            Else

                                rs!Field_OldValue = Left(Nz(ctl.OldValue, ""), 255)

                                rs!Field_NewValue = Left(Nz(ctl, ""), 255)

                            End If

                            rs!UserChanged = UserName()

                            rs!CompChanged = CompName()

                            rs.Update

                        End If

                    End If

            End Select

        Next ctl

    End With

    rs.Close

    Set rs = Nothing

    Set db = Nothing

End Sub

 

[Pat Hartman]

If Nz(ctl.ControlSource, "") > "" Then

should be

If Nz(ctl.ControlSource, "") <> "" Then

 

[Gasman]

I would still walk through the code for a new record?

 

[gcarpenter]

That didn't work Pat, if I open the form and type in a blank textbox, it doesn't log that, but if I change data in a textbox it logs it.

 

[Pat Hartman]

I don't see anything else. Have you stepped through the code as gasman suggsted?

 

[gcarpenter]

yes, nothing

 

[Gasman]

yes, nothing

How can it be nothing?
You must be able to see the path it takes and the values involved?

 

[gcarpenter]

yes, it ran through the code as expected. but still doesn't log a new entry into a textbox.

 

[Gasman]

Upload a compacted zipped db with enough data to recreate the issue, and instructions on what to do to recreate the issue