User and Date Stamp

[Godber]

Hi,

I have got a table that has numerous columns, i then have a query that looks at a form where you can type in parts of the data and it will use the query to search and give you all the results on a form.

(i hope that makes sense first of all)

On the form with the search results on the user can edit the data and it will be reflected in the table.

What i would like to do is to have 2 auto populated fields, one with username and one with date and time. This data then gets entered into its retrospective column which are called "Agent Worked" & "Date of Completion".

The problem i currently have is that which ever why i try to obtain the date and user stamp it does not get inputed into the table.

i open to any suggestion as long as it works, the non negotiable is that it needs to only stamp the row that the search refers to.

Hope someone can help.

Thanks

Liam

 

[SydneyGeek]

Try this: It goes in the BeforeUpdate event of the form.
You will need to create a text field called ChangedBy, and a Date field called ChangedTime.

'Adapted from http://www.vbaexpress.com/kb/getarticle.php?kb_id=768

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim objNet As Object
    On Error Resume Next
    'user name
    Set objNet = CreateObject("WScript.NetWork")
    Me.ChangedBy = objNet.UserName
    Set objNet = Nothing
    'timestamp
    Me.ChangedTime = Now()
End Sub

Denis

 

[Godber]

Hi Denis,

First off thanks for you reply and sorry it took me so long to reply.

However i will need further help on this as so far i cant seem to get anything from this, i have created 2 new fields within my table like you said and then pasted that code in the before update section of my form, simply nothing happens.

I am struggling to understand the logic of the code, are you able to help me out?

Thanks

Liam

 

[SydneyGeek]

It should work. Basically, the BeforeUpdate event of a form runs when you save a record (either by using a Save item on the menu, or by navigating to a new record / closing the form).
Check the following:
1. The fields must be named exactly as in the code
2. If you have a form based on a query, you need to edit the query so that those fields are added to the query as well. They don't have to be physically added to the form, unless you want to see that information in the form; otherwise you can have a query that shows who changed the records.

To explain the code:
The 3 lines after the user name comment create a network object based on the Windows Scripting Host, and use that to grab the name of the logged-in user.
To timestamp the change we use the Now() function.

On Error Resume Next is to prevent an error from stopping the code; in this case, we don't want to know.

Denis

 

[Godber]

Thank you for explaining that now makes much more sense to me.

In terms of keeping this information for record, i would like the user and time stamp to be entered into 2 colomns on the table so i can refer back to it at a later date to see who last went on said account.

Will the above do this?

Many Thanks

Liam

 

[Godber]

Ignore that Denis, i got that figured in the end what i did was use your code, have it relate to 2 invisible bound text boxes and it works a treat!

Thank you very much

 

[SydneyGeek]

You're welcome

Denis