Restricting access to records by using a Login Form


I have a Tbl Employees which has the employees name and a password. When entering the database I have a login form where they select their name and enter their password. In the main details form they can then only see their own information:
SELECT [Tbl Employees].[Employee ID], [Tbl Employees].Password, [Tbl Employees].Surname, [Tbl Employees].[First Name], [Tbl Employees].Branch FROM [Tbl Employees] INNER JOIN [Tbl Login] ON [Tbl Employees].[Employee ID]=[Tbl Login].[Employee ID];

Now I have a problem where I want the Director of each branch to be able to see all their employees details without entering the employees password. Is there any way I can do this using what I have now?
DotrooneyAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
u put in a password?
what do u mean?

I am assuming the director/manager also have records in your employee table

e.g.

say u had a employee id's 1,2,3,4,5
and a directory with a employee id of 15

this means the mgr id for employee id's 1,2,3,4,5 is 15


Now when directory logs in, say he/she wants to see a list of employess under him/her

u make a note of their id, which is 15, this u get from login

then u can simply query the database, list all employess whose mgr_user_id = 15
0
 
rockiroadsCommented:
U could do with adding a Manager ID in your employee table, each employee reports to a manager right?

your table  Tbl Employees

has the following

Employee ID
Password
Surname
First Name
Branch

now add in

Manager ID


or if they have more than one manager

tblEmployeeManager
Employee ID
Manager ID


so now when a manager logs in, they can see the details of people under them by

select * from [tbl employee] where [manager id] = MGR_USER_ID

or do a query based on tblEmployeeManager

u can then create a subform or listbox and load up details that way
0
 
DotrooneyAuthor Commented:
This sounds the way forward but I'm getting myself a bit lost. I have added a Manager ID into my employee table and put in a manager password for the relevant records he needs to see. Do I need to change my login form to include an employee ID and a manager ID? Would it just let you enter a Manager ID and leave the employee ID blank?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
DotrooneyAuthor Commented:
Rockiroads, the Manager ID is added to the employee table, which table would the MGR_USER_ID go into?
0
 
GordonPrinceCommented:
To flesh out rockiroads comment, if you add a column "UserName" to a table, you can read the manager's UserID by putting code into something that happens when the application starts (e.g., when the MainMenu is opened, etc.).
"select UserID from tblEmployeeManager where UserName = "" & Environ("UserName") & """

Then use that to select just the employees where the ManagerID=15 (from the example above).
0
 
dannywarehamCommented:
>>if you add a column "UserName" to a table
Don't add a column called "username" - call it something else like "strUserName"

The word "username" is used in Access and can cause confusion further down the development line.
:-)

0
 
GordonPrinceCommented:
Left out a " from the previous posting. Should be:

"select UserID from tblEmployeeManager where UserName = """ & Environ("UserName") & """"

to make a proper string.

dannywareham: Hadn't thought about the UserName column name. It's probably a good suggestion to not use a reserved word to name something else. I've just always used it because I can't remember between UserName, Login, etc. But I think you're right.
0
 
dannywarehamCommented:
No problem.

Just for ease, you might find this easier to read:

mySQL = "SELECT UserID FROM tblEmployeeManager WHERE strUserName = " & chr(34) & Environ("UserName") & chr(34) & ";"

I generally find CHR(34) easier to follow - although it does extend your string a little....
0
 
rockiroadsCommented:
No offence lads but why do u want to store the username in tblEmployeeManager?

that is what ID's are for

Ideally you should be storing EmployeeID and ManagerID, both of which are IDs in the employee table

When a user logs in, the userid has to be stored or reread in order to display the employees under him/her
0
 
GordonPrinceCommented:
Or you can make global variables in your application

Public Const QUOTE As String = """"               ' Quote
Public Const QCQ As String = """" & "," & """"  ' Quote Comma Quote

and use those to build strings. Etc. :)
0
 
DotrooneyAuthor Commented:
Thanks everyone for all your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.