Link to home
Start Free TrialLog in
Avatar of efrimpol
efrimpolFlag for United States of America

asked on

Microsoft Access 2013 - Unknown function "Environ" in validation expression or default value ...

Situation: upgrading Access 2003 databases to Access 2013.

Required: Table with fields "CreatedBy" with default value of ' =Environ("UserName") '. Data entry is completed via form, not directly into table, LastMaintained and LastMaintainBy, each with no default value.

Prerequisites:

Module:

Function UserNameWindows() As String
    username = Environ("USERNAME")
End Function

Form:

As soon as user begins to enter data, the default value for this field should populate with the username of the individual logged in (yes, I realize from others posts concerning altering via SET command, but company is less than 25 people and none know of such methods).

This has and always worked in Access 2003, but will not work in Access 2013.

Yet, form has an OnOpen Event with following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.LastMaintained = Now()
Me.LastMaintainedBy = Environ("UserName")
End Sub

These fields get the correct information no problem.

The error that occurs either when entering a new or updating an existing record.

"Unknown function "ENVIRON" in validation express or default value on "nameofform.nameoffield".

Help reflects error 3388 - The function you are referring to is either unknown (because the name is invalid) or misspelled or is a type of function not allowed by the validation rule or default value property. Among the types of functions not allowed are user-defined, SQL Aggregate function.

I tried removing default value from the field in the table with no change (still same error).

I cannot assign the ENVIRON= to the CreatedBy field because then every time the form is opened, it will overwrite that field (so the original creator will be overwritten with the last user who maintains the record).

Any ideas, I have some more databases with similar code that needs to be upgraded.

Thanks in advance.
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Have you tried Application.UserName?
Avatar of efrimpol

ASKER

Possibly add code to check CreatedBy field for isnull/isblank and then using Environ statement? On subsequent opens, field will be populated, so no change should occur.

Just thinking.
The ENVIRON function will retrieve the name of the user who is currently logged into the computer, whereas "I believe" the Application.Username will be the name of the user currently using the application. The default username when using an Access database is "Admin".

Please correct me if I am wrong.
And your database needs to be in a Trusted Locations - which you can also set in the Options>>Trust Center.
Meanwhile ...I highly recommend NOT using Environ() ... and instead use the Windows API call ... used by most professional Access developers.  

API: Get Login name
http://access.mvps.org/access/api/api0008.htm
Trust Center Settings

Enable all macros (not recommended)
Database in Trusted Locations

Both of these already set, including

following:

Allow Trusted Locations on my network (not recommended)
I heard about the Windows API call, but I just don't know how to code it.
" but I just don't know how to code it."
Nothing to it ...  see the link above - very simple.
It will immediately eliminate your issue.
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
will create and get back to you
Great News (with an afterthought)!

I did following:

a) removed "=ENVIRON("UserName") as default value from Table

b) added following module (giving credit where credit is due)

'******************** 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 **************************

c) changed field on form and set default value to =fosUserName

d) saved changes and closed database

Opened database/form and created and saved record (no errors).
Exited form and checked Table. Sure enough, CreatedBy field is populated with my name (yahoooo!!).

Went to other computer and edited same record (different user logged in). Saved record and exited database.

Went back to my computer and checked record again. I see CreatedBy still the same, but LastMaintainedBy is user from second computer (which would be correct).

So all is good in the hood.

But now my final question.

Will this code also work in 2003 version? I ask because I can change the existing 2003 databases until I am ready to convert them to 2013 and won't have to worry about this occurring again.
Definitely.  I've been using that code since at least A97 :-)
Version independent :-)

Good Job !
FYI. I created code in 2003 and it works just as great.

If I was wearing a hat, I'd tip it off to you...


What the heck... I hope you can see it.
Uncle-Sam-tip-hat2.gif
LOL ... no problem.  

Here is one for ComputerName instead of using Environ("ComputerName") if you ever need it:

http://access.mvps.org/access/api/api0009.htm
thanks