Update field

[Annette]

I have two fields in a table - WorkerID and OriginalWorkerID. The form
that displays this information only shows WorkerID. When the record is
first created, the user will enter a WorkerID. I would like the value
of OriginalWorkerID to be assigned to what was entered in WorkerID.
Then if WorkerID gets CHANGED, I want OriginalWorkerID to stay the
same as it was first entered.

For example on 01/01/2010 the record was created and assigned to
WorkerID 1. WorkerID and OriginalWorkerID would both have values of 1.
Then on 01/05/2010 the WorkerID field was changed to 17, I want the
OriginalWorkerID to stay at 1. This way I can run a report that shows
me how many entries had the WorkerID changed from when the record was
first created.

Any ideas or suggestions on doing it differently?

 

[John W. Vinson]

I have two fields in a table - WorkerID and OriginalWorkerID. The form
that displays this information only shows WorkerID. When the record is
first created, the user will enter a WorkerID. I would like the value
of OriginalWorkerID to be assigned to what was entered in WorkerID.
Then if WorkerID gets CHANGED, I want OriginalWorkerID to stay the
same as it was first entered.

For example on 01/01/2010 the record was created and assigned to
WorkerID 1. WorkerID and OriginalWorkerID would both have values of 1.
Then on 01/05/2010 the WorkerID field was changed to 17, I want the
OriginalWorkerID to stay at 1. This way I can run a report that shows
me how many entries had the WorkerID changed from when the record was
first created.

Any ideas or suggestions on doing it differently?

What if it gets changed a second time? Or a third or a fourth? Do you want to
track only the current and the original worker?

You can certainly set OriginalWorkerID to the value of WorkerID if you update
the record on a Form: I'd put code in the Form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any form validation code goes first>
If Me!WorkerID.Value <> Me.WorkerID.PreviousValue Then
Me!OriginalWorkerID = Me.WorkerID
End If
End Sub

will store the previous (not the original) value.

If you want a history, you'll need a new table with fields for the primary key
of this table as a link, a start date and end date, and the WorkerID in force
during that date range.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Annette]

Sorry, I don't think I explained myself very well.
I only want OriginalWorkerID to be what WorkerID was set initially.

So, if on 01/01/2010 a new record was created for WorkerID 3, then
OriginalWorkerID would be 3. Then on 02/01/2010 the WorkerID was
changed to 7, I still want OriginalWorkerID to be 3. If on 04/01/2010
the WorkerID was changed to 10, again, OriginalWorkerID would remain
at 3 as that was what WorkerID was when the record was created.

 

[John W. Vinson]

Sorry, I don't think I explained myself very well.
I only want OriginalWorkerID to be what WorkerID was set initially.

So, if on 01/01/2010 a new record was created for WorkerID 3, then
OriginalWorkerID would be 3. Then on 02/01/2010 the WorkerID was
changed to 7, I still want OriginalWorkerID to be 3. If on 04/01/2010
the WorkerID was changed to 10, again, OriginalWorkerID would remain
at 3 as that was what WorkerID was when the record was created.

Well... ok; just update both fields when you create the record and don't
change it later. Change my suggested code to

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any form validation code goes first>
If Me.NewRecord Then
Me!OriginalWorkerID = Me.WorkerID
End If
End Sub

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com