Audit TextBox changes in Access for (1 Viewer)

gcarpenter

Registered User.
Local time
Today, 02:48
Joined
Oct 21, 2013
Messages
68
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

Enthusiastic Amateur
Local time
Today, 06:48
Joined
Sep 21, 2011
Messages
14,048
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

Registered User.
Local time
Today, 02:48
Joined
Oct 21, 2013
Messages
68
Code:
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

Super Moderator
Staff member
Local time
Today, 02:48
Joined
Feb 19, 2002
Messages
42,981
If Nz(ctl.ControlSource, "") > "" Then

should be

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

Gasman

Enthusiastic Amateur
Local time
Today, 06:48
Joined
Sep 21, 2011
Messages
14,048
I would still walk through the code for a new record?
 

gcarpenter

Registered User.
Local time
Today, 02:48
Joined
Oct 21, 2013
Messages
68
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

Super Moderator
Staff member
Local time
Today, 02:48
Joined
Feb 19, 2002
Messages
42,981
I don't see anything else. Have you stepped through the code as gasman suggsted?
 

gcarpenter

Registered User.
Local time
Today, 02:48
Joined
Oct 21, 2013
Messages
68
yes, it ran through the code as expected. but still doesn't log a new entry into a textbox.
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:48
Joined
Sep 21, 2011
Messages
14,048
Upload a compacted zipped db with enough data to recreate the issue, and instructions on what to do to recreate the issue
 

Users who are viewing this thread

Top Bottom