Reflect the Windows User ID and Time of who did the record update in MS ACCESS 2013

Question

I have an MS access 2013 db that contains 5 tables. I have created in each table two fields (Update By and Update Time). I have created 5 data entry forms and included several action buttons like Add new record, delete record and Save record, etc... These action buttons were created using MS access Macro builder. What I would like to do is whenever a user click the Save button, the above two fields (Update by, and Update time) get filled with the Window user id (active directory id) and System time of that user. I tried to update the OnClick event of the Save button to do that, but it's not working for me. Any help. The After update event of the form, will keep updating these two fields all the time as a result of any modification or action being done to the form fields.

Thank you in advance for any help you can offer.

You may want to add some details about the code you've tried. — Hannah Vernon

Johnny Bones Johnny Bones · Accepted Answer · 2015-10-19T19:12:37

It's definitely the OnClick event that you want.

To get the username, put this behind the button:

Dim lngLen As Long
Dim lngX As Long
Dim strUserName As String

strUserName = String$(254, 0&)
lngLen = 255&
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0&) Then
    strUserName = Left$(strUserName, lngLen - 1&)
End If

If strUserName <> vbNullString Then
    GetNetworkUserName = strUserName
Else
    GetNetworkUserName = "{unknown}"
End If

Then put this in a separate module:

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

For the time piece, just use TimeValue:

TimeValue(Now)

Reflect the Windows User ID and Time of who did the record update in MS ACCESS 2013

1 Answers