I know this is simple but I can't find out how...

[Robert_L_Ross]

I have a form that I need to track if the data on the form has changed (so
when the user closes the form it can write the changed date/time to a field).

I have a sub that runs OnCurrent to 'take stock' of all of the data on the
form and write it to an unbound field. I then want to have another sub write
the data to a different unbound field when any field is changed.

I can't just have a flag run for the 'on change' event of every field
because the user could change a field, then change it back before saving the
record and would trick access into thinking they changed the record...I have
to actually detect changes at save.

So rather than write an 'OnChange' code for every field, I want to write
one, then have the 'OnChange' event of every field run that sub to update the
'post change' unbound field.

For the life of me I cannot find how to do this. How do you get one sub to
run another sub (in the same Module or a different one)??

Signed,
Pulling-what-little-hair-I-have-left-in-Sacramento

Thanks!!

 

[Klatuu]

Beware the evil Change event. It fires on every keystroke.

I would suggest you use the Before Update event of the form and check each
control's current value against the old value and if it has changed, do the
timestamp

If Me.SomeControl <> Me.SomeControl.OldValue Then
Call TimeStamp(Me.Name)
End If

etc.

Then create a sub or function that does the timestamp work for you. As in
the example, pass the name of the control so you can use it in your timestamp
code to know which control to timestamp for.

As to calling functions and subs, you can call any sub or function that is
in scope, even if it is an event procedure. Subs and Functions in standard
modules are visible every where in the application.

 

[Brian]

This would be much easier if you can use a bound form., because, you can just
use If Me.Dirty... on the Close event. Dirty means something has changed in
ANY bound control. Of course, the Close event automatically saves the record
anyway, but with the If Me.Dirty, you can do something like this:

If Me.Dirty then 'or could just enter the following two lines in
Form_BeforeUpdate
TimeChanged = Time()
DateChanged = Date()
End If

This assumes that you have DateChanged & TimeChanged as a field in the
form's underlying table and a (probably invisible) control of the same name.
I use a similar process in several apps to automatically record who made the
last change to the record. If the record is Dirty (or just before every
update), insert the current date/time/user into the appropriate
controls/fields.

If you really must use an unbound form, you can do this on the controls'
Change events:

Make a procedure called, for example, WhenSomethingChanges.

Private Sub WhenSomethingChanges()
'enter your events here
End Sub

Call that procedure from the change events:

Control1_Change() 'possible better done on Control1_AfterUpdate
WhenSomethingChanges 'this calls the other procedure
End Sub

 

[Brian]

One more thing...if you really need to compare the original value to a
changed, then changed back value (not via the Undo or ESC), you can use the
..OldValue property of the control thus:

If Control1 = Control1.OldValue then 'the value has not actually changed
'nothing actually changed
Else
'the value actually changed
End If

Same for each control. I think this requires bound form/controls. I cannot
remember if the .OldValue applies to code-populated unbound controls or not.

 

[Robert_L_Ross]

Thanks for the help, all of you!

I may not have made myself clear, but the FORM is bound, there are just some
unbound fields that I'm using to compare before-edit and after-edit values.

What I'm concerned about in using .oldvalue is let's say I go into the form,
change the first name field (bound) from Jim to James, hit tab a few times,
then for whatever reason tab or click back to the field and change it back
from James to Jim. Will that register using the .dirty or .oldvalue events?
I just want to make sure I don't accidentally push the modifiedon/modifiedby
fields (bound) when no change has actually been made.

 

[Brian]

The record will still be Dirty if a field is changed, then changed back,
unless it was changed back by the user pressing ESC or you have provided an
actual Undo method. So, to make sure something actually changed, you can
compare the .OldValue to the current value IF the record is dirty.

If Me.Dirty Then
If Control1 = Control1.OldValue then
'nothing actually changed, so don't update the date/time
Else
'the value actually changed; update date/time
End If
'repeat the .OldValue code for each control in question
End If

 

[Robert_L_Ross]

Dave,

I've been trying this and it doesn't work. Here's my code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Dirty Then
If Me.First_Name <> Me.First_Name.OLDVALUE Then
Call TimeStamp
End If
If Me.Last_Name <> Me.Last_Name.OLDVALUE Then
Call TimeStamp
End If
....
If Me.Notes <> Me.Notes.OLDVALUE Then
Call TimeStamp
End If
End If
End Sub

Function TimeStamp()
Me.Updated = Now()
End Function

I have a button to close the form:
Private Sub SaveChanges_Click()
DoCmd.Close acForm, "EditLenderContacts"
DoCmd.RepaintObject
End Sub

I can't figure out why this isn't working.

 

[Robert_L_Ross]

I'm testing the Note field...it saves the changed note value, closes the form
and updates the form it came from (repaint), but the Updated field doesn't
change.

 

[Robert_L_Ross]

Ok Update...

I've modified the code a bit:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Dirty Then
If Me.First_Name <> Me.First_Name.OLDVALUE Then
Me.Updated = Now()
End If
....
If Me.Notes <> Me.Notes.OLDVALUE Then
Me.Updated = Now()
End If
End If
End Sub

Private Sub SaveChanges_Click()
DoCmd.Close acForm, "EditLenderContacts"
DoCmd.RepaintObject
End Sub

When I click the X on the form, it doesn't work completely (updates the
bound forms, but doesn't change the Updated value). When I click the Close
button, it works.

How can I get this to work even when the user clicks the X?

 

[Robert_L_Ross]

OK, another correction, now it doesn't work at all (upates the bound field I
changed, but won't update the Updated field - worked once then stopped
working)!

 

[Robert_L_Ross]

Dave,

Ok, after playing around, this is what I have using BeforeUpdate:
If Me.Dirty = True Then
If Me.Notes = Me.Notes.OLDVALUE Then
msg = MsgBox("No change detected", vbOKOnly, "Text")
End If

If Me.Notes <> Me.Notes.OLDVALUE Then
msg = MsgBox("Change detected", vbOKOnly, "Text")
Me.Updated = Now()
End If
End If

Only problem is, .oldvalue doesn't seem to work if someone clears a field or
adds a record into a blank field. When I take a record with an empty 'Notes'
field and add a value, the form detects dirty but the .oldvalue doesn't kick
off. When I change the 'Notes' field from 'a' to 'b', both .dirty and
..oldvalue kick off. When I change 'Notes' from 'b' to null, only .oldvalue
kicks off.

I'm thinking of just putting two additional checks in place:
If Me.Notes <> Me.Notes.OLDVALUE Then Or (IsNull(Me.Notes) = True And
IsNull(Me.Notes.OLDVALUE) = False) Or (IsNull(Me.Notes) = False And
IsNull(Me.Notes.OLDVALUE) = True) Then ...

Is that the right solution, or is there something else I should look at with
..oldvalue?

Thanks!

 

[Dymondjack]

Perhaps an entirely different approach would work, rather than the Me.Dirty.
In a similar scenario, I have done the following, assuming you don't have a
ton a fields and are not extremely pressed for performance...

In the On_Current procedure, store the values of all fields of the current,
untouched record into privately declared variables. Ex:

In the form's module header:
'Declare "previous" field variables
Dim pvtField1 As Type
Dim pvtField2 As Type
'ect ect

And in the On_Current procedure...
pvtField1 = Nz(Me.ctlField1, "") 'For Strings
pvtField2 = Nz(Me.ctlField2, 0) 'For Numbers
'ect ect

And finally, on the After_Update event, do a comparison

For numbers:
If Me.ctlField1 <> pvtField1 Then
With rsChanges
.AddNew 'Store these in a revision table
.Fields(0) = Me.ctlField1.Name 'Store the Field Name
.Fields(1) = pvtField1 ' Store the previous value
.Fields(2) = Me.ctlField1 ' Store the after value
.Fields(3) = Now() ' Time of the record update
.Update
End With
End If

Or for strings
If Not (Me.ctlField1 = pvtField1) Then
ect. ect.


This is a lot more work that what you were trying to do with Me.Dirty, but I
like it because your code now has specific instructions to check each field
for a before and after value, you can store everything into a log table, and
you know that you are only getting the result you are looking for. Depending
on your requirements, and few custom subs or functions might be in order to
reduce any preformance decrease by making it more streamline. And the only
other thing is the handling of nulls. A little bit of working around can
take care of that depend on your needs.

Hope the idea helps some, and there's all sorts of different ways to go
about it using the same idea.

 

[Brian]

Yes. I neglected address nulls in my If Me.Dirty example. Sorry about that.

For text fields, you can combine the If IsNull and the comparison by using
NZ. NZ has the effect of changing any null value to something you specify.
That is, if abc = 1, then nz(abc,0) = 1, but if abc is null, then nz(abc,0) =
0. The same is true for strings if abc = "hello", then nz(abc,"") = "hello";
if abc is null, then nz(abc,"") = "" (an empty string, not null). You can
also use Else instead of two If's as well as omit the "msg = ". So, try this:

If Me.Dirty Then 'don't need the "= True" here
If nz(Me.Notes,"") = nz(Me.Notes.OLDVALUE,"") Then
MsgBox("No change detected", vbOKOnly, "Text")
Else
MsgBox("Change detected", vbOKOnly, "Text")
Me.Updated = Now()
End If
End If

For numeric fields, NZ will work for you IF you can replace the null with
some value that could never be a legitimate value; otherwise, you will need
to deal with the If IsNull, perhaps like this:

If IsNull(Box1) Then
If IsNull(Box1.OldValue) Then 'both are null
MsgBox("No change detected", vbOKOnly, "Text")
Else 'one is null; one is not, so we know it changed
MsgBox("Change detected", vbOKOnly, "Text")
Me.Updated = Now()
End If
Else
If IsNull(Box1.OldValue) Then 'one is null, one is not, so we know it
changed
MsgBox("Change detected", vbOKOnly, "Text")
Me.Updated = Now()
Else 'neither one is null; need to compare values
If Box1 <> Box1.OldValue Then
MsgBox("Change detected", vbOKOnly, "Text")
Me.Updated = Now()
Else
MsgBox("No change detected", vbOKOnly, "Text")
End If
End If
End If

 

[Brian]

I can't see why this part doesn't result in Me.Dirty being True:

"When I change 'Notes' from 'b' to null, only .oldvalue
kicks off."

If the Notes field had a previously-stored value, and someone clears it, the
form is still Dirty.

Looking at your code, I see that your .OldValue code is within the If
Me.Dirty Then...If loop, so I think it WAS actually finding the form Dirty in
order to get to the .OldValue code.

I have used .OldValue comparisons for this type of thing, but eventually, it
begged the question of whether I really needed to know if someone changed
something & then changed it back. Unless the users get paid per change and
they are prone to dummying up fake changes, it would seem to me that they
wouldn't be very motivated to change a value back, and I would be perfectly
happy knowing that someone made some sort of change, even if they went to all
the effort to manually change it back.

I simply record the date/time of the change on every save of the record, and
unless I save the record content history as an audit trail, I won't be able
to tell WHAT changed anyway - just when or by whom.

Now, if the users are changing things back because what they really need is
a way to undo a change before saving the record, then just make a button on
the form with Me.Undo as the VBA code on its click event.

 

[Robert_L_Ross]

This helps, but I still can't find a way to handle nulls.

My code:
Option Compare Database
Dim PreFirstName, PreLastName, PreTitle, PreInstitution, PreAddress,
PreCity, PreState, PreZip As String
Dim PrePortal, PreFDF, PreSLATE, PreManagement, PreAgreements, PreGeneral As
String
Dim PreEMail, PreTelephone, PreFax, PreCell, PreNotes As String
Dim PreExtension As Integer

Private Sub Form_Current()
PreFirstName = Nz(Me.First_Name, "")
PreLastName = Nz(Me.Last_Name, "")
....
PreNotes = Nz(Me.Notes, "")
End Sub

Private Sub Form_AfterUpdate()
Call CheckForChanges
End Sub

Private Sub CheckForChanges()
Dim VALIDATION As String
MESSAGE = MsgBox("Prior FN:" & PreFirstName & Chr(13) & Chr(10) & "Curr
FN:" & Me.First_Name, vbOKOnly, "TEST")
If Len(Me.First_Name) > 0 And Len(PreFirstName) > 0 Then
If (Not (Nz(Me.First_Name, "") = PreFirstName)) Or
(IsNull(Me.First_Name) = True And IsNull(PreFirstName) = False) Or
(IsNull(Me.First_Name) = False And IsNull(PreFirstName) = True) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = "First Name Changed"
End If
End If
End Sub

My problem is this - when I go from Null to populated, works fine and gives
me the message. When I go from populated "abc" to populated "def" works
fine. When I go from populated to Null, works fine. When I go from Null to
Null, it says something has changed.

I tried working without NZ() and by using NZ() on both the pre-change and
post-change values (as you can see above) and neither work.

What I'm trying to prevent is a user accidentally deleting a field and
saving without the system tracking it. This does that, but the next time you
open the record with a null field (let's say they deleted the phone number
the last time they opened the record) it thinks you've made a change and
writes the ModifiedBy and ModifiedOn values - even though you didn't make any
changes.

Any ideas what I'm missing?

 

[Robert_L_Ross]

I'm finding a world of hurt when it comes to null. I've set up my code to
add a note to a string for every field it checks.

I'm finding for example when the Cell field (text) is empty and I change the
Note field from null to something, the form thinks both Cell and Note
changed. When I change Cell from anything to anything and don't touch notes,
it again thinks both have changed.

Why we are doing this is because if someone deletes a field by accident and
puts the value back, the system should not indicate a change was made to the
record. We have to provide PAINSTAKINGLY DETAILED records back to our
auditors - we don't want the auditors looking at a record and seeing that we
indicate there was a change when there really wasn't a change made. It's not
that they get credit for every change - it's that our system will reflect
"Bob changed this record on the 1st" and when the auditors pull the record
from the 31st and compare it ot the 1st they'll see no changes.

You'll love this - let me post the new code I run right now:
Option Compare Database
Dim PreFirstName, PreLastName, PreTitle, PreInstitution, PreAddress,
PreCity, PreState, PreZip As String
Dim PrePortal, PreFDF, PreSLATE, PreManagement, PreAgreements, PreGeneral As
String
Dim PreEMail, PreTelephone, PreFax, PreCell, PreNotes As String
Dim PreExtension As Integer

Private Sub Form_AfterUpdate()
Call CheckForChanges
End Sub

Private Sub Form_Current()
PreFirstName = Nz(Me.First_Name, "")
PreLastName = Nz(Me.Last_Name, "")
PreTitle = Nz(Me.Title, "")
PreInstitution = Nz(Me.Institution, "")
PreAddress = Nz(Me.Address, "")
PreCity = Nz(Me.City, "")
PreState = Nz(Me.State, "")
PreZip = Nz(Me.Zip, "")
PrePortal = Nz(Me.Portal_Contact, "")
PreFDF = Nz(Me.FDF_Contact, "")
PreSLATE = Nz(Me.SLATE_Contact, "")
PreManagement = Nz(Me.Management, "")
PreAgreements = Nz(Me.Agreements, "")
PreGeneral = Nz(Me.General, "")
PreEMail = Nz(Me.E_mail_address, "")
PreTelephone = Nz(Me.Telephone, "")
PreExtension = Nz(Me.Extension, 0)
PreFax = Nz(Me.Fax, "")
PreCell = Nz(Me.Cell_Phone, "")
PreNotes = Nz(Me.Notes, "")
End Sub

Private Sub CheckForChanges()
Dim VALIDATION As String
If Not (Me.First_Name = PreFirstName) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = "FIRST NAME CHANGED"
End If
If IsNull(PreFirstName) = False And IsNull(Me.First_Name) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = "FIRST NAME CHANGED"
End If

If Not (Me.Last_Name = PreLastName) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "LAST NAME CHANGED"
End If
If IsNull(PreLastName) = False And IsNull(Me.Last_Name) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "LAST NAME CHANGED"
End If

If Not (Me.Title = PreTitle) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "TITLE CHANGED"
End If
If IsNull(PreTitle) = False And IsNull(Me.Title) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "TITLE CHANGED"
End If

If Not (Me.Institution = PreInstitution) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "INSTITUTION CHANGED"
End If
If IsNull(PreInstitution) = False And IsNull(Me.Institution) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "INSTITUTION CHANGED"
End If

If Not (Me.Address = PreAddress) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "ADDRESS CHANGED"
End If
If IsNull(PreAddress) = False And IsNull(Me.Address) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "ADDRESS CHANGED"
End If

If Not (Me.City = PreCity) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "CITY CHANGED"
End If
If IsNull(PreCity) = False And IsNull(Me.City) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "CITY CHANGED"
End If

If Not (Me.State = PreState) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "STATE CHANGED"
End If
If IsNull(PreState) = False And IsNull(Me.State) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "STATE CHANGED"
End If

If Not (Me.Zip = PreZip) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "ZIP CHANGED"
End If
If IsNull(PreZip) = False And IsNull(Me.Zip) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "ZIP CHANGED"
End If

If Not (Me.Portal_Contact = PrePortal) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "PORTAL CHANGED"
End If

If Not (Me.FDF_Contact = PreFDF) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "FDF CHANGED"
End If

If Not (Me.SLATE_Contact = PreSLATE) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "SLATE CHANGED"
End If

If Not (Me.Management = PreManagement) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "MGT CHANGED"
End If

If Not (Me.Agreements = PreAgreements) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "AGREEMENT CHANGED"
End If

If Not (Me.General = PreGeneral) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "GENERAL CHANGED"
End If

If Not (Me.E_mail_address = PreEMail) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "E-MAIL CHANGED"
End If
If IsNull(PreEMail) = False And IsNull(Me.E_mail_address) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "E-MAIL CHANGED"
End If

If Not (Me.Telephone = PreTelephone) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "TEL CHANGED"
End If
If IsNull(PreTelephone) = False And IsNull(Me.Telephone) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "TEL CHANGED"
End If

If Me.Extension <> PreExtension Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "EXT CHANGED"
End If

If Not (Me.Fax = PreFax) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "FAX CHANGED"
End If
If IsNull(PreFax) = False And IsNull(Me.Fax) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "FAX CHANGED"
End If

'Begin Cell Phone Check Code
MESSAGE = MsgBox("Pre :*" & Me.Cell_Phone.OLDVALUE & "*" & _
Chr(13) & Chr(10) & _
"Post:*" & Me.Cell_Phone & "*" & _
Chr(13) & Chr(10) & _
"Len Pre :" & Len(Me.Cell_Phone.OLDVALUE) & _
Chr(13) & Chr(10) & _
"Len Post:" & Len(Me.Cell_Phone), vbOKOnly, "Cell Value")

If Not (Me.Cell_Phone = PreCell) Then
End If

If Not (Me.Cell_Phone = PreCell) Then
Me.ModifiedBy = CurrentUser()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "CELL CHANGED"
Me.ModifiedOn = Now()
End If
If IsNull(PreCell) = False And IsNull(Me.Cell_Phone) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "CELL CHANGED"
End If

If Not (Me.Notes = PreNotes) Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "NOTES CHANGED"
End If
If IsNull(PreNotes) = False And IsNull(Me.Notes) = True Then
Me.ModifiedBy = CurrentUser()
Me.ModifiedOn = Now()
VALIDATION = VALIDATION & Chr(13) & Chr(10) & "NOTES CHANGED"
End If
MESSAGE = MsgBox(VALIDATION, vbOKOnly, "CHANGES DETECTED")
End Sub

Private Sub SaveChanges_Click()
DoCmd.Save
DoCmd.Close
End Sub

Now, when I change the Note field, it thinks both Note and Cell have
changed. When I change Cell it again thinks both Note and Cell have changed.

When I change the First Name, it thinks both First Name and Note has changed
(but not Cell).

When I exchange Me.Cell_Phone.oldvalue for PreCell and change Notes from
null to ABC while Cell had a value in it, works fine. When I then delete the
value in Cell, it returns that both Cell and Notes have changed.

Enough to make me old before my time! I've basically proven with this that
..oldvalue does NOT work reliably. I've also proven that taking the value
pre-change and trying to compare it post-change also doesn't work because
Access is somehow setting changes to fields where the cursor has never been.

 

[Brian]

Hi, Robert.

I posted an expanded bit of code yesterday, but lost my connection just as I
was posting, so here it is again, approximately. I apologize that I did not
address nulls in my first post. I must remember to copy my posts so I can
re-paste if the connection is dropped.

If Me.Dirty Then 'something changed
If IsNull(Notes) Then
If IsNull(Notes.OldValue) Then 'both are null; no change
MsgBox("No change detected", vbOKOnly, "Text")
Else 'one is null; one is not; that is a change
MsgBox("Change detected", vbOKOnly, "Text")
Updated = Now()
End If
Else
If IsNull(Notes.OldValue) Then 'one is null; one is not
MsgBox("Change detected", vbOKOnly, "Text")
Updated = Now()
Else 'neither is null; we need to compare values
If Notes = Notes.OldValue Then 'no change
MsgBox("No change detected", vbOKOnly, "Text")
Else
MsgBox("Change detected", vbOKOnly, "Text")
Updated = Now()
End If
End If
End If
End If

 

[Brian]

Oh, now I see that my prior code DID get posted up in the middle of the
thread. Oh, well, I guess you get it twice. Try that, but also think about my
other note there: unless the users are getting paid a commission on every
change they make (in which case they might be motivated to make a change,
then change it back), perhaps it would be enough to simply record the changes
any time they change something, even if they change it back before they leave
the form or save the record. For me, it is enough to know that someone was in
there trying to change something.

Form_BeforeUpdate
Updated = Now()
End Sub

The form updates only after something has changed (although, as you
indicate, it will update if the user, for some reason, enters something, then
deletes it BEFORE closing the form or saving the record), and this is much
simpler. Besides, unless you track the content of each change as an audit
trail, all you are doing is saving the date/time/user of the most recent
change - not an indication of what particular field was changed.

 

[Dymondjack]

My personal preference for this would be to set all the values to thier
default (assuming you dont have a specific use for a null value). in the
OnCurrent event, before you set your 'previous' values, change any null
values to "" or 0. The set your 'previous' values, and check them after.

Of course, if this is a new record, it would cause that record to be saved,
which might not be desirable. For this, a module scope pvt_blnDeleteRecord
could be used to check if there is any information in the fields and decide
if it is actually a legit record. Save the active record's ID in a tblSys
(allen browne is where I got the tblSys idea, but I dont have the link
handy), and in the form's Unload event, run a quick function to find like
ID's in that table and delete them. This will keep unwanted records from
populating your form's table. Just be sure to empy the tblSys's data for the
ID's to delete, otherwise it would happen every time.


This may be way more complicated than nescessary (I'm hardly an MVP), but I
think it should work well.

 

[yzarcman]

I am also working on a project where this type of detection is
necessary.

It involves communicating to the customer any time certain fields are
changed, so if someone does go in and make a change and then changes
back we would really like to spare the expense of contacting the
customer. If the field happened to be changed by changing the case of
the data the intrinsic data did not change and therefore the customer
doesn't need to be contacted.

Please post the solution you succeeded with so that future googlers
can see the answer.

Thanks,
Greg