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