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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )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.
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.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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.
John TsioumprisSoftware & 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.