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)

check references on target computers....

try requerying after insert and befor environ().....

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

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.....

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.....

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.

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]

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

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.

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

Should the variable be:

Environ$('UserName')

Simon

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).

Forgive me!

This is my Function:

This works, it works well and its reiterated throughout my entire database.

Bye

Simon

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.

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.