Microsoft Access - Track who created each record

I was wondering if it was possible to see who each record was created by.  I've tried the Environ("username") as the default value approach and got a Name#? error.  I  would like to avoid this approach anyways as I am on a network and not all of the usernames would have actual value. Many are just default admin or something similar.  I've created my own login for accessing the database and I assigned those login variables to tempvar's.  This way I can see who last edited each record by assigning that tempvar to an afterupdate event.  However I am trying to see who created the record and have that stay stagnant after it is first entered.  Any ideas?

You'll need to add columns in your tables or credit "audit" tables to record a history of changes.  This is not built into most RDBMS databases.

Currently I plan on having a column dedicated to who created the record. I just don't know how to automatically populate the field

Here is the similar example that you are trying to look for

Store all changes made, and 
> Ascertain who made each change and when

Using the example of a student's form, we would need

> An additional table - tblStudentChanges.

> Two extra fields (Usercode and DateofChange) in both tblStudent andtblStudentChanges

When both the tables have identical field names, this process is very simple. Use theAfterUpdate event of the form, to insert a new record in the tblStudentChanges table, whenever a change is made.

     Private Sub Form_AfterUpdate()
     Dim db As Database

         Set db = CurrentDb
         db.Execute "INSERT INTO [tblStudentChanges] " _
    	 & " SELECT * FROM [tblStudent] WHERE " _
    	 & " [tblStudent].[StudentID]=" & Me![StudentID] & ";"
         Set db = Nothing
     End Sub

Refer this link
http://www.vb123.com/toolshed/00_access/auditrecords.htm


Regards

I've been using this for a few years, still seems to work for me.

In a new module:

Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" _
    (ByVal lpBuffer As String, nSize As Long) As Long

Function UserName() As String

    On Error GoTo UserName_Err

    strUserName = Space(255)
    If GetUserName(strUserName, Len(strUserName) + 1) Then
      strUserName = Trim$(strUserName)
      strUserName = Left(strUserName, Len(strUserName) - 1)
      UserName = strUserName
    Else
      UserName = "ErrorName"
    End If

UserName_Exit:
    Exit Function

UserName_Err:
    MsgBox Err.Description

End Function

Then, when you need to pull in the user, whether in VBA or a query

=UserName()

I know .Net has the environment properties, and that is probably a better way to go over time, but this still works in Access 2010.

Using the example of a student's form, we would need

> An additional table - tblStudentChanges.

> Two extra fields (Usercode and DateofChange) in both tblStudent and tblStudentChanges

> Use a password driven system or turn on Access built-in security to ascertain who the current user is.

Store all changes made

When both the tables have identical field names, this process is very simple. Use the AfterUpdate event of the form, to insert a new record in the tblStudentChanges table, whenever a change is made.

     Private Sub Form_AfterUpdate()
     Dim db As Database

         Set db = CurrentDb
         db.Execute "INSERT INTO [tblStudentChanges] " _
    	 & " SELECT * FROM [tblStudent] WHERE " _
    	 & " [tblStudent].[StudentID]=" & Me![StudentID] & ";"
         Set db = Nothing
     End Sub

 
Ascertain who made each change and when

In the sample database, the student form receives the usercode of the current user through the OpenArgs property. Use the FormOpen event to set it up.

     Private Sub Form_Open(Cancel As Integer)
     'if you are using Access passwords to identify
     '   current user, use CurrentUser() Access function

     [txtCurrentUser] = Me.OpenArgs

     End Sub

In the BeforeUpdate event of the form, check to see if any real changes have been made. You can use the OldValue property of controls to test for changes. If no changes have been made, simply cancel the update. If changes have been made, update the Usercode and DateofChange fields in the record.

       Private Sub Form_BeforeUpdate(Cancel As Integer)
       On Error Resume Next

        ' some controls may not have the Tag property ,
	' hence the resume next

       Dim blnCheckDiff As Boolean
       Dim ctl As Control

       blnCheckDiff = False
       For Each ctl In Me.Controls
           If ctl.Tag = "Check" And ctl.Value <> ctl.OldValue Then
              blnCheckDiff = True
           End If
       Next
       If blnCheckDiff Then
               [txtTime] = Now()
               [txtuser] = [txtCurrentUser]
       Else
               Cancel = True
       End If
       End Sub

 
Display Changes

Create a separate form based on the tblStudentChanges table. The form in the sample db displays the records in reverse chronological order, but you can change that, if needed. Make sure the form is a read-only and pop-up as well. Provide a command button to close the form and display the original Student's form again.

In the Student's form, create a command button that will display the Student Changes form for the current student. Make sure that all current changes have been saved before you open the Student Changes form.

     Private Sub cmdChange_Click()
         Dim stDocName As String
         Dim stLinkCriteria As String

         stDocName = "frmStudentChanges"
         
         DoCmd.RunCommand acCmdSaveRecord
         ' the forced save of the current record
         ' will ensure that the current changes
         ' are reflected in the new form to be opened.
         
             stLinkCriteria = "[StudentID]=" & Me![StudentID]
         Me.Visible = False
         DoCmd.OpenForm stDocName, , , stLinkCriteria
     
     End Sub
 
Hope this will help you.

One option is finding last editted user, like this way

To record the end-user that last edited a specified record I used the following code (insert it as "BeforeUpdate" in the form properties):


Private Sub Form_BeforeUpdate(Cancel As Integer)
LastEditedUser.Value = CurrentUser()
End Sub

"LastEditedUser" is the name of user field

If you have user security in place for the database then you can simply call:

currentUser()

If you would like to know who logged into that P.C.

Environ("UserName")

There is also other methods Neopa has an example here where he pulls it out of the registry:
http://www.thescripts.com/forum/post2152274-2.html