Access 2003 report security for multiple users

[Guest]

We have 60 sales reps we want to be able to select and view a sales report of
their year-to-date performance.

I have a report created named SalesYTD based on a query.

However, we do not want the sales reps to be able to run this report and see
how other reps are doing.

I was thinking of adding a Criteria to the query of entering their Name to
see their own figures, but then they could also enter someone else's name. So
I was thinking of assigned them each a secret ID code to enter. But then my
report just has their name field, not an ID code field, and I'd have to add
this somehow.

What is the best and simplest way of accomplishing what I want to do? I
would also like to avoid creating 60 separate reports.

 

[Guest]

If the user (sales person) is logging into a network the following code will
work.

Private S1 As String
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA"
(ByVal lpBuffer As String, nSize As Long) As Long
Public Function FindUserName() As String
' Function that returns the name of the currently logged on user
' Example - MyString = FindUserName
S1 = Space(512)
GetUserName S1, Len(S1)
FindUserName = Trim$(S1)
FindUserName = Left(FindUserName, Len(FindUserName) - 1)

End Function

Got this code a long time ago from a newsgroup but don't know who wrote it.
But it works well.

 

[Guest]

Hi Conrad

In what/where would I put that code snippet - it sounds good.

FrankSpokane

 

[Guest]

You would put that into a module. Then as the example comment says set a
variable equal to that function - ex. MyUserName = FindUserName. And you
can Dim that variable, MyUserName as a Global and call it from the forms load
event. It will then be avaible anywhere in your database code the minute the
form loads up.

Happy Coding