Microsoft Access forms and passwords

Ok i Have a access database where i have people inputting data into a form.  I locked 2 of the fields.  Only someone with a password can enter into those fields.  What I would like is if the user clicks in those fields then a password prompt comes up and they will be able to enter or change the data in those fields?  Can that be done?

Who is Participating?
John TsioumprisConnect With a Mentor Software & Systems EngineerCommented:
You can also put a login form on the application and prompt once the user for the password...after that depending on the permission you granted you can either allow or disallow the entry...keep fields hidden or whatever you need.
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You can use the Enter or GotFocus event to show an Input box. The user would enter the password in the Input box, and if it does not match you could set the textbox.Locked = True. This prevents users from modifying the data.
This prompting will eventually annoy the users.  Especially if you are going to do it once for each field.  A better design would be to have all users log on.  Assuming the login is valid, the login form hides itself rather than closing and opens the main menu.  Then in the BeforeUPdate event of EACH secured field, check the security level on the login form and either allow or deny the change.  Only one password entry will be required regardless of how many secured field on secured records will be updated.

If Forms!frmLogin!txtLevel > 7 Then
    Msgbox "You are not allowed to change this field.",vbOKOnly
    Cancel = True
    Exit Sub
End If

Open in new window

This requires a little more sophistication than you are asking for but it will not annoy the users and it can be expanded.  To implement, create a table with a userID, password, and security level.  You will need a maintenance form that is only valid for users with a very high security level.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

jpb12345Author Commented:
Yeah this is  a small database that only one user will be entering data into these 2 fields (a few records a week).  I just want to keep them locked so the regular users don't put anything into these fields.  Realistically the special user can put data into the back end table for these fields.
PatHartmanConnect With a Mentor Commented:
Realistically the special user can put data into the back end table for these fields.
Not in my world.  Size is irrelevant.  If data is important enough to collect.  It is important enough for us to ensure it as valid as we can make it.  No user should ever have access to tables or queries.

You don't need to keep the field locked.  You simply need to prevent the data from being saved.  If you can't be bothered to work out something more convenient for the user, then prompt  as Scott suggested.  PS, I'm not sure you can lock a box when it has the focus.  So you would actually need to do something different.  In my opinion, the BeforeUpdate event is the best answer.  You don't have to fiddle with focus.  You don't have to lock anything.  If the user cannot provide the necessary password, you simply undo his change and cancel the update as I showed in the earlier code I posted.
I believe that was my first suggestion.
John TsioumprisSoftware & Systems EngineerCommented:
The difference is that i am not doing any checking...valid users have complete access and everything is visible...the others won't see anything
John TsioumprisSoftware & Systems EngineerCommented:
the solutions provided cover all the cases
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.