Prev: 2 list box iteration
Next: looping thru a continous form
From: Yappy on 4 Jun 2010 11:52
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!

"Douglas J. Steele" wrote:

> 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: Yappy on 4 Jun 2010 11:52
Tom,
Thanks for your reply. Unfortunately your suggestions did not resolve my
issues.

"Tom van Stiphout" wrote:

> 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 12:29
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.

--
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:21C502ED-566E-4120-8850-65464A56D55F(a)microsoft.com...
> 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!
>
> "Douglas J. Steele" wrote:
>
>> 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: nrgins on 5 Jun 2010 06:29
Yappy:

You need to post the function that's calling LogChanges, so we can see what's
going on.

Also, set a breakpoint to the "varOld =" line by pressing F9 on that line.
When the breakpoint is activated, go the debug window (Ctrl+G) and type:

? Screen.ActiveControl.Name

and press Enter. Tell us when it shows.

Neil

 | 
Pages: 1
Prev: 2 list box iteration
Next: looping thru a continous form