tracking changes

  • Thread starter Thread starter tina
  • Start date Start date
T

tina

-----Original Message-----
I built a very simple database for my job. I use one form
to enter info, and another form to send info out. Is there
a way I can look up when a particular record was changed?
I am looking for a way to trace when exactly a particular
field value was entered and/or changed?
Any help would be very welcome.
Matthew
.
Click to expand...

try this:

create a table, as

tblHistory
HistID (autonumber, increment, set as primary key)
Hist_DataID (change this to the name of the primary key
field in your data table; make sure it's the same data
type, and make sure it allows duplicates)
HistOldValue (make sure it's the same data type as the
field you want to track in your data table)
HistStamp (make this a date/time field and set the
default value to Now())

in the form where you add/change records in your data
table, add the following code

Dim strOldValue As String, booContinue As Boolean
' the variables above are declared at module level.

Private Sub Form_AfterUpdate()

If strOldValue = Me!DataText Or booContinue = False
Then
Exit Sub
Else
AddHistory
End If

End Sub

Private Sub AddHistory()

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblHistory ( Hist_DataID,
HistOldValue ) SELECT " _
& Me!DataID & ", '" & strOldValue & "'", False
DoCmd.SetWarnings True

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

booContinue = True

If IsNull(Me!DataText.OldValue) And IsNull(Me!
DataText) Then
booContinue = False
ElseIf IsNull(Me!DataText.OldValue) Then
strOldValue = "Added value"
Else
strOldValue = Me!DataText.OldValue
End If

End Sub

go thru the code and change the Me!DataText reference to
the controlname of the field you want to track changes on.

this code will track each time a value is added to the
specified field, or changed in the field, in a specific
record. it will also track when a new record is added,
UNLESS that field is left blank in the new record. in each
instance, the OldValue field in tblHistory shows what the
data was BEFORE it was changed. so you have a history
of "what the data was changed from" in a specific record
in additon to "when the data was changed".

hth
 
tina said:
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblHistory ( Hist_DataID,
HistOldValue ) SELECT " _
& Me!DataID & ", '" & strOldValue & "'", False
DoCmd.SetWarnings True
Click to expand...

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise wierd
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Back
Top