User access via Logon screen

[hinchi1]

I know how to create and setup tables for a logon form, and also create the form itself. The DB I have only has two forms, so not too big. What I would like to do is when the user logs in to the DB, check what access they have and only allow the user to see the records that are relevant to them when they open the staff form.



Database Admin can see all data
Users can only see, edit records of the staff they manage
When form is opened the data is filtered to the user access level

Thanks in advance.

[Welshgasman]

So what are you asking?
It would all depends on what the structure of your DB is?

Do you have a way of identifying a user and the records they should see?
If not, you are out of luck.

[mike60smart]

Hi

The attached is a very good method produced by Vlad.

[hinchi1]

Thank you very much

[hinchi1]

So what are you asking?
It would all depends on what the structure of your DB is?

Do you have a way of identifying a user and the records they should see?
If not, you are out of luck.

When the user logs on, using TempVars I can identify their user ID and use this to filter records. Just not sure of the best way to do this.

[CJ_London]

use this to filter records

not a good idea to filter records - a user can remove the filter. Better to apply a criteria to the form's underlying recordsource

[hinchi1]

How do I do this?

[Gicu]

Filtering data is different form the usual "access levels" concept which is used in general to restrict access to certain functionality\controls on forms based on the current user's level. To filter data you would use some sort of criteria in the form's recordsource query to restrict the query's records; for example you would add the user table to the recordsource, join the UserID from it to the UserID field in the data table you want to restrict, bring the UserLevel (or UserRole or whatever you call it) in the query and put Like Forms!frmLogon!UserRole (assumes you hide the logon form so it stays open or use the tempvar approach and reference that). For a regular user set the hidden control to a certain value and for Admin set it to "*" so they can see everything.

Cheers,

[hinchi1]

Do you have any examples please?

[CJ_London]

there are a number of ways to accomplish what you want. Which way is appropriate depends on what you have. And you haven't thought to tell us what that is

You understand what joins are, so what don't you understand about Gicu's suggestion?

[hinchi1]

Thanks for the reply. I will remove sensitive data and post the database as is tomorrow. That should make it clear, hopefully.

[Amicron]

This is how I do it: User Level Security

[hinchi1]

Thanks Amicron
I watched this today, very useful. I'm probably over thinking this. Will give it some more thought this next week.

[Gicu]

@amicron - what you're showing is what I mentioned in my original post - controlling what a certain user can do (open forms, click buttoms, view textboxes, etc.). I have a free utility that does that using a custom system table so it uses record based "access rules" rather than VBA hardcoded rules.
https://forestbyte.com/ms-access-uti...access-levels/
What the OP wants is a totally different thing, the need here is to restrict data to a certain subset (so for example a manager can see only their department data). You mention that your third video is showing how to restrict the data for the current user's own records (probably by storing that in the table with a default from the login form), which is closer to what OP wants but still not quite. Restricting data can only be done by manipulating the form's (or report's) recordsource. With an Access back-end that can only be done by criteria in a query; for SQL Server, MySQL, Oracle and similar you can use views in the back-end to enforce the data based on the login permissions.

Cheers,

[hinchi1]

Hi Gicu
Thanks for this. The DB is very basic and doesn't require too much security. I will look at this tomorrow, hopefully I will understand it.