How to Log the user and date a record was completed

[sai_rlaf]

I have a form that is being used by about 4 people as a queue system. There is status field that indicates when something is new, pending, and completed. I would like to create a macro that is linked to a button that does the following.



- If a record is marked with a completed status, then that record add's today's date in the completed date field and add's the user's information to the Reviewed by field.

Any ideas on how to make that happen? I used 2007 but can use 2003 if I need to use .mdb security settings. I was wondering if its possible to use the "User name" field in the Access Options?

[JeroenMioch]

If me.[Status].value = "Complete" then
me![DateField] = date()
me![UserInfo] =fOSUserName()

Something like that ?

[sai_rlaf]

Those criteria work, I'm having trouble getting to only update the record I'm updating. It wants to go and update every record in the database with a completed status. I can I get to only do the record I'm updating?

[JeroenMioch]

Thats rather strange..
Normally, only the current record is affected uless i tell it to do otherwise.

Try this :

With Me.RecordsetClone
.MoveLast

'your codes here

end with

That tells it to go to the last record in your recordset, this is normally the current (on screen) record.

[sai_rlaf]

Maybe we are in different places. Where are you inputting your data? I'm using an Update query. Are you writing some VBA code or something?

[JeroenMioch]

Oh yes were definately NOT in the same place haha.
My codes is VBA coding.

You told me that you had a form, so i was guessing there was a record on it that needed updating on your form.

[sai_rlaf]

It keeps taking me to fOSUserName() and saying it's not defined. Any thoughts? Do I need to have security set up?

Thanks for your help so far.

[JeroenMioch]

Nope,

Im trying to let u use the windows or network login name.
I might have set u on the wrong track because i forgot it there was a module to go with it.

Follow the instructions in the link below if you think its of use for your project.

http://access.mvps.org/access/api/api0008.htm

If you made the module you call it with = FOSUsername()

its a good way to keep track of what user is doing things.

[nhior]

you can log the user what they do in the system by stamping..should be your table have a field name: who make update, when update.call the user name from the login by modules..then make something in the form in before update or after update..like this [username]= user; [lastupdate]=now() or use date()

[sai_rlaf]

Awesome Jereon. Thanks for the help. One more question and I'll leave you alone I promise. Below is the code I have so far. What I would like to do is add another condition before the update is made. I want to require that a particular follow up date field must be entered before the update occurs. If it's not entered, then the update won't go farther and instead they will get a msg box saying "Please enter a follow up date".

If Me.[Follow Up Status].Value = "Pending - Confirmation Needed From Sponsor Company" Then
Me![Reviewer] = fOSUserName()
MsgBox "Record Follow Up Status Successfully Updated"