Solved

Restricting access to records by using a Login Form

Posted on 2006-11-10
11
259 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access creating a Chart from Search result on Form view 2 21
Help writing a query 6 72
Auto Filter in Combo Box 7 33
MS Access 03, TransferText, decimal places 8 45
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now