Record the user who enters a record and the user who edits a record 1

[Oliver2003]

I would like to record the user who enters a record and the user who edits a record with time and date stamp.

To keep things simple I have a table with a linked form - the form is used to input data into the table. I have added the following fields to the table:
InputUser
InputDate
InputTime
LastEditedByUser
LastEditedDate
LastEditedTime

For input date and time I have set the default value to =Date() and =Time().

What I would like to do is record the name of the user who started the new record and then if at a later date that record is edited I would like to record the name of the user that edited it along with the time and date that it was edited at. This info would be stored in the fields above.

I am using access workgroup security .mdb

Any help would be appreciated.
Cheers

 

[psemianonymous]

1. For the default value of the username control, =CurrentUser()

2. If you want to have it date/timestamp your data on every update, put that code in the Form's BeforeUpdate() event. By "that code" I mean assign the current date/time/currentuser to their associated controls.


--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[GingerR]

you can use the access function

environ("Username")

to get the logon ID of the person signed on to the machine. this is usually sufficient.
you can see how it works by making a query, based on any table you have (it doesnt matter). then in the query for the first field put

=Environ("Username")

and run the query. your logon ID should appear. you can just delete that query, it's just to show you how it works.

so, in the table design, for the field InputUser, set the DEFAULT to the above.

in the form design, in the AfterUpdate property, put an [EventProcedure] and click the button with the three dots on it to the right of that property. put in this code:

 me.LastEditedByUser = environ("Username")
me.LastEditedDate = date()
me.LastEditedTime = time()

 

[psemianonymous]

I'd like to change my answer to what GingerR wrote, except for the Environ("Username&quot -- you're specifically using Access/JET security, so just replace that with CurrentUser(). Other than that, do what GingerR tells you.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Oliver2003]

Thanks for the replies,

I have a problem, are:

me.LastEditedByUser = environ("Username&quot
me.LastEditedDate = date()
me.LastEditedTime = time()

refering to text box's on the form - I have done this and set their Control source to the fields in the table. When the record is updated they are filled in but when you click to go to the next record it does nothing just stays on record 1 and updates the time value

I could not add the =CurrentUser() to the default value of the field in the table again I had to create a text box on the form then set the default value to =CurrentUser()

Am I doing something wrong? Is there a way to enter the data directly into the table without the text boxes - they seem to lock the form.

Thank You

 

[Oliver2003]

Just checked the other fields in record 1 in the table (they all had data) and now they have been replaced with #Deleted ?

Any Ideas

 

[psemianonymous]

Sorry about the confusion. It turns out I was right all along.


Move everything in the Form_AfterUpdate() to Form_BeforeUpdate(). This will stop the "infinite saving" problem.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[psemianonymous]

And no, I haven't been (personally) able to find a way to save to values without going through controls (i.e. textboxes). You do have to create textboxes (or whatever) for each value you want to save in the table.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Oliver2003]

thanks for that it works now!

 

[MichaelRed]

see faq 181-291. It is (I thik) easy to implemment, records ALL changes to records from BOUND forms and provides more detailed information than you are starting with. In most situatiuons tracking of changes will 'grow' to wanting to know the rest of the story, so just start at hte end and you won't need to re-visit the tioic as often.

The ONE caveat re the faq. Be sure to have a plan for 'archiving' the audit trail files fairly often, as letting them grow to the point of impacting overall application pperformance.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Oliver2003]

For some reason the =CurrentUser() will not work in the VB code, after typing it in, it comes up as =currentuser() i.e. it is not being recognised - I am using access 97
Any ideas?

 

[JoyInOK]

CurrentUser is available in Access 97. If you're using it in VB code, it would be
[Reviewer Name] = CurrentUser()
If you're setting the default value for a control from the properties window, you set it to
= CurrentUser()
This function is part of the Access Application library. Check your references to make sure it is not corrupted/moved. If you need to browse for it, mine was in:
C:\Program Files\Microsoft Office\Office\MSACC8.OLB
Microsoft Access 8.0 Object Library

 

[AWithers]

I found GingerRs post very helpful

I added the code environ("Username&quotto the default value in my table, saves coding all relevent forms etc.

Andy