Tracking changes to a form

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!
0
Utf
6/3/2010 3:55:15 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
1514 Views

Similar Articles

[PageSpeed] 59

"Yappy" <Yappy@discussions.microsoft.com> wrote in message 
news:786E06D5-B560-494A-9E53-0F37BA21EC62@microsoft.com...
>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! 


0
Roger
6/3/2010 4:56:38 PM
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" wrote:

> 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!
0
Utf
6/3/2010 8:32:57 PM
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.

"tighe" wrote:

> 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" wrote:
> 
> > 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!
0
Utf
6/4/2010 11:51:45 AM
Reply:

Similar Artilces: