Tracking changes to a form

[Yappy]

I have a table and module set up to track all changes made to a record on my
form. I am using the following code for the module:

Option Compare Database

Function LogChanges(lngID As Long, Optional strField As String = "")
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim varOld As Variant
Dim varNew As Variant
Dim strFormName As String
Dim strControlName As String

varOld = Screen.ActiveControl.OldValue
varNew = Screen.ActiveControl.Value
strFormName = Screen.ActiveForm.Name
strControlName = Screen.ActiveControl.Name
Set dbs = CurrentDb()
Set rst = dbs.TableDefs("ztblDataChanges").OpenRecordset

With rst
.AddNew
!FormName = strFormName
!ControlName = strControlName
If strField = "" Then
!FieldName = strControlName
Else
!FieldName = strField
End If
!RecordID = lngID
!UserName = Environ("username")
If Not IsNull(varOld) Then
!OldValue = CStr(varOld)
End If
!NewValue = CStr(varNew)
.Update
End With
'clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function


My data changes table consists of the following fields:
LogId--AutoNumber & Primary Key
FormName--Text
ControlName--Text
FieldName--Text
RecordID--Text (This is set as text because my primary key in my main table
is text)
UserName--Text
OldValue--Text
NewValue--Text
TimeStamp--Date/Time Default Value=Now()

The Before Update event procedure on my form is:
Private Sub BeforeUpdate(Cancel As Integer)
Call LogChanges(VendorNumber)
End Sub

My problem is that the tracking table (ztblDataChanges) records the info in
the field following the actual field that was changed.

What can I do to correct this problem? I am using Access 2003.

Any help would be much appreciated.

Thanks!

 

[Roger Carlson]

I have a table and module set up to track all changes made to a record on
my
form. I am using the following code for the module:

Option Compare Database

Function LogChanges(lngID As Long, Optional strField As String = "")
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim varOld As Variant
Dim varNew As Variant
Dim strFormName As String
Dim strControlName As String

varOld = Screen.ActiveControl.OldValue
varNew = Screen.ActiveControl.Value
strFormName = Screen.ActiveForm.Name
strControlName = Screen.ActiveControl.Name
Set dbs = CurrentDb()
Set rst = dbs.TableDefs("ztblDataChanges").OpenRecordset

With rst
.AddNew
!FormName = strFormName
!ControlName = strControlName
If strField = "" Then
!FieldName = strControlName
Else
!FieldName = strField
End If
!RecordID = lngID
!UserName = Environ("username")
If Not IsNull(varOld) Then
!OldValue = CStr(varOld)
End If
!NewValue = CStr(varNew)
.Update
End With
'clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function


My data changes table consists of the following fields:
LogId--AutoNumber & Primary Key
FormName--Text
ControlName--Text
FieldName--Text
RecordID--Text (This is set as text because my primary key in my main
table
is text)
UserName--Text
OldValue--Text
NewValue--Text
TimeStamp--Date/Time Default Value=Now()

The Before Update event procedure on my form is:
Private Sub BeforeUpdate(Cancel As Integer)
Call LogChanges(VendorNumber)
End Sub

My problem is that the tracking table (ztblDataChanges) records the info
in
the field following the actual field that was changed.

What can I do to correct this problem? I am using Access 2003.

Any help would be much appreciated.

Thanks!

 

[tighe]

i'm going to give props to Allen Browne here:
http://allenbrowne.com/AppAudit.html

i use it, it works, but sorry it not a correction to your current code.

 

[Yappy]

Thanks, Tighe! I have tried Allen's method with no success. The process I
described below is the closest I have come to getting the tracking to work.