Tracking changes to a form's fields---still searching for an answe

[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!

 

[Tom van Stiphout]

On Fri, 4 Jun 2010 04:54:08 -0700, Yappy

Put "Option Explicit" as the second line in EVERY module, and make it
the default under Tools > Options.

Your code works with Screen.ActiveControl, so everything depends on
which control is active when LogChanges is called.

You probably did not literally copy the BeforeUpdate code, because as
written it is highly unusual if not incorrect. This is the standard
version:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call LogChanges(VendorNumber)
End Sub

-Tom.
Microsoft Access MVP

 

[Douglas J. Steele]

See whether using PreviousControl, rather than ActiveControl, works. (I
suspect it won't, because if focus has moved from the control, I believe
you've lost the ability to check the control's previous value)

 

[Yappy]

Douglas,
The old value and new value show with your suggestion; however, it is not
indicating the correct field in which the changes were made. For example, I
changed the City name from "testchange" to "Leesville" and the old and new
values were shown in the change table but it showed under the field name of
State/Province.

Looks like I am getting closer to the answer. Any other suggestions?

Thanks!

 

[Yappy]

Tom,
Thanks for your reply. Unfortunately your suggestions did not resolve my
issues.

 

[Douglas J. Steele]

Looking more closely at your code, it's actually doing exactly what you're
telling it to do. As Tom pointed out, your code is relying on which control
was active before you save the record. Which control was active before the
record was saved has nothing to do with which control was changed!

You might consider looping through all of the controls on the form to
determine which control(s) had changing in them.

 

[bobbie]

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!