Restricting Users from Editing Data in Forms

[JithuAccess]

Hello Guys,

Our database in a Network Folder. Is there any method to restrict some users From Editing the Data of some forms? We have Forms for Entering Basic Details of Employers like Name, Employment History etc And we have Forms for Entering the Pay Details of the Employers. what we need is those who are assigned to Enter Basic Details should not have the Right to Edit the Payment Details and those who are assigned to Enter Payment Details should not have the Right to Edit the Basic Details.

Can we do this in Access 2016?

Thanks

 

[Isaac]

You should have a table with users, listing things like their name, local username (on their computer), access levels, etc. Then you can use a dlookup() at runtime to see what the access level is of that logged-in username. This is one out of various ways to handle this, but should get you started.

 

[Gasman]

You can do it in any version of Access.
Store the edit level with the username, then set controls/forms accordingly depending on the edit level.

 

[JithuAccess]

You should have a table with users, listing things like their name, local username (on their computer), access levels, etc. Then you can use a dlookup() at runtime to see what the access level is of that logged-in username. This is one out of various ways to handle this, but should get you started.

Thanks a lot. To be very Honest I don't know how to do this. But I will check in Youtube Tutorials. If you have any Links to how to do this, that will be really helpful.

Thank You

 

[JithuAccess]

You can do it in any version of Access.
Store the edit level with the username, then set controls/forms accordingly depending on the edit level.

Thanks a lot for your reply. Very Happy to know that this can do in Access. Though I don't know anything about how to do this.

Thank You

 

[Isaac]

well the users table would be to study up on the concept of creating a table in access.

collecting their information is a manual process, and involves storing their LocalUsername in a table column. This is their network username, most probably the same one they use to log on their computer each day.

dlookup() you can study - the Criteria portion will be where you use something like environ("username") to find out their AccessLevel (maybe that's a column in a table).

the dlookup would be in an IF statement to decide whether or not to open the form (docmd.openform) or not

HTH

 

[Gasman]

Isaac has given you some details.
I would save relevant details when the user logs on, UserName, EditLevel etc, then refer to those each time you need them, rather than going back and getting them each time.
Basically all it is is a field in your table with a value you will understand to be either Pay or Basic user. You could use a numeric level if there could be a hierarchy at all.?

Also rather than present a load of buttons to open forms, then tell the user they are not allowed to use that form, I would not present the user with those options in the first place? That is a pet peeve of mine.

 

[gemma-the-husky]

You can have two different forms with the functions that you want, and then control access based on the user to the particular form you want them to have. An easier way is to put users in groups, and control the access by group membership

Alternatively, you have a single form, and then have code that changes the form's appearance/functionality depending on the user.

This is one situation where you could have 1 to 1 tables. Put selected fields of the employee record into a separate table. Then you can stop some users having access to the data in the private tables.

It's still awkward and time consuming, and you need to be sure your users keep their passwords private.

 

[Pat Hartman]

There are lots of simple security options that you can code in Access. One that I created uses a variation on the old Switchboard. Each item in the switchboard had a "level" for Add, Change, Delete, Update options. The form itself would need to compare the user's level for the indicated function in the code behind the form. Use BeforeInsert to check "Add" permission level, BeforeUpdate to check "Change" permission level, On Delete Confirm to check delete permission, and Current to check Read permissions.

 

[JithuAccess]

There are lots of simple security options that you can code in Access. One that I created uses a variation on the old Switchboard. Each item in the switchboard had a "level" for Add, Change, Delete, Update options. The form itself would need to compare the user's level for the indicated function in the code behind the form. Use BeforeInsert to check "Add" permission level, BeforeUpdate to check "Change" permission level, On Delete Confirm to check delete permission, and Current to check Read permissions.

Many Thanks for your Help

 

[The_Doc_Man]

I have tried all morning to throw in my two cents' worth.

Have a table with Username, UserRole, and anything else you need to know about specific people.

Logging in to a DB is a once-per-Access-launch kind of event. If you have a login form, there is where you do part of the work. Given the input username, find the appropriate record and pick up anything else you think you might need to know about the user later, including user role. Save this information. There are three places commonly used for this:
1. Public variables in a General Module. Note that this is vulnerable to Code Resets after improperly trapped errors.
2. Create and load some TempVars to remember what you wanted. Not vulnerable to Code Reset
3. Create and load a dictionary to remember what you wanted. Not vulnerable to Code Reset
Pick one.

Now for each form where user role is an issue, have something in the Form_Load routine to read the variables you stored (as noted above) and enable or disable controls according to your needs.

If and ONLY IF you have some data element that interacts with the role in a more dynamic fashion, put your data-dependent control rules in the Form_Current routine.

EDIT: HOORAY! My posts kept getting the Oops error. But at last it let me in.

 

[Isaac]

Doc excellent post.

#4 maintain a hidden form with some stuff dumped on it.

Congrats on getting past Oops - it's been formidable recently!

 

[JithuAccess]

Hello Guys,

Many thanks for all of your valuable suggestions and support.

I think I solved this.

In my Database there isn't any Login Forms. So I can't set the security Level based on users. At first I have created an InputBox for a user to Enter a Password. It was working fine. But I was not able to mask that Input Box with Asterisk (*). I found the code to crack this in google. But since I didn't understand how this code works, I have to choose another option.

When a user click on the Data Entry Form, a new Form "frmPassword" will open and there are only a Text Box and a command button in the Form. I have put the following code in the Command Button.

If Text1 = "HighSecurity" Then
    DoCmd.OpenForm "frmData Entry", acNormal, , , acFormReadOnly
    
ElseIf Text1 = "LowSecurity" Then
    DoCmd.OpenForm "frmData Entry", acNormal, , , acFormEdit

Else
    MsgBox "Wrong Password", vbOKOnly, "Wrong Password!!!"
End If

Me.Text1 = ""
Me.Text1.SetFocus

End Sub

There are two passwords, one for Editing the Entries in the Form and one for just viewing the Entries. It is working the way we want to.

Apologies, this may not be the correct and professional method to solve this.

Thanks

 

[Isaac]

In my Database there isn't any Login Forms. So I can't set the security Level based on users.

Remember, if you are working in a formal corporate type of setting (where each person logs into their machine using highly controlled network usernames and passwords), then you can skip the whole idea of needing usernames and passwords specific to your database. Just write code to grab the logged-in username and set securities based on that.

 

[Pat Hartman]

The sample I posted includes a login form if you want to use it. The reason for using security is to keep people who might "discover" the FE on the server and open it to see what happens. You can control this in a buttoned-down shop as Isaac suggested by using Active Directory to control access to the folders holding the FE and the BE and even to the FE and BE files themselves if the folders might contain multiple Access apps. This is easy enough to do if your IT support staff are helpful. Impossible if they are not.