Solved

Restricting access to records by using a Login Form

Posted on 2006-11-10
11
261 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

828 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