Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms / December 2007

Tip: Looking for answers? Try searching our database.

Who updated the record?!

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
KateB - 12 Dec 2007 10:39 GMT
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?
BruceM - 12 Dec 2007 13:01 GMT
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,
>
[quoted text clipped - 11 lines]
> first ID never changes but the 2nd reflects whoever amended the record
> last?
Rob Parker - 12 Dec 2007 13:16 GMT
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

> Hi,
>
[quoted text clipped - 11 lines]
> first ID never changes but the 2nd reflects whoever amended the record
> last?
Douglas J. Steele - 12 Dec 2007 13:47 GMT
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"

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi Kate,
>
[quoted text clipped - 44 lines]
>> first ID never changes but the 2nd reflects whoever amended the record
>> last?
KateB - 13 Dec 2007 15:56 GMT
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.

> 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,
[quoted text clipped - 51 lines]
> >> first ID never changes but the 2nd reflects whoever amended the record
> >> last?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.