Prev: OutputTo isn't avaialable now
Next: recording the print date
From: Yappy on 3 Jun 2010 11:55
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!
From: Roger Carlson on 3 Jun 2010 12:56

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


From: tighe on 3 Jun 2010 16:32
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!
From: Yappy on 4 Jun 2010 07:51
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!
 | 
Pages: 1
Prev: OutputTo isn't avaialable now
Next: recording the print date