Who updated the record?!

K

KateB

Hi,

I have a form with a field called "entered by" which will automatically
insert the user's logon ID by using code supplied in a previous post by Jeff
Conrad.

It is possible that records may need updating or additional data adding (its
a call logging DB), therefore I need the original "entered by" to be fixed
with whoever began the record, but a second field "updated by" to mark if
anyone amends it. I assume I can use the same code, but how do I ensure the
first ID never changes but the 2nd reflects whoever amended the record last?
 
B

BruceM

Don't add any code that changes the first value. If the data is to be
displayed, lock or disable the text box in which it appears.
One thing that may help for the "started by" person is to add that value
only if it is a new record:
If Me.NewRecord Then
etc.
End If

One thing you may want to guard against is adding the name too soon. If
somebody goes to a new record, then reconsiders creating a new record, you
most likely do not want that person's ID added to the record. The Before
Update event may be a good place to run the code (or maybe a different
event, but in any case after some data have been added to the record).
 
R

Rob Parker

Hi Kate,

I didn't see your original post and replies, and you haven't posted the code
you're currently using, but the following should work. In the form's
BeforeUpdate event, insert the following:

Me![updated by] = CurrentUser()

If the previous code you were given uses a different variable for a similar
assignment to the [entered by] field, use that instead of the
"CurrentUser()" variable in the statement above.

For a new record, this will set the [updated by] field to the same value as
the [entered by] field; however, that should not be a problem. If you
really need to know if the record has been changed (ie. the [update by]
field should be null until the record is edited), then you can wrap this in
an If statement to test if the record is a new record:

If Not(Me.NewRecord) Then Me![updated by] = CurrentUser()

BTW, I would recommend that you name your fields without spaces in the field
names, and use "CamelCase" (a mix of upper and lower case - as used here)
for your fieldnames; this will allow you to enter code without enclosing
field names in square brackets. You can, if you need to do so, alias such
field names to a "human readable" form in a query which you use as the
RecordSource for forms/reports, so that the automatic label appears as you
desire.

HTH,

Rob
 
Top