Stamping a New Record with the Current User Name Only Once??

[motoxracer400f]

Hi,

I am using the the following code to get the windows user name:

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

and this code to put it in a text box on my form on load:

    Call fOSUserName
    UserNameTXT.Value = fOSUserName

How do I store the user's name (only when it is created) in my main table. I have a User Name text box on my form that is linked to my "User Name" column in the table. I am able to get it to stamp each record with the user name but if someone else views that record, it overwrites the original user stamp with the current one. I only want the original persons name that created the record to be stored.

Can anyone please help?
--Thanks

 

[RuralGuy]

Try this as the form's BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel As Integer)

   If Me.NewRecord Then
      Me.UserNameTXT = fOSUserName
   End If

End Sub

...and *stop* what you are doing in the OnLoad event!

 

[motoxracer400f]

Thank you, that works great!!

 

[RuralGuy]

Excellent! Thanks for posting back with your success.

 

[LB79]

Hello all,

I have a simular problem and wonder if someone can help me out...?

I have a form linked to a table in datasheet view.
I also have a textbox on the form showing the current user ID. Each time a record in changed I want the current user ID to save to the "Updated By" field of the record.
Is it possible to do this?
Many thaks for any help.

 

[RuralGuy]

Have you tried making the change in the BeforeUpdate event of the FORM?

 

[LB79]

Im not sure how that would work :S

 

[RuralGuy]

Something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

   [Updated By] = Me.txtUserID

End Sub

Using your field and control names of course.

 

[LB79]

Thanks - Would this code go in the Form VBA space or a module?
Will it only update the user name for the row that is changed?

 

[RuralGuy]

It goes in the BeforeUpdate event of the FORM, not a standard module. What version of Access are you using? Assuming the form is in Single Form mode then the code will only affect the one record.

 

[qadeerahmed83]

how to track changes made by the user, if he changes some record directly in Access table, not by form?

 

[Frothingslosh]

how to track changes made by the user, if he changes some record directly in Access table, not by form?

Not possible in Access. That's one reason users should NEVER have access to the tables themselves.

 

[gemma-the-husky]

going on from the last - the other reason for not using tables directly, is that users could enter some completely erroneous value in the table, which maybe a form would not allow them to do.

 

[nanscombe]

... not to mention deleting records without a trace.