Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    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)

  2. #2
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    check references on target computers....

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

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

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

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

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

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

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

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

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,525
    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 09:35.

  11. #11
    Join Date
    Sep 2006
    Posts
    265
    Should the variable be:

    Environ$('UserName')

    Simon

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

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

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

  15. #15
    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.
    Last edited by pkstormy; 11-07-07 at 12:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •