Updated or dirtied doesn't necessarily mean the data will have been changed
of course as a user might change a value, then change it back to the original
value before the record is saved. To record the date of an actual change to
the data put the following module in the database:
''''module starts''''
' module basChangedRecord
' determines if data in a record edited
' in a form has actually been changed
Option Compare Database
Option Explicit
' arrays for storing values from recordsets
Dim aOldVals(), aNewVals()
Public Sub StoreOldVals(rst As DAO.Recordset)
' store values of current row in array
aOldVals = rst.GetRows()
End Sub
Public Sub StoreNewVals(rst As DAO.Recordset)
' store values of edited row in array
aNewVals = rst.GetRows()
End Sub
Public Function RecordHasChanged() As Boolean
Dim n As Integer, intlast As Integer
Dim var As Variant
Dim aOld(), aNew()
intlast = UBound(aOldVals) - 1
' loop through array of original values
' and store in new array
For Each var In aOldVals()
ReDim Preserve aOld(n)
aOld(n) = var
n = n + 1
Next var
n = 0
' loop through array of edited values
' and store in new array
For Each var In aNewVals()
ReDim Preserve aNew(n)
aNew(n) = var
' if any value has changed then return True
If Nz(aNew(n), 0) <> Nz(aOld(n), 0) Then
RecordHasChanged = True
Exit For
End If
n = n + 1
Next var
End Function
''''module ends''''
And call the functions in a form's module as follows, taken from a form
based on an Addresses table with an AddressID primary key:
Private Sub Form_Current()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String
If Not Me.NewRecord Then
strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
StoreOldVals rst
End If
End Sub
Private Sub Form_AfterUpdate()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If Nz(Me.DateUpdated) <> VBA.Date Then
StoreNewVals rst
If RecordHasChanged() Then
With rst
.MoveFirst
.Edit
.Fields("DateUpdated") = VBA.Date
.Update
End With
Me.Refresh
End If
End If
End Sub
Ken Sheridan
Stafford, England
Ed Robichaud said:
One of the better solutions is to add two date/time fields to your tables.
One[Created] defaulted to Now(), the second one [LastUpdate] controlled by
the BeforeUpdate or OnDirty event of the bound data entry form.
Darrell said:
Can I add a field to a query that shows the date a record was created on?
I
want to run this query every day so I need to know which records where
created on which date? How do I add this field to a query table, or do I
have
to create a seperate table and then join then to bring the record date
over?
wishing I understood everything I know about Access <grin>
Darrell
