efrimpol
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.
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.
Have you tried Application.UserName?
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.
Just thinking.
ASKER
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.
Please correct me if I am wrong.
You need this setting
Capture1.gif
Capture1.gif
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
API: Get Login name
http://access.mvps.org/access/api/api0008.htm
ASKER
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)
Enable all macros (not recommended)
Database in Trusted Locations
Both of these already set, including
following:
Allow Trusted Locations on my network (not recommended)
ASKER
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.
Nothing to it ... see the link above - very simple.
It will immediately eliminate your issue.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
will create and get back to you
ASKER
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.
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
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 !
Version independent :-)
Good Job !
ASKER
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
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
Here is one for ComputerName instead of using Environ("ComputerName") if you ever need it:
http://access.mvps.org/access/api/api0009.htm
ASKER
thanks