Date Stamp and Username for New and Edited Records

[legalhustler]

I am reading alot of conflicting and variations online about how to stamp the date and username for a new record or if a record get's edited.

I have a multi-user database. In the database I have a data entry Form that is linked to my Table with the following invisible fields (this is set this way because I don't want these fields to show in the Form):

1) CreatedDate
2) CreatedByUser
3) ModifiedDate
4) ModifiedByUser

Note: Each record will have these fields.

Whenever a new record gets added in the data entry form I want to capture the CreatedByUser and CreatedDate. In addition, whenever a record changes or get's modified I also want to capture the ModifiedByUser and ModifiedDate. If there has been no modifications/changes to a record, then obviously these two fields (ModifiedByUser and ModifiedDate) should be empty. None of the four fields information should change if someone is just viewing the record.

Can someone put it in a clear/concise step by step way to do this.

Thanks!

 

[legalhustler]

One more note - the CreatedByUser and ModifiedByUser usernames should be the names they use to login to their network computer.

 

[legalhustler]

Here is what I have accomplished so far:

I put the following code in a standard module:

Option Compare Database
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

In the CreatedByUser textbox property I set the default value as =fOSUserName()
In the CreatedDate textbox property I set the default value as =Now()

In the ModifiedDate textbox property I inserted the following code in the Before_Update event property:

Private Sub txtModifiedDate_BeforeUpdate(Cancel As Integer)
[txtModifiedDate] = Now()
End Sub

In the ModifiedByUser textbox property I inserted the following code in the Before_Update event property:

Private Sub txtModifiedByUser_BeforeUpdate(Cancel As Integer)
[txtModifiedByUser] = fOSUserName()
End Sub

The CreatedDate and CreatedByUser fields stamps the correct date/time and username when a new record is created, but the ModifiedDate and ModifiedByUser fields are empty when I modify an exisiting record. Does another user need to modify the record in order for this to work? If yes, then I do not want it designed this way. It should put the day and username no matter who modify a saved record (including me). I am not sure what I am doing wrong. Any help is appreciated.

Thanks!

 

[alansidman]

Look at this link by Denis Wright (SydneyGeek).

Tracking data changes in Access

 

[legalhustler]

Look at this link by Denis Wright (SydneyGeek).

Tracking data changes in Access

Thanks. Do you know what the following means: To capture the username it says it will "write the current logged-in user's name." Is this referring to a unique login for the Access DB or a login to the network computer username? I am interested in the latter. How would I change the code to capture the latter?

 

[alansidman]

It is the computer username. This can be determined by employing the Environ("UserName") function.

 

[legalhustler]

I don't know if this makes any difference but another important thing to note is that I currently have history log for two other fields in my Form that get's recorded in a separate Table. I used Allen Browne's method:

Allen Browne - Creating an Audit Log

I don't know if I can modify that to record the 4 fields I need in my main table or follow the suggestion on your link. Is it possible to have a history log in a separate table and in the main table for different fields in my form consecutively?

 

[alansidman]

Create a backup copy of your db and play with it to determine if you can do what you want. Without testing, the answer is an automatic no.

 

[legalhustler]

Create a backup copy of your db and play with it to determine if you can do what you want. Without testing, the answer is an automatic no.

I kept the audit trail codes that keeps a log of changes for two fields in my data entry form in a separate table. I also needed to keep timestamp and userstamp of my Form in my main table for each record being entered by a user, so I implemented the "Simple Tracking" solution from the link in your post #4, but I get the following errors:

Ambigious name detected: Form_BeforeUpdate

and

Ambigious name detected: Form_Current

I changed the name of the "Form" in the VBA code by making it "Form1" (i.e. Private Sub Form1_Current() ) which took care of the error message, but three of my fields are empty (CreateBy, ModBy and ModDate) when either try to create a new record or modify a existing one. Note, that I have other VBA codes for the BeforeUpdate and Current events of my Form.