none
Use Environ("Username") in update log RRS feed

  • Question

  • Hi,

    I have a Table T_Company and want to log changes to the values (in fact every field eventually).

    I am trying to use the after update event on the table. All is well with the attached macro (see picture), however I want to add the current windows username to each change.

    I am fluffing around using setlocalvar and other haphazard attempts to insert it into a Username field in my T_LogCompany Table without success.

    Any tips? The following works fine, I just want to add the Environ("Username") value to the T_LogCompany.Username field.  (using 2016).

    Thanks in advance

    John


    JG


    Sunday, August 5, 2018 3:17 AM

Answers

  • Hello John,

    It seems the Access macro does not support expression to get username directly. You could try to consider below solution as a workaround.

    Put below VBA code in a standard module.

    Public Function getUserName() As String
     getUserName = Environ("USERNAME")
    End Function

    Then in your macro.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 6, 2018 9:40 AM

All replies

  • You might like to take a look at ChangedRecordDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how records can be datetime-stamped, logged or copied to an audit table if a change in data will occur.  Note that the demo detects actual changes to data, not merely updates.  A record can be updated without any changes to its data taking place.

    To get the current user the Windows API GetUserName function is called rather than referencing the UserName environmental variable.  The value of an environmental variable can be changed, so it's possible for a moderately knowledgeable user to masquerade as another user.  Calling the API function is safer.

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Sunday, August 5, 2018 2:23 PM Hyperlink added.
    Sunday, August 5, 2018 2:21 PM
  • Hello John,

    It seems the Access macro does not support expression to get username directly. You could try to consider below solution as a workaround.

    Put below VBA code in a standard module.

    Public Function getUserName() As String
     getUserName = Environ("USERNAME")
    End Function

    Then in your macro.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 6, 2018 9:40 AM
  • Macros don't recognize most built-in functions so you need to build your own wrapper public function and call it instead.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, August 6, 2018 11:46 AM
  • I should also add that the environ approach is unreliable, not secure.  You may like to read http://www.devhut.net/2018/04/12/vba-recognize-user-get-username/

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, August 6, 2018 11:48 AM