Environ("UserName") & Office 2007

[Kenln]

I use 'Environ("UserName")' in several places and it has always worked well.

In Office 2007 (without Service Pack 1 yet) I can use the variable in VBA but not in a query.

i.e.

SELECT UCase(tbl_Job_Access.Job_No) as Job_No

FROM tbl_Job_Access 
LEFT JOIN Job_Desc 
ON tbl_Job_Access.Job_No = Job_Desc.Job_No

WHERE (((tbl_Job_Access.User_ID)=Environ("UserName")))

Does anyone know if this is fixed in SP1, if I need to add a reference or if it simply does not work anylonger.

Thank you

 

[georgedwilkinson]

As a work-around you could make a VBA function to return the value to a query.

 

[Kenln]

True,

But to be honest I am unsure as how to do it. The query is used as a RecordSource for Combo Boxes, Forms, etc...

It might be easier to simple create a new query each time the db is opened.

In either case that really doesn't answer the question though.

 

[Banana]

Actually it does.

Just create a wrapper function in a module:

Public Function GetUserName() As String

GetUserName=Environ(UserName)

End Function

Then in your query, replace "Environ(UserName)" with "GetUserName()".

 

[chergh]

If the environ function is being blocked I would guess you are running Access in sandbox mode.

See http://office.microsoft.com/en-us/access/HA101674291033.aspx to disable sandbox mode.

 

[Kenln]

chergh,

Yep! That worked perfect.

Thank you,

 

[Kenln]

Banana,

I need to remember that one. I didn't know I could call a function from a query.

I probably won't use it here (because of the SandBox article) but I can think of places I can.

Thank you,

 

[Banana]

Glad it didn't involve replacing the function one by one in all of your queries!

Can you tell that I don't have 2007?

Good luck!