Is this possible?

[martinmike2]

Hello,

I have been asked to create a report that shows items that have been
changed after running an update query. What my higher-ups would like
is a report that would show the old value of the field and the new
value of the field. Do tables have a .OldValue property like forms?
If so, how would I access that information to fill the report?

 

[Allen Browne]

No. Access (JET) tables do not provide triggers or logging, so there is no
record of what was changed by an action query.

It might be possible to simulate it by executing another INSERT query before
the main one, to log the records that will be changed.

 

[John Spencer]

No, there is no old value property for fields in tables.

You would have to store the old information in some form to have it
available.

MVP Allen Browne has an Audit Trail example that logs changes to the
data - but it only captures changes done through data entry on forms.
You could use the Audit Trail table to get the old values.

Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

Basically, it can be done, but it will be a lot of work to do so. One
option might be to copy the records to be updated to a "work" table, do
the update, and then link the table you have changed to the "work" table
to generate your report. You would have to control cleaning out the
"work" table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Ted]

Are you storing the old values in the table? I had to do that for one for
one of my dbs also. I added the "old" fields to my table
and added code to the form to update those with the old value before the
values were changed. That way I could run the report a
ny time showing what the values were and what they are now. You can also
create a database somewhere else on your server and write the new values and
old values to it.

For example...

I use the following code in the AfterUpdate event for the fields I need to
know the old value for.

Dim db As Database
Dim audit As Recordset
Dim temp As Integer

Set db = OpenDatabase("L:\Claims\audit.accdb")
Set audit = db.OpenRecordset("YourAuditTableName")
temp = WriteAuditTrail(audit, Field.NAME)





Function WriteAuditTrail(rst As Recordset, fn As String) As Integer
On Error GoTo ErrorHandler
' Edit the current record in the recordset.
If Me(fn).OldValue() <> "" Then
rst.AddNew
rst!UserModified = CurrentUser()
rst!ClaimNumber = Forms![frmRCAClaims]![1#_CLM#]
rst!FormModified = Me.NAME
rst!fieldmodified = fn
rst!DateTimeModified = Now
rst!OldValue = Me(fn).OldValue()
rst!NewValue = Me(fn)
rst.Update
Me.Refresh
End If
ErrorHandler:
Select Case Err
Case 0
' conSuccess is defined at the module level as a public constant
' of type Integer with a value of zero.
WriteAuditTrail = conSuccess
Exit Function
Case Else
MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
WriteAuditTrail = Err
Exit Function
End Select

End Function

Hope that helps...good luck!

 

[Larry Daugherty]

OP may have already solved his issue but there may be more things to
consider: If management currently wants to know the specifics of the
last prior state, what's to say that they couldn't later want to know
the state before that - or even every state back to the beginning of
time ( or the application...)?

I suggest that a better solution might be to treat this as a
relational thing and just create a related table with all of the data
in question in the related table. Use the TOP n predicate to return
the desired depth for the current report.

HTH
--
-Larry-
--

Are you storing the old values in the table? I had to do that for one for
one of my dbs also. I added the "old" fields to my table
and added code to the form to update those with the old value before the
values were changed. That way I could run the report a
ny time showing what the values were and what they are now. You can also
create a database somewhere else on your server and write the new values and
old values to it.

For example...

I use the following code in the AfterUpdate event for the fields I need to
know the old value for.

Dim db As Database
Dim audit As Recordset
Dim temp As Integer

Set db = OpenDatabase("L:\Claims\audit.accdb")
Set audit = db.OpenRecordset("YourAuditTableName")
temp = WriteAuditTrail(audit, Field.NAME)





Function WriteAuditTrail(rst As Recordset, fn As String) As Integer
On Error GoTo ErrorHandler
' Edit the current record in the recordset.
If Me(fn).OldValue() <> "" Then
rst.AddNew
rst!UserModified = CurrentUser()
rst!ClaimNumber = Forms![frmRCAClaims]![1#_CLM#]
rst!FormModified = Me.NAME
rst!fieldmodified = fn
rst!DateTimeModified = Now
rst!OldValue = Me(fn).OldValue()
rst!NewValue = Me(fn)
rst.Update
Me.Refresh
End If
ErrorHandler:
Select Case Err
Case 0
' conSuccess is defined at the module level as a public constant
' of type Integer with a value of zero.
WriteAuditTrail = conSuccess
Exit Function
Case Else
MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
WriteAuditTrail = Err
Exit Function
End Select

End Function

Hope that helps...good luck!

news:395dfcdf-aaa9-44e8-80a8-77b0847ec95e@y21g2000hsf.googlegroups.com...

Hello,

I have been asked to create a report that shows items that have been
changed after running an update query. What my higher-ups would like
is a report that would show the old value of the field and the new
value of the field. Do tables have a .OldValue property like forms?
If so, how would I access that information to fill the report?

 

[twj]

From your e-mail, I think you are a engineer. As we all know nothing is
impossible, but it is just a old saying, never mind ,be happy everyday.

 

[Storm Express]

Not known.

Oct 27 2008