Recording old value after a change "mcarlyle via AccessMonster.com" <u6592[ at ]uwe> 12/9/2008 9:03:12 PM Ok I can do this at any point but want to minimize the number of stored records in the notes field. I have a user driven db that uses logins to track a lot of what goes on. I am trying to track if a user changes a record from one person's ownership to anothers. Currently I have this code. Private Sub Salesperson_AfterUpdate() origionalvalue = Forms!Active_Leads!Salesperson.OldValue DoCmd.SetWarnings False DoCmd.RunSQL "INSERT INTO notes ( [notes], [id], [rep]) VALUES ('Rep changed from ' & origionalvalue & ' to ' & [Salesperson] & ' by ' & [CurrentUser],[ID],currentuser())" DoCmd.SetWarnings True End Sub The problem is my code is wrong on the collection of the oldvalue. first the form is a subform of another form called managerbar and for some reason I can't remember how to recurs down to the sub form. If I remove the origionalvalue portion, the code records the new number, but I need it to also record the oldvalue. Thanks in advance for any help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1

Re: Recording old value after a change "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> 12/9/2008 9:34:58 PM Because the control you are referring to (Salesperson) is in the same form as the code you are running, it it in the local scope, so you don't need the full reference. Just use: origionalvalue = Me!Salesperson.OldValue Note also that if the user changes the Salesperson several times before saving the record, you will get multiple records added to your notes table. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "mcarlyle via AccessMonster.com" <u6592[ at ]uwe> wrote in message news:8e6bbbd6bdccf[ at ]uwe... [Quoted Text] > Ok I can do this at any point but want to minimize the number of stored > records in the notes field. > > I have a user driven db that uses logins to track a lot of what goes on. > I > am trying to track if a user changes a record from one person's ownership > to > anothers. Currently I have this code. > > Private Sub Salesperson_AfterUpdate() > origionalvalue = Forms!Active_Leads!Salesperson.OldValue > DoCmd.SetWarnings False > DoCmd.RunSQL "INSERT INTO notes ( [notes], [id], [rep]) VALUES ('Rep > changed from ' & origionalvalue & ' to ' & [Salesperson] & ' by ' & > [CurrentUser],[ID],currentuser())" > DoCmd.SetWarnings True > End Sub > > The problem is my code is wrong on the collection of the oldvalue. first > the > form is a subform of another form called managerbar and for some reason I > can't remember how to recurs down to the sub form. > > If I remove the origionalvalue portion, the code records the new number, > but > I need it to also record the oldvalue. > > Thanks in advance for any help. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1 >