If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Undefined function Environ() in expression

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 7
Undefined function Environ() in expression

Hi All,

I'm using Access 2000 w/ ADO.

When I copy and execute my application to another machine (not all machines)
I am getting the error "Undefined function Environ() in expression".

The environ functionality works on my machine consistantly. It's hit of miss on others. The one funny thing about this environ() functionality is that is works on all machines on insert, but then you try to select the same line you just inserted you get the "Undefined function Environ ...

The environ("username") function is in an Access query as criteria. ie: ww_user = environ("username"). I then take that Access query and call it from a combo box rowsource in a form.

I have checked my Visual Basic References and have no MISSING or BROKEN references. I even checked the references via code.

These are my present references in this order:

Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

Thanks in advance,
Gerry (gone crazy)
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Bosnia & Hercegovina
Posts: 57
check references on target computers....

try requerying after insert and befor environ().....
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 7
Undefined function Environ() in expression

Hi,

Thanks for your reply.

I've checked the references on the other machines also and have no broken or missing references.

I guess what I really need to know is why are my queries failing
where the criteria is equal to environ("username") ?
Function RTrim is also failing but it is not in the criteria of a query it just as follows: EXP_ACCT: RTrim([ACTNUMBR_1])

Could I be missing a reference ? I know these functions can be used as I used them in the previous revision of this app.

Thanks again,
GWest
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Bosnia & Hercegovina
Posts: 57
I tested the function on my machine and it works fine (in a query just like you did it)....

so the query must be good....

I still think that it's about references....

I use same references as you do, except for Access, it's version 10.0

but I found 5 more references to Visual Basic for applications....

same name, but different dlls......

check on one of client computers (not runing it) what file they have for refference to this VBA.....
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Bosnia & Hercegovina
Posts: 57
there is one more thing to try.....

if you use SQL or query name and call it with execute method, you might want to try calling query/sql through DAO....

there was a problem like this with Access 97, and DAO was the only solution....

it might work with ADO as well, don't know.....
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Posts: 1
Having same problem with ENVIRON

I also ran into this problem with ENVIRON / RTRIM / FORMAT.

An odd one I noticed is that the command PRINT ENVIRON("USERNAME") works in a VBA immediate window but will not work in a text object on a form or report.

I posted this problem to Microsoft's Access FormDesign newsgroup and got a nice reply including code to call an API for the user name. I also got replies from others who have encountered this same problem.

WHY DOES ENVRON WORK ON SOME MACHINES & NOT OTHERS!!!

I just encounter this problem with the latest computers we got from DELL. The only way I got the forms to display the Username using ENVIRON on these new Dell machines was to reload the operating system. We order these machines from Dell with OfficeXP Small Business. I do not want to have to rebuild every new machine we buy.

I checked the references, dates & version. They match on the older functioning machines with the newer nonfunctioning Dell.

Also, the above functions are VB functions. So shouldn't we just be looking at VB / VBA references - whatever they are?

Thanks for any help you might offer.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 7
Re: Having same problem with ENVIRON

Hi Timmer,

I'm sorry you have run into this problem.
We also have the new Dells. We have encountered this problem on a new Dell w/ windows 2000/Access 2000 and XP/Access 2000.

I have a Dell laptop loaded with XP/Access 2000.

If I login as Administrator and access my .MDB on the C:\ drive, I no longer get the problem.

If I login as Administrator and access my .MDB on a network drive, I no longer get the problem.

If I login as myself (and I have admin privs) and access my .MDB on the C:\ drive or a network drive I get a permissions problem, stating that Access cannot access my file due to Internet permissions. As I do not have the laptop presently in front of me I cannot give you the exact error message, but you get the idea.

Our Network Administor thinks there may be specific permissions for MS Access which need to be changed.

But I still have the question you do:
!!! So shouldn't we just be looking at VB / VBA references - whatever they are? !!!

I've search the internet like you and have no solution yet.
I think it's about time to call Microsoft Support and see if they have a solution for this problem.

I'll keep you posted as to what or if we find anything. Please let me know if you find a solution.

Thanks,
GWest
=====================

Originally posted by timmer
I also ran into this problem with ENVIRON / RTRIM / FORMAT.

An odd one I noticed is that the command PRINT ENVIRON("USERNAME") works in a VBA immediate window but will not work in a text object on a form or report.

I posted this problem to Microsoft's Access FormDesign newsgroup and got a nice reply including code to call an API for the user name. I also got replies from others who have encountered this same problem.

WHY DOES ENVRON WORK ON SOME MACHINES & NOT OTHERS!!!

I just encounter this problem with the latest computers we got from DELL. The only way I got the forms to display the Username using ENVIRON on these new Dell machines was to reload the operating system. We order these machines from Dell with OfficeXP Small Business. I do not want to have to rebuild every new machine we buy.

I checked the references, dates & version. They match on the older functioning machines with the newer nonfunctioning Dell.

Also, the above functions are VB functions. So shouldn't we just be looking at VB / VBA references - whatever they are?

Thanks for any help you might offer. [/SIZE][/QUOTE]
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 7
Re: Undefined Function ENVIRON in Expression (Solution from Microsoft)

After an 1 1/2 hrs on the phone with Microsoft. We have a solution ... Thank You Janet !

When the MS Office suite is loaded, it changes the SandBoxMode registry value, this is a bug. To fix this problem go into the registry and navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\SandBoxMode

Open the SandBoxMode value under Engines and change the Value Data from 3 to 2.

Closed down your Access App, reopen it and try your operation again.

Thanks,
Gerry
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 101
I have the same problem but my Function ("Ergasimes") is not a built-in Function (like environ). I changed the SandBox value to all values (I had 3) but I had the same message "Udefinied Function 'Ergasimes' in Expression". Iam stuck.
Note that I run the query from a vb6 program. I tried both ADO and DAO but nothing.
Reply With Quote
  #10 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,212
I know it doesn't help directly... but this is a prime example of why you shouldn't rely on Environ for critical stuff.

Its a hangover form years ago..(back to MSDOS I think) when you could set environment variables. Some of these are set as part of the logon script on a networked system, some are on system wake up. the problem is that some environment variables get binned if there is low system memory, or if something isn't available at the time of system initialisation.

Another problem is that Environ variables can easily be faked or spoofed.. another good reason not to use them for security related stuff.

so thats the bad news about environ, its good for non critical stuff such as location of temp files, workspaces and the like

the bad news about environ can be got round by using other approaches to getting the username of the currently logged on user. Personally I prefer using Dev Ashish' API calls, Paul (PKStormy) has contributed the methods he uses.. they can be fond in the code bank

Last edited by healdem; 11-07-07 at 08:35.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Posts: 265
Should the variable be:

Environ$('UserName')

Simon
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 101
Hey, you are out of subject...The problem is not how to obtain the username. My problem is how to solve the error message that appeared when I run a query with a user-defined function. The function ("Ergasimes") is in an access module and the query run from a vb6 code (ADO or DAO).
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Posts: 265
Forgive me!

This is my Function:

Code:
Function GetUser()

Dim db As Database
Dim rs As Recordset

    With CodeContextObject
        Set db = DBEngine(0)(0)
        Set rs = db.OpenRecordset("SELECT ... WHERE EmployeesLookup.[Employee Login] = Environ$('UserName')")
        
        Do
            .UserDB = rs!Employee
            rs.MoveNext
        Loop Until rs.EOF
    
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End With
End Function
This works, it works well and its reiterated throughout my entire database.

Bye

Simon
Reply With Quote
  #14 (permalink)  
Old
Moderator
 
Join Date: Dec 2004
Location: Madison, WI
Posts: 3,925
FYI:

I've NEVER had a problem using the Dev Ashish' API call to get the username that I uploaded to the code bank but I have had occasional problems using the Environ.
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
Reply With Quote
  #15 (permalink)  
Old
Moderator
 
Join Date: Dec 2004
Location: Madison, WI
Posts: 3,925
Regarding...
Function GetUser()

Dim db As Database
Dim rs As Recordset

With CodeContextObject
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT ... WHERE EmployeesLookup.[Employee Login] = Environ$('UserName')")

Do
.UserDB = rs!Employee
rs.MoveNext
Loop Until rs.EOF

rs.Close
Set rs = Nothing
Set db = Nothing
End With
End Function

I'm an ADO person so I would have code like....
Function GetUserNEW()

Dim rs as adodb.recordset
set rs = new adodb.recordset
dim strSQL as string
strSQL = "SELECT ... WHERE EmployeesLookup.[Employee Login] = ' " & getuser() & " ' " <- note getuser from code bank
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
if rs.eof and rs.bof then
msgbox "No Records."
rs.close
set rs = nothing
exit sub
end if
rs.movefirst
do while not rs.eof
With CodeContextObject
.UserDB = rs!Employee
rs.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
End Function

Although I'm confused as to this function and the intent. If it's just to get the user loginID and put it into the form, if you look at the example getuser() from the code bank, you can see how easy it is to utilize the getuser() function in the example. You just set the default value of an unbound field (or the ControlSource) to =getuser(). There's no need to open up a recordset anywhere and loop through it.

If you're trying to return the FirstName/LastName or security level from a table, check out the new version of the getuserandpermissions example in the code bank though and see if this does what you're trying to do. I think you'll find it helpful.
- Seriously.
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Last edited by pkstormy; 11-07-07 at 11:27.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On