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.
LVL 9
efrimpolAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Have you tried Application.UserName?
efrimpolAuthor Commented:
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.
efrimpolAuthor Commented:
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.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You need this setting
Capture1.gif
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Trust Center
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
And your database needs to be in a Trusted Locations - which you can also set in the Options>>Trust Center.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
efrimpolAuthor Commented:
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)
efrimpolAuthor Commented:
I heard about the Windows API call, but I just don't know how to code it.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" 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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Function UserNameWindows() As String
    username = fOSUserName()   ' From the code in the link above
End Function

RE "Required: Table with fields "CreatedBy" with default value of ' =Environ("UserName")"
I never use table level Default Values.
You can set any default value in the Form Before Insert event - which is what that event is intended for, among other things.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
efrimpolAuthor Commented:
will create and get back to you
efrimpolAuthor Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Definitely.  I've been using that code since at least A97 :-)
Version independent :-)

Good Job !
efrimpolAuthor Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
efrimpolAuthor Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.