Passing Environ("Username") value to a query-based form 2

[StarScream]

Passing Environ("Username&quot value to a query-based form

I originally had several forms and reports based on the same query which asked you to input your name. What I'd like to do is use the Environ("Username&quot function to determine who the person is and then set the query based upon that value. (Since we're on a network, we all have to login anyway, and it avoids having people type anything in).

How do I pass the value of Environ function to the report or form thats based on the query? Do I remove the field from the query that asks for the name? If so, how does the report or form know to list only records of the person?

I'm trying to do this as code. I tried using it on the OnOpen or OnLoad functions, but it seems to load the query input first. Any suggestions?

PJ

 

[Maquis]

The way I do it is not to allow my users to open up forms and reports directly from the database window. I have a "Main title form" which the users see when they enter the database. That form contains command buttons to open other forms and reports that the users use.

You can then pass in criteria filters to the forms and reports based on the login id.

docmd.openform "Formname",,,"[UserId] = '" & strCurrentLogin & "'"


In my databases strCurrentLogin is a global variable which is set using the Environ command when the database app is started. Maq B-)
<insert witty signature here>

 

[hennep]

PJ,

I have used the Username environment variable in some of my application. Recently I found out that on some windows installations the variable was not set in the login script.
Also a tool named &quot;TweakUI&quot; caused problems when the user cleared the username in the &quot;paranoya settings&quot;.

A better option was the GetUserName API, see the example below.

regards,
Hennie.



Private Declare Function GetUserName Lib &quot;advapi32.dll&quot; Alias &quot;GetUserNameA&quot; (ByVal lpBuffer As String, nSize As Long) As Long

Public Function MyUserName() As String
Dim sBuf As String
Dim lBuf As Long
sBuf = Space(32)
lBuf = 32
GetUserName sBuf, lBuf
MyUserName= Left$(sBuf, InStr(sBuf, Chr$(0)) - 1)
MsgBox Left$(sBuf, InStr(sBuf, Chr$(0)) - 1), vbOKOnly, &quot;Current User&quot;
End Function

 

[StarScream]

Hey Maq & Hennie,

I use a switchboard with which the users select the form or report they are interested in. The forms and reports are based on a query. The query selects records based upon a prompt field &quot;LoginName&quot;.

How can I pass information to the field (LoginName) using the Environ function? Or can I just use the Environ function in a procedure that will act as the query itself and bypass the query altogether? What code can I use to act as a query based on whether a name field is equal to the Environ function value?

Hennie, I'm not gonna have a problem like you mentioned, so this should do just fine if I can get it to work.

Thanks guys,
PJ

 

[Maquis]

PJ, don't worry about incorporating the login name into the query itself. (Unless your users will be running these queries outside of the form). Just open the form with the docmd.openform command I posted above. This will automatically weed out unwanted records from other users in the forms/reports.

If you absolutely have to have the queries look at the login then place your Environ command in the criteria line of the query. =Environ(&quot;Username&quot Maq B-)
<insert witty signature here>

 

[StarScream]

That's a great idea, Maq!

I do need the query because it sorts on other criteria automatically in addition to the name field. I'll try that.

PJ B-)

 

[StarScream]

I got this to work just great, but I realize I need to have a query sort based upon another field of a person. I don't want to set the query criteria to =Environ(&quot;Username&quot, but rather a DLookUp(&quot;Branch&quot;,&quot;Personnel&quot;,&quot;[LoginName] = &quot; & Environ(&quot;Username&quot).

[LoginName] is a field in the &quot;Personnel&quot; table to compare the Environ function with. Based on a match, I want to DLookUp that person's &quot;Branch&quot; and sort on that field. I don't just want the person's records, I want any record relating to his/her &quot;Branch&quot;.

So close, yet so far... B-)

PJ

 

[Maquis]

You need a sub-query. I don't know the correct SQL syntax for this. Perhaps one the SQL gurus lurking around here will read this and provide it.

Something like
Select * from Personnel where branch = (select branch from personnel where loginid = Environ(&quot;Username&quot; Maq B-)
<insert witty signature here>

 

[StarScream]

Hey Maq,

Would this subquery run in the control of the form or report itself as a criteria in the query? I'm gonna play around with it and see what I can get to work. Thanks.

PJ