[AccessD] Tracking real edits

MarkH lists at theopg.com
Tue Jan 11 10:06:19 CST 2005


John

Did you try loading the recordsetclone into another recordset on_current
and then use that instead of the recordset clone?

	Set rsOld=me.recordsetclone' at start (current)
	Set rsNew=me.recordsetclone' at save / exit

Then cycle the recordset something like... (watch for typos as this
wasn't done in a vba editor

	for i=0 to rsOld.fields.count-1
		if rsOld(i)<>rsNew(i) then
			debug.print rsOld(i).name & ": OldValue=" &
rsOld(i) & " new Value=" & rsNew(i)
		end if
	next

This should work fine with bound forms... In principal (I reckon -
haven't tried it yet)

Mark

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: 11 January 2005 15:48
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Tracking real edits


Gustav,

I just tried than and it doesn't appear to work.  It appears that the
original RecordsetClone is a live dataset, not a snapshot.  Even at
BeforeUpdate update, the changes to the recordset filter down to the
recordsetclone.  Further I cannot use the .OriginalValue property, I get
an error "doesn't support this ...".  

I am using the following code to look at and test this:

Private Function DisplayRecordData(rst As DAO.Recordset)
On Error GoTo Err_DisplayRecordData
Dim fld As DAO.Field
Dim strData As String
Dim varValOrig As Variant

    For Each fld In rst.Fields
        With fld
            
            strData = strData & .SourceTable & "." & .SourceField & ":"
& .Value
            On Error Resume Next
            varValOrig = fld.OriginalValue
            If Err = 0 Then
                strData = strData & ":" & fld.OriginalValue & "; "
            Else
                Err.Clear
On Error GoTo Err_DisplayRecordData
                strData = strData & "; "
            End If
        End With
    Next fld
    Debug.Print strData

Exit_DisplayRecordData:
Exit Function
Err_DisplayRecordData:
        MsgBox Err.Description, , "Error in Function
clsAudTrailRstMethod.DisplayRecordData"
        Resume Exit_DisplayRecordData
    Resume 0    '.FOR TROUBLESHOOTING
End Function

The code simply cycles through all of the fields and builds up a string
of the SourceTable name, the SourceFieldName and the value.  The line
immediately following OnError resume next allways throws an error,
whether editing the record or not.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, January 11, 2005 4:07 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Tracking real edits


Hi Mark & John

Wouldn't it be easier to create a RecordsetClone at the OnCurrent event,
then at BeforeUpdate compare all fields of the current record with the
clone and cancel if they match?

/gustav

>>> lists at theopg.com 11-01-2005 00:37:38 >>>
John

Dunno if either of these would be appropriate... In some cases I store
all the values in a two simple delimited strings, create a function
which concatenates all the values and call it first on form load (or
current) to populate oldString and then on save / exit (or whatever) to
populate newString and then compare the two... It works pretty good but
could (possibly?) be problematic where the contents of the form include
memo fields. I have also done the same with arrays (containing
controlName and controlValue), one for the old values and one for the
new then loop through doing comparisons and whatever actions
appropriate. Do the comparisons on save / exit as appropriate and stuff
the access update events :@) 

Both methods work pretty good in the right place... Generally, where
control specific audits have been required I found it less problematic
to use unbound forms... In fact, recently I have wanted to kick myself
for using bound forms at all coz it's such a pain.

Hth

Mark

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: 10 January 2005 05:42
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Tracking real edits


I could do that.  If it comes down to that I am examining a class for
each control being tracked, with an Old/Newvalue property.  If I want to
use WithEvents to trigger the save of the data only when there is a
modification to the control's data, then I have to have a class for each
control type being tracked since a control object type cannot source
events (it must be a specific type - combo, text etc).  Doing it that
way allows it to work for A2K (or even A97) as well as WinXP and beyond.
I have toyed with that idea for logging the actual data changes which is
a subject that comes up often on this list.  

I was really hoping to just use built in events to tell me that data
changed, and if the data changes were undone.  Sometimes Access can be
so frustrating.  Something like this should just already be there in the
control class.  If the undo events triggered correctly and reliably I
would be done right now.  Each control MUST know that it's data changes
are undone, why doesn't it fire the event each and every time?

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/ 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Sunday, January 09, 2005 11:43 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Tracking real edits


Hi John:

Would it work if you took control yourself but simply saving the initial
data settings, of the current record....then comparing for any
differences. I use a similar technical, storing all the fields in a
matching TYPE record and compare when moving or exiting. (It an old
unbound habit....)

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Sunday, January 09, 2005 6:14 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Tracking real edits

I have wondered for a long time how to track real edits.  Issues:

User starts to edit a field, hits the escape key and undoes edit.  The
AfterUpdate of a given control tells me the user started an edit.  In
Access XP the Escape causes OnUndo to fire, I can determine that the
undo happened. Notice that A2K and previous do not even have OnUndo.

However, if the user edits a control and leaves that control, then the
AfterUpdate fires, I know the control was edited, but if they then hit
Escape, the control edit is undone, but OnUndo DOES NOT FIRE, thus I
incorrectly think that the control is still edited.

My client wants to create date stamp fields for groups of fields, i.e. a
NameDateStamp, AddressDateStamp, PhoneDateStamp etc.  Thus I need to
know that an edit happened and that the edit actually stored.  I've
never really figured out a satisfactory way to do this.  Does anyone
have a method that they use that appears to correctly handle all the
nefarious things that a user can do?

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.832 / Virus Database: 566 - Release Date: 10/01/2005
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.832 / Virus Database: 566 - Release Date: 10/01/2005
 




More information about the AccessD mailing list