Tracking changes made to a field on a form
in [Forms]
|
Prev: The data has been changed message
Next: choose function
From: Yappy on 19 May 2010 10:07 I have followed the steps described on Microsoft Online to record the date and time when a record is modified and added these fields to my form. Is there a way to drill this process down to when a field has been modified and indicate which field(s) was/were modified? If so, I would need step by step direction for creating the code or information where I can find it. I am using Access 2003. Thank you!
From: Jeff Boyce on 19 May 2010 11:38 A couple different interpretations of your question ... "track when the field definition was modified" -- hard! "track when the field value was modified" -- still hard, but not as much. Check Allen Browne's website for an approach to keeping "audit" information... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Yappy" <Yappy(a)discussions.microsoft.com> wrote in message news:DEFAF6F0-18EF-485B-8357-8B617252250A(a)microsoft.com... >I have followed the steps described on Microsoft Online to record the date > and time when a record is modified and added these fields to my form. > > Is there a way to drill this process down to when a field has been > modified > and indicate which field(s) was/were modified? If so, I would need step > by > step direction for creating the code or information where I can find it. > > I am using Access 2003. > > Thank you!
From: Arvin Meyer [MVP] on 19 May 2010 11:50 "Yappy" <Yappy(a)discussions.microsoft.com> wrote in message news:DEFAF6F0-18EF-485B-8357-8B617252250A(a)microsoft.com... >I have followed the steps described on Microsoft Online to record the date > and time when a record is modified and added these fields to my form. > > Is there a way to drill this process down to when a field has been > modified > and indicate which field(s) was/were modified? If so, I would need step > by > step direction for creating the code or information where I can find it. > > I am using Access 2003. Yes, Add code to the before update event It makes sense to add a separate table which stores, the OldValue, and the date/time of change, that way you can keep track of multiple changes. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strSQL As String Dim lngCount As Long Dim lngItemID As Long Dim strContractorID As String Dim strSubdivision As String Dim lngModelID As Long Dim dblCost As Double Dim dblCostCode As Double Dim dtmEffectiveDate As Date Dim db As DAO.Database Dim rstItems As DAO.Recordset lngItemID = Me.txtItemID.OldValue strContractorID = Me.txtContractorID.OldValue strSubdivision = Me.txtSubdivision.OldValue lngModelID = Me.txtModelID.OldValue dblCost = Me.txtCost.OldValue dblCostCode = Me.txtCostCode.OldValue dtmEffectiveDate = Me.txtEffectiveDate.OldValue Set db = CurrentDb strSQL = "INSERT INTO tblItemHistory ( ItemID, Subdivision, ModelID, CostCode, Cost, ContractorID, EffectiveDate )" strSQL = strSQL & " VALUES (" & rstItems!ItemID & ", '" & strSubdivision & "', " & lngModelID & ", " & dblCostCode & ", " & rstItems!Cost & ", '" & rstItems!ContractorID & "', '" & rstItems!EffectiveDate & "');" db.Execute strSQL End Sub To explain the code briefly, I've dim'd a variable for each value on the form, and stored the OldValue of each textbox in it, then I've written that data to a history table. You'll need to add error handling, as I stripped this out of a much more complex piece of code. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access
From: Dorian on 19 May 2010 11:55 It depends what you mean by modified. How will you cater for when someone changes a field from A to B and then back to A? In the field after update event, you can compare the current value with the oldvalue. But then in form before update event the update could be cancelled, so you probably need to do it in the form before update event for every field on the form. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Yappy" wrote: > I have followed the steps described on Microsoft Online to record the date > and time when a record is modified and added these fields to my form. > > Is there a way to drill this process down to when a field has been modified > and indicate which field(s) was/were modified? If so, I would need step by > step direction for creating the code or information where I can find it. > > I am using Access 2003. > > Thank you!
From: Yappy on 20 May 2010 08:04
Hi, Dorian! Modified = a data entry change in the field I am trying to get to the end result which is generating a report showing all new records added and any data entry changes to certain fields in existing records. "Dorian" wrote: > It depends what you mean by modified. How will you cater for when someone > changes a field from A to B and then back to A? > In the field after update event, you can compare the current value with the > oldvalue. But then in form before update event the update could be cancelled, > so you probably need to do it in the form before update event for every field > on the form. > -- Dorian > "Give someone a fish and they eat for a day; teach someone to fish and they > eat for a lifetime". > > > "Yappy" wrote: > > > I have followed the steps described on Microsoft Online to record the date > > and time when a record is modified and added these fields to my form. > > > > Is there a way to drill this process down to when a field has been modified > > and indicate which field(s) was/were modified? If so, I would need step by > > step direction for creating the code or information where I can find it. > > > > I am using Access 2003. > > > > Thank you! |