UserName function not working in 2007 (worked in 2003)

[sychoangel]

I have a form in Access 2003 that will automatically grab the users network Login ID when they reserve a PC on a form & store it in a field. This worked fine in 2003, however in access 2007 I get the error message:
Unknown function 'Environ' in validation expression or default value on 'PCReg.Net_ID'.

Here is the code:
Private Sub Server_Name_AfterUpdate()
Me.Net_ID = Environ("UserName")
End Sub

 

[boblarson]

If you are going to use the Environ function in Accesss 2007 (I suggest using this API instead: http://www.mvps.org/access/api/api0008.htm )

Use Environ$("username") if you must use Environ.

 

[sychoangel]

Thankyou for that is there are reason why you recommend the API instead

 

[boblarson]

The Environment variables can be easily changed by anyone with knowledge of them. The API will get the actual network user login, instead. For example, if I wanted to be malicious and do something and point the blame elsewhere I could simply set my username environment variable to someone else's login and do what I want and then set it back and you'd likely not ever know.

 

[RossWindows]

...I suggest using this API instead: http://www.mvps.org/access/api/api0008.htm

Hi Bob, I've been using Environ("username") in the defualt value in many of my tables. It has worked great so far. I want to follow your advice and use the API instead due to security.

I added the API in a new module in my front-end, and I can call it using fOSUserName() in VBA... This works fine. I can also use it in queries just fine.

I am having a problem using it as the default value in my tables though.
I added the API to the back-end but it still doesn't work.
I get the same error as the fellow on the original post.
I am using access '97

 

[boblarson]

I am having a problem using it as the default value in my tables though.
I added the API to the back-end but it still doesn't work.
I get the same error as the fellow on the original post.
I am using access '97

The API should only be in the frontend.

How are you referencing it in your default value for the table? It should be thus:

=fOSUserName()

 

[RossWindows]

Yes, I've tried every variation of =fOSUserName()
(with and without the equal sign and the parenthesis)
And I tried leaving the module in the front end and adding a reference to it, but still no luck.

In table design view, it allows me to enter =fOSUserName but when I try to close and save the table, I get error message shown in the attached image.

 

[boblarson]

How about a screenshot of the table in design view?

 

[boblarson]

Oh, also you didn't name the standard module that has fOSUserName in it the same name did you? And you did put it into a standard module and not a form's module, correct?

 

[RossWindows]

I named the module modUserName
And if you mean the difference between a Module and a Class Module, I added it as a Module

 

[RossWindows]

Sorry Bob, I didn't see your previous request for the screen shot...
Here you go.

 

[boblarson]

1. Have you set a trusted location?
http://www.btabdevelopment.com/main...owtoenablecodeandmacros/tabid/57/Default.aspx

2. You don't have it set like
Private Function fOSUserName()

Instead of

Public Function fOSUsername()

or just

Function fOSUsername()

do you ?

 

[RossWindows]

Here is my Module named modUserName

Option Compare Database
Option Explicit
'******************** Code Start **************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
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 = vbNullString
    End If
End Function
'******************** Code End **************************

 

[RossWindows]

1. Have you set a trusted location?
http://www.btabdevelopment.com/main...owtoenablecodeandmacros/tabid/57/Default.aspx

I'm actually using Access '97 and '03.

 

[boblarson]

I'm actually using Access '97 and '03.

The title of your post said "UserName function not working in 2007 (worked in 2003). That probably has something to do with it. I don't know if you can use that function in Access 97.

 

[RossWindows]

I guess so.
Yeah the title was misleading because it was orignally posted by somebody else and i hijacked it. It definately was not my intention so my apologies.

How about I add some code so that if the user has the db open in '03 it uses the standard API, but if the user has the db open in '97 it just uses fOSUserName = Environ$("username")

I wonder...
Is my problem that I can't call THAT function in access '97
Or is the problem that I can't call ANY function from a table in access '97

 

[boblarson]

Or is the problem that I can't call ANY function from a table in access '97

It is likely that you can't call a function in the default like that for A97. But, why don't you just make a simple modification that should work for all versions -

Instead of using the default, just put in the BeforeUpdate event code to add the username.

 

[RossWindows]

I guess that is my only option...

And you're right. Access 97 only allows built-in functions for table default values (as I found out by clicking the "..." next to default value; The only functions displayed are the built-in ones)

Well, thanks for your help bob!
I have a lot of work to do!