If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is this possible?
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? |
#2
|
|||
|
|||
Is this possible?
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "martinmike2" wrote in message ... 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? |
#3
|
|||
|
|||
Is this possible?
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 '================================================= === martinmike2 wrote: 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? |
#4
|
|||
|
|||
Is this possible?
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! "martinmike2" wrote in message ... 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? |
#5
|
|||
|
|||
Is this possible?
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- -- "Ted" wrote in message ... 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! "martinmike2" wrote in message ... 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? |
#6
|
|||
|
|||
Is this possible?
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. "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? |
#7
|
|||
|
|||
Is this possible?
Not known.
Oct 27 2008 "martinmike2" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|