Prev: create a form that collects parameters
Next: Number Error Problem
From: Yappy on 4 Jun 2010 07:54
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: Tom van Stiphout on 4 Jun 2010 10:01
On Fri, 4 Jun 2010 04:54:08 -0700, Yappy
<Yappy(a)discussions.microsoft.com> wrote:

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


>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: Douglas J. Steele on 4 Jun 2010 10:31
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)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"Yappy" <Yappy(a)discussions.microsoft.com> wrote in message
news:A0575259-8886-42D7-AF5B-F68E508C8ECE(a)microsoft.com...
>
> My problem is that the tracking table (ztblDataChanges) records the info
> in
> the field following the actual field that was changed.
>


From: bobbie on 4 Jun 2010 20:55

"Yappy" <Yappy(a)discussions.microsoft.com> wrote in message
news:A0575259-8886-42D7-AF5B-F68E508C8ECE(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!
>

 | 
Pages: 1
Prev: create a form that collects parameters
Next: Number Error Problem