Who updated the record?!

  • Thread starter Thread starter KateB
  • Start date Start date
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?
 
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).
 
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
 
CurrentUser will only work if Access User-Level Security has been applied so
that users have to log into the application. If ULS has not been applied,
CurrentUser will return admin for all users.

To get their network ID, see http://www.mvps.org/access/api/api0008.htm at
"The Access Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rob Parker said:
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


KateB said:
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?
Click to expand...
Click to expand...
 
Sorry, it wasn't MY post that had received the response before, it was one I
searched for, and the link Doug pasted below is the one I found which works
great! Thanks for the responses - I'm trying to get my head around them, and
have since seen posts regarding setting up a user log which might be a better
option if I have the time to try to understand it. I suppose I should do a
course in VBA sometime!

Thanks for the help - much appreciated.

Douglas J. Steele said:
CurrentUser will only work if Access User-Level Security has been applied so
that users have to log into the application. If ULS has not been applied,
CurrentUser will return admin for all users.

To get their network ID, see http://www.mvps.org/access/api/api0008.htm at
"The Access Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rob Parker said:
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


KateB said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top