Database Records By User Login (1 Viewer)

[vcarrill]

Hello,

I need to develop an access database that will only allow users to see records based on their login. Could someone please refer me to a sample database, video, etc on how to get this done.

Thanks you

 

[Ranman256]

when user opens the app, grab the user's ID and store it on the form in a locked textbox :

Private Sub Form_Load()
txtUser= Environ("Username")
end sub

now all queries will use this text box as criteria:
select * from table where [userid]=forms!fMyForm!txtUser

 

[Gasman]

You might want to consider function or dept?
What happens if you are sick and another user needs to process the records you should have worked on?

 

[theDBguy]

Hello,

I need to develop an access database that will only allow users to see records based on their login. Could someone please refer me to a sample database, video, etc on how to get this done.

Thanks you

How do you determine which records to show based on their login? Do you have a field to help identify which records belong to which user?

 

[vcarrill]

How do you determine which records to show based on their login? Do you have a field to help identify which records belong to which user?

Its an employee database that will only show employees by supervisor. So in essence, when Supervisor A or B login, they should only be able to see their assigned employees. Then I need a superuser who can see all employees. I just need to understand how to accomplish this...

 

[theDBguy]

Its an employee database that will only show employees by supervisor. So in essence, when Supervisor A or B login, they should only be able to see their assigned employees. Then I need a superuser who can see all employees. I just need to understand how to accomplish this...

Well, as @Ranman256 tried to demonstrate in post #2, you'll need to have a field ([userid] in the provided example) to compare to the login user information (the result of Environ("Username")).

Basically, you would use a query to retrieve the records you want to display on your forms, and your query will have a criteria against that field to filter only the login user info.

Hope that makes sense...

 

[Pat Hartman]

As Gasman suggested, doing this by SupervisorID is wrong. The supervisor manages a department. Use department instead. And you also need the ability to override so that a higher level manager can see all employees.

 

[vcarrill]

As Gasman suggested, doing this by SupervisorID is wrong. The supervisor manages a department. Use department instead. And you also need the ability to override so that a higher level manager can see all employees.

Hello Mrs Hartman, so yes by department makes sense versus supervisor. Other supervisors would not be allowed to access another supervisors employees, except for the superuser. How do you suggest the override? Thanks

 

[Gasman]

@vcarrill
Pat is female?

 

[Cronk]

Depends on the database design as to whether employees are assigned to a supervisor or to a department. Maybe there is no department entity in the db design. In any case, Environ("Username") returns a string containing the user network logon name, not necessarily the db (probably numeric) userID for an employee.

 

[vcarrill]

@vcarrill
Pat is female?

Corrected, my apologies.

 

[Pat Hartman]

Pat is female?

Pretty soon you're not even going to be allowed to say that I I heard in passing today so I don't have the actual details that Pelosi either made an edict or got her minions to vote on it that members who don't used "approved" pronouns will be sanctioned.

What a world we are dropping into If Pelosi can prevent you from saying she or he, when will she stop you from saying up or down or anything else the left determines is hate speech?

 

[Pat Hartman]

The easiest way to control this is to have an employee table with a departmentID for each entry as well as a supervisorID.

This table is a self referencing relationship since all supervisors are employees but only some employees are supervisors and until you get up to the CEO, even supervisors have a supervisor.

Have each user log in. When the user logs in, you know who is a supervisor by looking in the employee table and don't allow login unless the person is a supervisor. You have to decide how you want to control supervisors who can view other's employees.

 

[vcarrill]

Pretty soon you're not even going to be allowed to say that I I heard in passing today so I don't have the actual details that Pelosi either made an edict or got her minions to vote on it that members who don't used "approved" pronouns will be sanctioned.

What a world we are dropping into If Pelosi can prevent you from saying she or he, when will she stop you from saying up or down or anything else the left determines is hate speech?

Well Ms/Mrs Hartman I look forward to trying to enforce the use of unapproved pronouns. We have so many greater challenges in this world and this is what we want to focus on? Sad.... Thank you very much for your advise and support of my access questions.

 

[Pat Hartman]

You are quite welcome

 

[vcarrill]

when user opens the app, grab the user's ID and store it on the form in a locked textbox :

Private Sub Form_Load()
txtUser= Environ("Username")
end sub

now all queries will use this text box as criteria:
select * from table where [userid]=forms!fMyForm!txtUser

Hello, would the following assist me following your directive? https://www.creativeconnections.co....m-how-to-unlock-a-text-box-on-an-access-form/

 

[Pat Hartman]

Locked is a property of most controls. You don't need code. Just set the locked property. That keeps the user from typing in the username. It always gets the username from the Environ() property.

Personally, I prefer to have the user enter his UserID and password rather than pulling them out of the environment. If I were to go to my boss' computer and she left it unlocked, I could just open the app and it would ASSUME that I am she and give me access to the secure information. Don't use this method if you care at all about security.

Here is a simple login example. A UserName and Password are on the login form for your convenience in testing the app. You can create additional people and log in with their credentials. The actual security is not what you asked for. This security is based on forms and who can modify data in which form. You want to use data values which is different so you would need to modify this example to use it.

 

[vcarrill]

Locked is a property of most controls. You don't need code. Just set the locked property. That keeps the user from typing in the username. It always gets the username from the Environ() property.

Personally, I prefer to have the user enter his UserID and password rather than pulling them out of the environment. If I were to go to my boss' computer and she left it unlocked, I could just open the app and it would ASSUME that I am she and give me access to the secure information. Don't use this method if you care at all about security.

Here is a simple login example. A UserName and Password are on the login form for your convenience in testing the app. You can create additional people and log in with their credentials. The actual security is not what you asked for. This security is based on forms and who can modify data in which form. You want to use data values which is different so you would need to modify this example to use it.

Thank you!!!!