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

 

[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.

 

[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.

 

[pr2-eugin]

Thank you so much Pat.. I understand this.. I also did not know that you will be able to update fields just by using Me.Field_name.. I though that can be done only for visible fields.. or fields that are on the Form.. That is the only reason I used UPDATE. after you mentioned I tried using it.. Works great.. Thanks once agin for opeing my eyes..

 

[nadzira]

hi guys,

I just want to know if this coding works for other database?
and I want to know where to put this coding because I faced the same problem which I want to record the timestamp of the gridview that has been modified by users. FYI I am using VS2010.

Hopefully u guys can help me..thank u very much..