Solved

Restricting access to records by using a Login Form

Posted on 2006-11-10
11
260 Views
Last Modified: 2011-10-03

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?
0
Comment
Question by:Dotrooney
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17920228
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
 

Author Comment

by:Dotrooney
ID: 17920242
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
 

Author Comment

by:Dotrooney
ID: 17920277
Rockiroads, the Manager ID is added to the employee table, which table would the MGR_USER_ID go into?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 65

Accepted Solution

by:
rockiroads earned 250 total points
ID: 17920300
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
 
LVL 4

Expert Comment

by:GordonPrince
ID: 17920767
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
 
LVL 26

Expert Comment

by:dannywareham
ID: 17921191
>>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
 
LVL 4

Expert Comment

by:GordonPrince
ID: 17921220
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
 
LVL 26

Expert Comment

by:dannywareham
ID: 17921231
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17921373
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
 
LVL 4

Expert Comment

by:GordonPrince
ID: 17921416
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
 

Author Comment

by:Dotrooney
ID: 17922448
Thanks everyone for all your help
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dsum Function for List Box Data 7 47
Attachment field in SQL 3 28
Access on Mouse move 5 35
Sum with where criteria on a report 5 16
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question