Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Restricting access to records by using a Login Form

Posted on 2006-11-10
11
Medium Priority
?
267 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 65

Accepted Solution

by:
rockiroads earned 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

824 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