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

[Djblois]

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]

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]

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]

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

 

[gemma-the-husky]

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]

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]

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]

I'm glad you understand your limitations

 

[Rabbie]

I'm glad you understand your limitations

My wife regularly reminds me of them