Recording old value after a change

[mcarlyle via AccessMonster.com]

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.

 

[Graham Mandeno]

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.