Time Stamp when record is changed

[gmatriix]

Hello All,

I am trying to figure out how I can put a time stamp in a field when the record is changed. I know I can do a =Now() or something in the form but I would like for it to be a record in the table. So this is what I mean

If I change a record It would record the date and time in field in the table called "LastModified" I would also like to record the Window user and Machine number by using something like this:

=fOSUserName()
=fOSMachineName()

This is so the user can know who last made a change on that record.

Any Ideas??

 

[pr2-eugin]

You can get the Environmental variables using the following methods.

    [COLOR=SeaGreen]' Get Host Name / Get Computer Name[/COLOR]
sHostName = Environ$("computername")
   [COLOR=SeaGreen] ' Get Current User Name[/COLOR]
sUserName = Environ$("username")

Hope this helps !

 

[gmatriix]

Thanks, this does help, however my main problem is how to record date and time when the record is changed. That can be added to show who made the change.

Any Ideas on how I can accomplish that?

 

[pr2-eugin]

Do you update or modify records through the use of Forms?

 

[gmatriix]

Hello,

Yes I do

 

[pr2-eugin]

Use the After_Update Property, in ehich you can use a UPDATE statement. As

Private Sub Form_AfterUpdate()
    Dim dd As Date
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    dd = Format(Now(), "dd/mm/yyyy hh:mm:ss")
    [COLOR=SeaGreen]' Get Host Name / Get Computer Name[/COLOR]
    sHostName = Environ$("computername")
    [COLOR=SeaGreen]' Get Current User Name[/COLOR]
    sUserName = Environ$("username")       [COLOR=SeaGreen] 'Username is obtained to produce Time and UserStamp[/COLOR]
    cmd = "UPDATE your_table_name SET DateStamp=#" & dd & "#, UserStamp='" & sUserName & "' WHERE use_a_Criteria"    [COLOR=SeaGreen] 'use_a_Criteria like [/COLOR][COLOR=SeaGreen][CUSTOMER ID] = " & ID & "[/COLOR]
    dbs.Execute cmd
End Sub

 

[gmatriix]

Thanks very much! This is how I have the code

Private Sub Form_AfterUpdate()
    Dim dd As Date
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    dd = Format(Now(), "dd/mm/yyyy hh:mm:ss")
    ' Get Host Name / Get Computer Name
    sHostName = Environ$("computername")
    ' Get Current User Name
    sUserName = Environ$("username")        'Username is obtained to produce Time and UserStamp
    cmd = "UPDATE Product SET DateStamp=#" & dd & "#, UserStamp='" & sUserName & "' WHERE [ProductID]??"     'use_a_Criteria like [CUSTOMER ID] = " & ID & "
    dbs.Execute cmd
End Sub

Sorry Im alittle new to code. Not sure what to exactly to put in the Where statement. Do I have to have fields in the table that say DateStamp and UserStamp?

Sorry

 

[pr2-eugin]

No dont worry about that, we are all here to learn, well the UPDATE statement has to know which particular record to update, if there is no WHERE condition it will update the whole table with DateStamp and UserStamp. So use something like this..

Private Sub Form_AfterUpdate()
    Dim dd As Date
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Dim pID As Integer
    pID=[ProductID].Value    [COLOR=Red]'MAKE SURE IT IS THE [CONTROL_OF_ProductID_NAME].VALUE[/COLOR]
    dd = Format(Now(), "dd/mm/yyyy hh:mm:ss")
    ' Get Host Name / Get Computer Name
    sHostName = Environ$("computername")
    ' Get Current User Name
    sUserName = Environ$("username")        'Username is obtained to produce Time and UserStamp
    cmd = "UPDATE Product SET DateStamp=#" & dd & "#, UserStamp='" & sUserName & "' WHERE [ProductID]=" & pID &""     
    dbs.Execute cmd
End Sub

As mentioned you should use the name of the text field which has the ProductID... Hope this helps..

EDIT: Yes you shold have fields in the table !!

 

[gmatriix]

Ok...I am following you.

I went to design and right clicked on the text box that is named "ProductID" Then to the event tab and after update put the code in. My table is called "product".

I guess I'm confused as to what field in the table it would be updating or where it would put the date/time stamp and the User and Machine data in the table.

I ran the form and updated something and look back at the table but did not see anything...

Something I must be doing wrong. I really appreciate your help with this!

> what fields should I have in the table?

 

[pr2-eugin]

No problem, Go to the Table Design View, Create two new columns as UserStamp with Text type and then DateStamp with Date/Time type. Save and close.

Then the code should go inside Form_AfterUpdate() not the TextField's AfterUpdate. You have to note the name of the TextField and use it in pID as,

pID=name_of_theProduct_ID_Field.Value.

Save the Form and then try it. Now it should have stamped the value of the computer name in UserStamp and Date&Time in DateStamp.

 

[gmatriix]

Alright....I fixed it.

Ran it again and got a compile error on

sHostName = Environ$("computername")

Thx

 

[Pat Hartman]

DO NOT USE THE FORM'S AFTERUPDATE EVENT TO MODIFY THE CURRENT RECORD!!!! This puts the form into an infinite loop in earlier versions of Access. The current version stops the recursion more gracefully.

The correct event to use for this purpose is the Form's BeforeUpdate event. It is the last event that runs BEFORE a record is saved. The AfterUpdate event runs AFTER a record is saved so when you put code in that event to update the current record, you make it dirty again so the BeforeUpdate event fires again followed by the AfterUPdate event where you dirty the record causing the BeforeUpdate event to run followed by the AfterUpdate event where you dirty the record ---- are you getting the picture?

Running an update query that updates the current record is almost as bad. But that only results in an error message telling you that someone is causing a conflict by updating the current record. That someone is you.

In the form's BeforeUpdate event use:

Me.UpdateDate = Now()
Me.UpdateBy = Environ("UserName")

 

[pr2-eugin]

Pat, I understand what you mean, I also understand why it is best to place the code inside BeforeUpdate. But just curious. I have a Form which is bound to a Table, I use the AfterUpdate in whcih I perform an Update exactly how I have described. I have not had any problem until today. Why would this be? I use Acess2010, on a Windows 7 OS.

gmatrix > Try removing the $ and see if it works.

 

[gmatriix]

Hello Pr2-euin,

I tried taking the $ out and Im still getting the "Compile Error Variable not defined". Can you post the exact code you have so I can compare it to mine?

Pat thank you for your post! I will try your suggestion as well..

This is the code that I have

Private Sub Form_AfterUpdate()
Dim dd As Date
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Dim pID As Integer
    pID = [productid].Value  'MAKE SURE IT IS THE [CONTROL_OF_ProductID_NAME].VALUE
    dd = Format(Now(), "dd/mm/yyyy hh:mm:ss")
    ' Get Host Name / Get Computer Name
    sHostName = Environ("computername")
    ' Get Current User Name
    sUserName = Environ("username")        'Username is obtained to produce Time and UserStamp
    cmd = "UPDATE Product SET DateStamp=#" & dd & "#, UserStamp='" & sUserName & "' WHERE [productID]=" & pID & ""
    dbs.Execute cmd
End Sub

thx

 

[pr2-eugin]

Try adding this..

Dim sHostName As String

along with other variable declarations inside the method.

 

[gmatriix]

It Worked! Thank you so much!

I added just a little to it and it worked! I just followed the same pattern that you gave me. I just added a couple more "As String" Here is what I added

Private Sub Form_AfterUpdate()
Dim dd As Date
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Dim pID As Integer
    Dim sHostName As String
    Dim sUserName As String
    Dim cmd As String
    pID = [productid].Value  'MAKE SURE IT IS THE [CONTROL_OF_ProductID_NAME].VALUE
    dd = Format(Now(), "dd/mm/yyyy hh:mm:ss")
    ' Get Host Name / Get Computer Name
    sHostName = Environ("computername")
    ' Get Current User Name
    sUserName = Environ("username")        'Username is obtained to produce Time and UserStamp
    cmd = "UPDATE Product SET DateStamp=#" & dd & "#, UserStamp='" & sUserName & "' WHERE [productID]=" & pID & ""
    dbs.Execute cmd
End Sub

Thanks Again! I am study VB for Access now...So I hope I can help someone help someday!

Take Care!

 

[pr2-eugin]

You are welcome, glad it worked. someday you will.

 

[Pat Hartman]

I have a Form which is bound to a Table, I use the AfterUpdate in whcih I perform an Update exactly how I have described. I have not had any problem until today. Why would this be?

I explained how modifying the current record in the AfterUpdate event puts the form into an infinite loop. You're lucky you haven't had a problem before. It could be that the loop finally ate up all the memory in your computer. Although running an update query that updates the current record doesn't cause a loop, it does cause an error message and could cause you to loose one update or the other.

gmatrix,
Your solution is a problem waiting to happen. It may work now but it will fail at some point.

 

[gmatriix]

Thanks Pat,

I did try your solution and it works! Thanks so much for your help! I hope to be as good as you guys one of these days.

Take Care!

 

[pr2-eugin]

Pat,
So what you mean is updating a current record "anywhere" using a UPDATE query will cause problem? What if the update query is placed inside some other method like Button_AfterClick()?

Sometime I want to update some notes after the user selects a option from option group. Based on the option, I would like to update that particular column.. Example, I have options like 'Cancellation', 'Complaints', 'General'.. So if the option Cancellation is selected, I want to update the CanxMemo column in the table.. I have one text are depending on the selection, I use a update query. Still a problem?

I tried placing the update query inside BeforUpdate now it is not called at all.. so my data remains unchanged.. I mean the Update has not taken place.