Tracking changes made to a field on a form
in [Forms]
|
Prev: The data has been changed message
Next: choose function
From: Yappy on 20 May 2010 08:08 Hi, Jeff! Sorry about the confusion. The correct interpretation is track when the field value was modified. Thanks for the suggestion! I will definitely check it out. My goal is to generate a report that shows all new records added and any changes that were made to field values on certain fields. I am perplexed as to how to get to the results. "Jeff Boyce" wrote: > 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: Yappy on 20 May 2010 08:21 Thanks, Arvin! The code that I added to record the date and time when a record is modified was added to the BeforeUpdate event. My goal is to generate a report that includes Field X, Y, Z for all new records added and any records where the field value for Field X, Y, Z has changed. The query and report are already set up for new records added. I guess the best way is to follow your suggestion on adding a new table for history. Can I then compare the old with the new to extract the new information for the report? "Arvin Meyer [MVP]" wrote: > "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: Yappy on 20 May 2010 08:49 Hi, again! I checked out Allen Browne's website. I am unable to use his approach because the tables used must have an autonumber for the primary key. The primary key that I have set for my table must remain because it is used to identify the record and key to preventing duplications and errors in data entry. If there is a work-around, I am open to suggestions. "Jeff Boyce" wrote: > 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: Jeff Boyce on 20 May 2010 12:20 Please note the following difference ... MyTable MyTableID (an autonumber) MySpecialUniqueIdentifier (your record identifier) blah blah blah You can ADD an autonumber field, make it the primary key, and keep your unique identifier. .... and are you sure you can't adapt how Allen's done his audit routine to USE your unique identifier? 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:E1D689A3-94DD-4A41-9A09-1B75274C0B43(a)microsoft.com... > Hi, again! I checked out Allen Browne's website. I am unable to use his > approach because the tables used must have an autonumber for the primary > key. > The primary key that I have set for my table must remain because it is > used > to identify the record and key to preventing duplications and errors in > data > entry. If there is a work-around, I am open to suggestions. > > "Jeff Boyce" wrote: > >> 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 21 May 2010 08:08
Yes. You can compare the old with the new. It is very easy to compare with the last record put in history, but a just bit harder to pick a record from a previous period. For that you first need to run a query to get the exact date/time you are looking for, then use that date/time to pull the record that you will use in the comparison. I was using a system similar to that to compare building costs over a period of 5 years. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access Co-author: "Access 2010 Solutions", published by Wiley "Yappy" <Yappy(a)discussions.microsoft.com> wrote in message news:EAD863C9-8CB7-452D-9B61-8C32ECDFFD40(a)microsoft.com... > Thanks, Arvin! > > The code that I added to record the date and time when a record is > modified > was added to the BeforeUpdate event. > > My goal is to generate a report that includes Field X, Y, Z for all new > records added and any records where the field value for Field X, Y, Z has > changed. The query and report are already set up for new records added. > I > guess the best way is to follow your suggestion on adding a new table for > history. Can I then compare the old with the new to extract the new > information for the report? > > > "Arvin Meyer [MVP]" wrote: > >> "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 >> >> >> . >> |