Using Environ Function to get user name - stops working

I was using the Environ Function as the default value for the UserName and then it stopped working. My computer had to be reformatted a few months back and I believe this might have something to do with the problem, however, I am unsure what changed.

Does anyone have any ideas?

Karen
=Environ$("UserName")

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Most issues with ENVIRON have to do with Macro Security.  

Hey ... try and Office Repair ... open any office app, then from the Help menu ... Detect And Repair ...

can't hurt.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What happens if you do this in the vba Immediate window:

?Environ("UserName")

mx
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
returns the correct value, however, when I have it set in a query or as default value in table/field I either get error or #Name in the field.

I am getting "Unknown function 'Environ' in validate expression or default value on the table."

K
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I know there are some issues in A2007 ... but not in A2003

Do you have Office SP3 on this system?  SP3 does have issues.

Also, do you have Macro Security set to LOW ?

Tools>>Macro>>Security ... Set to LOW ?

mx
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Yes to both, any ideas on work around - I need to store the UserName - WinNt name.

k
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
I am currently using the

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

and

Public Function MyUName() As String
    Dim s As String
    Dim lRv As Long
   
    s = String(1024, 0)
   
    lRv = GetUserName(s, 1024)
    If lRv <> 0 Then MyUName = Mid(s, 1, InStr(s, Chr(0)) - 1) Else MyUName = Environ("Username")
End Function
 both give area - Missing Environ?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Any change you have some missing references?

Vba editor>>Tools>>References

mx
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
I found the code on google,

I tried setting the default value on the table and still am getting the same issue.  see picture of table settings.

K
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 = ""
    End If
End Function

Open in new window

tableUsrName.png
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
When I run the code for either that fOSUserName  or Environ the both work in debug stage, however, the problem seems to lie with the calling of the functions as the default value for the field.  Any ideas why this is happening - check the references they are all correct.

k
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
If you are calling a function from the Default Value, you need an Equals sign

I have this in many places:

=ENVIRON("UserName")

mx
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
tried with and w/o Equals - still errors.  

k
KFS.jpg
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Humm ... well, I'm kind of running out of ideas Karen ... I've never had an issue with ENVIRON.

You have this above
http://www.mvps.org/access/api/api0008.htm

So ... if you put

=fOSUserName()

for the default value ... does that work?

Does ?fOSUserName() work in the immediate window?

mx
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
It works with the debug window but not with the default value in the table -

K
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
And for SURE Macro Security is set to Low?

This:

" both give area - Missing Environ?"

Maybe you should cross post in the XP zone?

I can't think of what else to try ...

IF this fOSUserName()  is failing at the table level, something very unusual is going on ...

mx
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks Joe - I will try your suggestion of cross posting.

Thanks anyway.  I am searching the web - but not finding any hints as to why this works in the code but not when called in the table level.

Karen
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
I got to thinking it has to be just my machine, because I have no complaints from my users.  I am currently seeking help from my internal help desk - I may have to do like you say and do the Repair possibly reload Access on my pc.

Thanks,

K
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<but not with the default value in the table ->

You've been doing this at the TABLE level? AFAIK, you can't do this at the table level. You can at the Form level.
0
 
Jim P.Commented:
I have found that you are better off using the mpr.dll instead of the advapi.dll. I had big problems with the advapi in a TS/Citrix environment vs an XP environment.
-------------------------------------
   Declare Function WNetGetUser Lib "mpr.dll" _
      Alias "WNetGetUserA" (ByVal lpName As String, _
      ByVal lpUsersName As String, lpnLength As Long) As Long
-------------------------------------

As far as the ENVIRON -- it is based off your SET strings in the CMD prompt. I'm betting that if you go to a dos prompt and do "SET <Enter>" you won't have "USERNAME=kfschaefer" in the list. This is set login/environment setting -- not sure where.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Scott ... you can - I promise.

mx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I just tried it - gave me the same error as she's reporting:

"Unknown function Environ in validation expression or default value on Table1.sName"

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Here's a screenshot ... maybe I don't have a table setting right? Not sure ...
EnvironError.png
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Scott,

I got it working after I did the Detect and Repair on the Office Suite.

Karen
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I have that exact same expression in the Default Value of loads of tables ... I'm looking at one right now ... never have seen that error.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ah ha ... http:#a23593647

:-)

mx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Seems like mx answered your question ... http:#a23593647
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yes ... I was just about to point that out.

thx Scott.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I got it working after I did the Detect and Repair on the Office Suite."

I answered your question ... http:#a23593647  with that suggestion.  Would you mind re-opening the Q ?

thx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"DatabaseMX's comment #23593647 (0 points)"

Zero points ???

<sigh>
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
m ... go to bed !!!  I am ... cuz I am SOOO confused.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.