Created by, Created Date, Modified by, and Modified Date

Djblois · Feb 9, 2009

I am creating it so in my database I have the ability to view who added a record or who modified a record. I already created fields in the table for this information. However, how do I add the information to the table.

1) How do I add today's date only when the record is created to a field.

2) How do I add today's date whenever the record is modified.

3) How do I add the users login (I already created a variable called strUserLogin) to the record upon creation. I assume this would be very similar to #1 if it is just tell me and I will figure it out.

Rabbie · Feb 9, 2009

1) is very simple. Just set the default value for the field to Date(). This will populate the field automatically when you create a record.

2) is done by putting in code to set the Modified field to Date() in the afterUpdate event for the form you use to edit the record.

3) would be similar to 2) when you want to record the last person to change the record.

Djblois · Feb 10, 2009

Rabbie said:
1) is very simple. Just set the default value for the field to Date(). This will populate the field automatically when you create a record.

2) is done by putting in code to set the Modified field to Date() in the afterUpdate event for the form you use to edit the record.

3) would be similar to 2) when you want to record the last person to change the record.

Ok, I have figured out the first two, but #3 is a little harder. How would I enter the userLogin only when it is created. I do not want it to change the userlogin any other time.

Brianwarnock · Feb 10, 2009

Could you check to see if its Null and only update if it is?

gemma-the-husky · Feb 10, 2009

i often have a recordcreatedby , and a recordlasteditedby

so i have in the forms beforeupdate event

if me.newrecord then
recordcreatedby = whoever
recordcreatedon = now() or date() 'depending
else
recordlasteditedby = whoever
recordlasteditedon = now() or date() 'depending
end if

now the whoever is important
there is a currentuser in access, but this is always Admin, unless you activate security.

otherwise, you can read the computer name, and windows login name, but you couldnt get these from access, and had to use windows calls - not sure if you can get these directly in VBA now.

Pat Hartman · Feb 10, 2009

Rabbie, you need to use the form's BeforeUpdate event to populate the fields we are talking about. The form's AfterUpdate event runs AFTER the record is updated and saved. Therefore, code in the form's AfterUpdate that dirties the current record puts the form into a never ending loop becaue Access needs to rerun the BeforeUpdate event and then the AfterUpdate event and you dirty the record again and so Access needs to rerun the BeforeUpdate event -- get the picture? Luckily, Access ultimately breaks out of this infinite loop when its call stack fills up and gracefully exits (earlier versions of Access would simply freeze if you did this). Using the AfterUpdate event of a control does not cause the same infinite loop.

Rabbie · Feb 10, 2009

Pat Hartman said:
Rabbie, you need to use the form's BeforeUpdate event to populate the fields we are talking about. The form's AfterUpdate event runs AFTER the record is updated and saved. Therefore, code in the form's AfterUpdate that dirties the current record puts the form into a never ending loop becaue Access needs to rerun the BeforeUpdate event and then the AfterUpdate event and you dirty the record again and so Access needs to rerun the BeforeUpdate event -- get the picture? Luckily, Access ultimately breaks out of this infinite loop when its call stack fills up and gracefully exits (earlier versions of Access would simply freeze if you did this). Using the AfterUpdate event of a control does not cause the same infinite loop.

Thanks for posting that correction, Pat. I was having a senior moment with my typing fingers and trying to do several things at once. I must remember that multi-tasking is for women

We men can't manage it

Pat Hartman · Feb 10, 2009

I'm glad you understand your limitations

Rabbie · Feb 11, 2009

Pat Hartman said:
I'm glad you understand your limitations

My wife regularly reminds me of them

Created by, Created Date, Modified by, and Modified Date

Djblois

Registered User.

Rabbie

Super Moderator

Djblois

Registered User.

Brianwarnock

Retired

gemma-the-husky

Super Moderator

Pat Hartman

Super Moderator

Rabbie

Super Moderator

Pat Hartman

Super Moderator

Rabbie

Super Moderator

Similar threads

Users who are viewing this thread