Setting Environ$("UserName") for a form filter query

Posted on 2012-09-07
Last Modified: 2012-09-12

I am setting up a form that has a filter in place (im modifying something I found on the web and am not too familiar with how the filter works but...)

I already found out how to set the default form value to Environ$("UserName") so that the value is stored when a new record is created.

The filter property i am looking at should look like:

([Work Hours Extended].[UserName]=theUser)

How do I set "theUser" as a Environ$("UserName")?

Thank you!
Question by:billg7
    LVL 119

    Assisted Solution

    by:Rey Obrero
    create a function in a regular module

    Funtion getUserName() as string
    Exit Function

    in your query call the function

    ([Work Hours Extended].[UserName]=getUserName())
    LVL 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    Minor point that you should be aware of; if your environment is not locked down (user can get to a command prompt), it's very easy to set the username to something else.

    Instead, use the function below to return the login name from windows.


    Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long
    Private Declare Function GetUserNameA Lib "advapi32.dll" (ByVal lpBuffer As String, nSize As Long) As Long

    Public Function WhoAmI(bReturnUserName As Boolean) As String

            ' Function returns either user name or computer name

            Dim strName As String * 255

    10      If bReturnUserName = True Then
    20        GetUserNameA strName, Len(strName)
    30      Else
    40        GetComputerNameA strName, Len(strName)
    50      End If

    60      WhoAmI = left$(strName, InStr(strName, vbNullChar) - 1)

    End Function
    LVL 1

    Author Comment

    Ok, well I'm getting a syntax error. I tried yours above. First off, the word function was missing a 'c' :) no big deal, but I'm still getting a syntax error. I also tried changing the 'exit' to 'end' with no luck.

    Also, I didn't have any modules in this, so I just set this up in the general declarations. I also tried creating a module which resulted in the same error.

    I'm not at all worried about security and i want to keep this very very simple.

    Here's what I've got:

    Function theUser() As String
        theUser = Environ("UserName")
    End Function

    Thank you so much for the help!
    LVL 1

    Author Comment

    Ok, for whatever reason it started working. Very strange. Anyway, thanks again for your help!
    LVL 1

    Accepted Solution

    Just FYI for anyone else, this is what I ended up with in it's own module:

    Public Function theUserName() As String

        theUserName = Environ$("UserName")

    End Function
    LVL 1

    Author Closing Comment

    This is the working solution with proper syntax based on the experts feedback :).

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now