Filtering by logon (1 Viewer)

ianacole

Registered User.
Local time
Today, 09:34
Joined
Nov 26, 2001
Messages
26
I have a report that lists current contacts for a sales rep. I want it to filter this report by the logon name of the individual, so that they only see thier contacts. Any thoughts on how to perform this?

Thanks,

Ian
 

David R

I know a few things...
Local time
Today, 04:34
Joined
Oct 23, 2001
Messages
2,633
Base your report on a query that has the criteria set to "SalesRepField = " & fOSUserName() (or whatever you're using to get the user's login name).

HTH. If I didn't understand your question, please post back.
David R
 

ianacole

Registered User.
Local time
Today, 09:34
Joined
Nov 26, 2001
Messages
26
I need some help even further up front. How do I identify (or associate a variable) to the logon user name? Then I can set a filter to report against that field. Is this something I can write to a table in the background, something along the lines of CurrLogon as a text field that has the logon ID written to it?
 

David R

I know a few things...
Local time
Today, 04:34
Joined
Oct 23, 2001
Messages
2,633
Caveat: Because my security needs are not too strenuous, and because I've heard Access Security is arcane at best, I use a function someone gave me to determine the Network username from the Windows Registry. This works on Win98 and Win2k that I've tested so far.

Go to the Modules tab, click on New. Paste this in exactly as written:
Code:
Option Compare Database

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

Now you can use fOSUserName() (or as a control source, for example, =fOSUserName() )to stand for your user's login name to the computer. There is a simpler way using Environ$() if everyone has Win2k or greater, but this has worked without problems so far.

Btw: It doesn't matter what you name the module when you save it. Only the function name matters.

HTH,
David R
 

David R

I know a few things...
Local time
Today, 04:34
Joined
Oct 23, 2001
Messages
2,633
If you're using Access workgroups, I'm afraid I won't be able to help you much. Perhaps someone else can or make use of the search function on this forum.
You can also search for "environ user name" to see what that function does. It's very useful, but it does require a newer operating system.

Good luck,
David R
 

ianacole

Registered User.
Local time
Today, 09:34
Joined
Nov 26, 2001
Messages
26
Thank you so very much. I made a slight addition to the code, as I am using the full name in the field rather than a truncated logon windows name. If interested, here is the code:

Option Compare Database

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
Dim strCurrLogon As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If lngX <> 0 Then
strCurrLogon = Left$(strUserName, lngLen - 1)
Else
strCurrLogon = ""
End If

If strCurrLogon = "icole" Then
fOSUserName = "Ian Cole"
ElseIf strCurrLogon = "jmead" Then
fOSUserName = "John Meade"
ElseIf strCurrLogon = "dmolloy" Then
fOSUserName = "Desi Molloy"
ElseIf strCurrentLogon = "gmatheny" Then
fOSUserName = "Greg Matheny"
ElseIf strCurrentLogon = "sjohnstone" Then
fOSUserName = "Sean Johnstone"
End If

End Function
 

David R

I know a few things...
Local time
Today, 04:34
Joined
Oct 23, 2001
Messages
2,633
That looks like it would work beautifully, Ian. However, if your team is ever going to change you don't want to have to re-hardcode everything.
Make a separate table, as long as you can secure your tables from changes by users, hide it if necessary, and create entries for each person. Make a simple form, password protected to you, for adding/removing/changing entries.
First field: username
Second field: Full Name

HTH,
David R
 

ianacole

Registered User.
Local time
Today, 09:34
Joined
Nov 26, 2001
Messages
26
How would I then equate the logon name to the appropriate row to return the username in the fOSUserName() string?
 

David R

I know a few things...
Local time
Today, 04:34
Joined
Oct 23, 2001
Messages
2,633
Y'know, I've never actually done it in code, so I had to think about it. I was thinking along the lines of what the lookup wizard does. I know you can get an unbound combo box to display whatever you want, and yet store the actual login name in the table. That may be a solution.

My understanding of the process is that you can do this with Dlookup or a multi-column combo box. Try this topic to see if it helps any: http://www.access-programmers.co.uk/ubb/Forum1/HTML/001676.html

Sorry I wasn't more help.
David R
 

Fornatian

Dim Person
Local time
Today, 09:34
Joined
Sep 1, 2000
Messages
1,396
Please don't post under more than one topic as you have a similar thread in another section of this forum which I have answered
 

Users who are viewing this thread

Top Bottom