We help IT Professionals succeed at work.

Using Environ Function to get user name - stops working

Karen Schaefer
on
Medium Priority
1,308 Views
Last Modified: 2013-11-27
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

Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
What happens if you do this in the vba Immediate window:

?Environ("UserName")

mx
Karen SchaeferBI ANALYST

Author

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
Karen SchaeferBI ANALYST

Author

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

k
Karen SchaeferBI ANALYST

Author

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?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Any change you have some missing references?

Vba editor>>Tools>>References

mx
Karen SchaeferBI ANALYST

Author

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
Karen SchaeferBI ANALYST

Author

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
If you are calling a function from the Default Value, you need an Equals sign

I have this in many places:

=ENVIRON("UserName")

mx
Karen SchaeferBI ANALYST

Author

Commented:
tried with and w/o Equals - still errors.  

k
KFS.jpg
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
Karen SchaeferBI ANALYST

Author

Commented:
It works with the debug window but not with the default value in the table -

K
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
Karen SchaeferBI ANALYST

Author

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
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Karen SchaeferBI ANALYST

Author

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
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
<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.
Most Valuable Expert 2014

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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Scott ... you can - I promise.

mx
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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"

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Here's a screenshot ... maybe I don't have a table setting right? Not sure ...
EnvironError.png
Karen SchaeferBI ANALYST

Author

Commented:
Scott,

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

Karen
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
ah ha ... http:#a23593647

:-)

mx
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Seems like mx answered your question ... http:#a23593647
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Yes ... I was just about to point that out.

thx Scott.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"DatabaseMX's comment #23593647 (0 points)"

Zero points ???

<sigh>
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
m ... go to bed !!!  I am ... cuz I am SOOO confused.

mx
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.