?
Solved

Using Environ Function to get user name - stops working

Posted on 2009-02-09
33
Medium Priority
?
1,140 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

0
Comment
Question by:Karen Schaefer
  • 14
  • 10
  • 4
  • +1
29 Comments
 
LVL 75
ID: 23592783
What happens if you do this in the vba Immediate window:

?Environ("UserName")

mx
0
 

Author Comment

by:Karen Schaefer
ID: 23592828
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
 
LVL 75
ID: 23592859
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Karen Schaefer
ID: 23592907
Yes to both, any ideas on work around - I need to store the UserName - WinNt name.

k
0
 

Author Comment

by:Karen Schaefer
ID: 23592917
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
 
LVL 75
ID: 23592962
Any change you have some missing references?

Vba editor>>Tools>>References

mx
0
 

Author Comment

by:Karen Schaefer
ID: 23593087
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
 

Author Comment

by:Karen Schaefer
ID: 23593117
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
 
LVL 75
ID: 23593173
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
 

Author Comment

by:Karen Schaefer
ID: 23593223
tried with and w/o Equals - still errors.  

k
KFS.jpg
0
 
LVL 75
ID: 23593389
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
 

Author Comment

by:Karen Schaefer
ID: 23593405
It works with the debug window but not with the default value in the table -

K
0
 
LVL 75
ID: 23593426
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
 

Author Comment

by:Karen Schaefer
ID: 23593450
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 23593647
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
 

Author Comment

by:Karen Schaefer
ID: 23593771
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
 
LVL 85
ID: 23605221
<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
 
LVL 38

Expert Comment

by:Jim P.
ID: 23605837
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
 
LVL 75
ID: 23605866
Scott ... you can - I promise.

mx
0
 
LVL 85
ID: 23606061
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
 
LVL 85
ID: 23606096
Here's a screenshot ... maybe I don't have a table setting right? Not sure ...
EnvironError.png
0
 

Author Comment

by:Karen Schaefer
ID: 23606126
Scott,

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

Karen
0
 
LVL 75
ID: 23606140
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
 
LVL 75
ID: 23606161
ah ha ... http:#a23593647

:-)

mx
0
 
LVL 85
ID: 23715842
Seems like mx answered your question ... http:#a23593647
0
 
LVL 75
ID: 23716277
Yes ... I was just about to point that out.

thx Scott.

mx
0
 
LVL 75
ID: 23764970
"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
 
LVL 75
ID: 23767403
"DatabaseMX's comment #23593647 (0 points)"

Zero points ???

<sigh>
0
 
LVL 75
ID: 23767482
m ... go to bed !!!  I am ... cuz I am SOOO confused.

mx
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

850 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