InputBox
By Richard Rost
4 years ago
Get User Input with InputBox in Access VBA
In today's video, I'll show you how to use the InputBox function in Microsoft Access VBA to prompt the user for some input. We will create a Manager Menu form and if the user attempts to open it, he will be asked for a password.
Nasir from Baltimore, Maryland (a Platinum Member) asks: I have a few forms in my database that have some sensitive information on them, like employee payroll data. Is there any way I can password protect just those forms?
Members
Members will learn how to create a password form that echo's asterisks (*) as the user types. This will keep any nosey Nancys in your office from spying your password over your shoulder.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Recommended Course
Pre-Requisites
Links
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, inputbox function, prompt for user input, username, password, Hide Password in Input Box
Subscribe to InputBox
Get notifications when this page is updated
Intro In this video, we will talk about how to use the InputBox function in Microsoft Access to prompt users for a password before opening sensitive forms, such as those containing payroll data. I will show you how to add a simple password prompt to your forms using VBA, handle user input, and prevent unauthorized access to certain areas of your database. We will also cover handling errors when a form is canceled and discuss some best practices for securing your Access applications.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to talk about Input Box - how to use the Input Box function in Microsoft Access to prompt for user input. For example, we'll use it to get a manager password. Today's question comes from NOSER in Baltimore, Maryland, one of my Platinum members. NOSER says: I have a few forms in my database that have some sensitive information on them, like employee payroll data. Is there any way I can password protect just those forms?
Well, yes, you certainly can. The easiest way to do that is to pop up an Input Box where you ask the user to type in a password. Now, Access doesn't have built-in user-level security like it used to back in the old days. In my security seminar, I do teach you how to set up individual user logons, and you can set permissions for everything they can access in the database. But if all you want is a simple manager password for yourself, for maybe one or two other people, and you want to just have a password that prevents the average users from seeing this kind of stuff, that's pretty simple to set up.
Now, before we get started, a couple of things. Even if you don't want to get my full security seminar, where I teach you how to properly lock your database down, at least go watch my Simple Security video. This is a free one, and I teach you how to do things like hide the navigation pane so your users can't go and poke around on all your forms and stuff. At least watch this one, this free video.
Then go watch Intro to VBA. If you've never done any programming, we're going to need a couple of lines of code to set up a password. Go watch this. Don't be scared of VBA. It's 20 minutes long, and it'll teach you all the basics. You're going to have to know "If...Then," how to write an If...Then statement, if the password is right, then do this. So go watch this one.
You're going to need to know what variables are, because we have to store that password somewhere. We're going to store it in memory in something called a variable. Go watch this if you don't know what those are. And finally, if you don't know how to use MessageBox, go use this. This is like the little cousin of Input Box. Input Box lets you type in text. MessageBox just gets a yes or no response, for example, and it's easier to program. Go learn this first if you don't know how to use this, then come on back.
So did you get all that? Did you watch all those videos? We're on the same page now.
This is the TechHelp free template. If you watched all those videos, you know what this is. You can go grab a copy of it if you want to.
Let's say in addition to your main menu, you've got a super secret manager menu. I'm just going to copy this guy, copy and paste. This will be the Manager Menu F. We'll edit you, Design View. We'll change this so it says Manager Menu. Remember, we do it over here in the Caption property, not on the label itself. That way, the label doesn't resize. Nice little trick there.
We'll change some colors around. We'll make this dark red. We'll make the background a dark red, and then we'll go even darker. More colors. Get over here. We'll be really super dark. I'll get rid of this date. We'll change these labels up a little bit. This will be Super Secret Manager Stuff. This one will be Employee Payroll. And this one will be, I don't know, CEO's Diary. This is the stuff that you don't want your end users seeing. Just your managers, just the people who you give the password to.
Now right now, that's why it's important for you to lock this thing down. You have to close that, hide it, whatever you have to do, for my Simple Security. Because if they can come in here and just open stuff up, there's your manager menu.
Let's drop a button on here to open up that manager menu. Copy and paste. Then I'll stick that there and we'll call this the Manager Menu. We'll give it a good name: Manager Button. Let's call it and bold it. Let's make it red so it looks like the rest.
There's the Manager Menu. Now this guy, Build Event. DoCmd.OpenForm "ManagerMenuF".
But wait a minute. Why are we just going to open that form? You don't want to ask for a password or anything here? Well, yeah, we are going to, but we're not going to get the password here. I'm going to get the password when the form opens.
So we'll save this. Let's go to your Design View and go to the form's properties. We're going to click right here where the little boxes or the ruler bars meet, go to Events. Now there's OnLoad and OnOpen. The benefit of OnOpen over OnLoad is that OnOpen can be cancelled. Do you see "Cancel" right there? And in here is where we're going to put our password stuff.
First, we need a variable to hold the password that the user types in. So: Dim S As String. Then we're going to say S = InputBox. Now the Input Box takes a bunch of stuff. The only stuff I really care about is the first two items: the prompt and the title. You don't have to put the title in there, but then it just says Microsoft Access.
We're going to put "Enter manager password" in as the prompt and then just maybe "Password" in as the title. Default is a default value you can start it with. In this particular case, we're not going to start it with something. But maybe if you're asking the user "What's the person's first name?" or something, you can default it to something like "Rick," or what the existing first name is. That kind of stuff. XPosition, YPosition, HelpFile, Context - we don't need any of that stuff. You can control where the Input Box appears on the screen and you can specify a Windows Help file. Don't worry about all that stuff.
Just these two things for now.
If the user hits Cancel, that's going to return an empty string. S will be an empty string like that. Just so you know for future reference. But I don't care if it's an empty string. All I care about is if it's equal to my password or not.
So what do you want the password to be? I don't know. Let's use 599CD for now. So we're going to say: If S <> "599CD" Then... do some stuff.
And if it is the password, it'll just exit out and the form will continue loading.
What happens in here is if they type in something other than the password, we're going to say: MsgBox "Wrong. Peasant.", vbCritical, "Be gone." Okay, we all know how to use the MessageBox.
Now here's the important part. Say Cancel = True. I know Cancel is an integer, but I always refer to Cancel as True or False. It'll be -1 or 0. It works just fine. Basically, Access is looking for zero or not zero. Cancel = True just makes more sense.
At this point, we're going to Exit Sub. And you really technically don't need this because Cancel, when this thing exits, anyway is going to close the form (well, not open the form). But just in case, later on, you put more stuff down here, that's why I put the Exit Sub in there.
So save it. Let's close that. Close that. Now I'm just going to try opening it right from here. Enter manager password. If I hit Cancel, "Wrong, Peasant," nothing opens. Do it again. Type in whatever. "Wrong, Peasant. Be gone." But if I type in the right password, there's my form.
So in this particular case, even if you don't have this thing hidden, as long as you are giving your users an ACCDE file, an encrypted file to run, and not an ACCDB file, then they can't get into your design and they can't see what your password is. And if you don't know what an ACCDE file is, you didn't watch my Simple Security video like I told you to. Go back and watch that.
You give your users an encrypted front end. And remember, every user gets their own copy if you're on a network. You don't want to run the front end in a shared folder. Everybody gets their own. That way no one can get in here.
Now technically, all you really have to do is just put that code that I gave you in any form that is sensitive. So this OnOpen stuff right there - just put this in any form that you don't want them to open without the password.
That will do it for the most part.
Can you set up different user accounts? Yes, of course. That's what I cover in my security seminar, the full one. There are a few steps involved in that. Then I show you how to set up user groups and stuff, so you could say only people in the managers group can open these forms.
But this is a basic, simple password to let people in certain areas of the database.
One thing I do have to mention here though: if you open it from the Main Menu with our button, if you type in the password, it works just fine. But I'm going to slide that over. It's both open now. One problem: if you do come in here and hit Cancel, you're going to get "That OpenForm was canceled." Why? Because if you hit Debug, this line is going to cancel because the OpenForm event was canceled.
So you're going to have to throw in here an "On Error Resume Next," because if they do hit Cancel, you don't want that error popping up.
If you want to learn more about "On Error Resume Next," I have a video for it. Go watch this one on error handling. That just basically says if any error happens with the next command or commands, then just ignore them.
Now if you come in here and try this and the user hits Cancel, you don't get the error message. You still get told "Be gone, peasant," but that's it.
At this point, everybody always asks me, what if I have nosy Nancy sitting next to me and I don't want her seeing my password when I type it in? Can you mask that? Can you have it echo stars like on a bank website? Yes, that involves a little more coding, and I will show you how to do that in the extended cut for the members.
We're going to make our own little password form and a password open form function, and we'll have it so that not only does it echo stars, but once you log in as a manager, you stay logged in. So you don't have to keep entering it, and any form that requires that will just automatically let you in now.
If you want to learn more about the Input Box function, I cover that in my Access Developer Level 3 class.
Everyone always says, why do you have all these videos? Why do I have to bounce around and watch all this different stuff? Because your TechHelp videos, in my full Access Developer course, I teach you stuff in order: Level 1, Level 2, Level 3, and there's no bouncing around. You just learn it in the order you're supposed to learn it.
If you really want to learn how to lock down your database and set up proper user security with user levels and groups and all that stuff, watch my Security Seminar. It's very detailed. You can make a system log, you can track who has access to what, you can set up groups and permissions, all that kind of stuff.
If you want to learn this little guy, come check out the extended cut for the members. Silver members and up have access to all of my extended cut videos. There's lots of knowledge, 300 and some now. Gold members can download these databases and have access to my code vault and get all kinds of cool extra stuff like a free class every month. There are all kinds of cool things you get as a member. So check it out.
If you have any questions, post them down below. I hope you learned something today, and I'll see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free Expert class each month after you've finished the Beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full Beginner courses for every subject, and one free Developer class each month after you finish the Expert classes. These are the full-length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shoutout in the video and a link to your website or product in the text below the video and on my website.
Don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.Quiz Q1. What is the main purpose of using an Input Box in Microsoft Access as described in the video?
A. To prompt users for input, such as a manager password
B. To store sensitive information in the database permanently
C. To create reports automatically
D. To backup the database
Q2. What type of security does Richard recommend for most users who just want a simple password for specific forms?
A. Full user logon system with individual permissions
B. Simple manager password using Input Box
C. Encrypting the entire database
D. Removing all forms from the navigation pane
Q3. Why does the video suggest using the OnOpen event for the password prompt instead of OnLoad?
A. OnOpen can be cancelled before the form fully opens
B. OnOpen is only available on report objects
C. OnOpen loads faster than OnLoad
D. OnOpen disables message boxes
Q4. What variable type is used to store the password entered by the user?
A. Integer
B. Boolean
C. Date
D. String
Q5. What happens when the user clicks Cancel in the Input Box?
A. S variable is set to an empty string
B. The form always opens regardless of the password
C. The application crashes
D. The user is logged in automatically
Q6. Besides the prompt, what is the other most important argument when calling InputBox in Access VBA?
A. Default value
B. Title
C. Context number
D. XPosition
Q7. If the password entered does not match, what happens according to the video script?
A. The form opens anyway
B. A message box appears saying "Wrong, Peasant"
C. The user is redirected to another form
D. The form saves and closes automatically
Q8. What file format did Richard mention should be given to users for better security?
A. ACCDE
B. ACCDB
C. MDB
D. XLSX
Q9. What is the primary purpose of making every user on the network have their own copy of the front-end database?
A. To avoid shared folder issues and increase security
B. To make editing the database easier
C. To reduce the size of the database
D. To enable multiple users to work on the same form simultaneously
Q10. If the user presses Cancel when trying to open a password-protected form, what error can occur and how is it handled?
A. Syntax error, fixed by changing the password
B. OpenForm was canceled, handled by "On Error Resume Next"
C. Database closes automatically, fixed by restarting Access
D. No error occurs at all
Q11. What is suggested if you want to mask the password with stars as you type it in?
A. Use a custom password form instead of InputBox
B. Rename the InputBox function
C. Change the background color of the form
D. Switch to Excel for database security
Q12. Where would you learn to set up complex user accounts and permissions, according to the video?
A. In the Security Seminar course
B. In the Simple Security video
C. In Intro to VBA
D. In the ACCDE file documentation
Q13. What should you watch if you do not know how to use MessageBox in Access VBA?
A. The MessageBox video
B. The Security Seminar
C. The Download lesson
D. The Extended Cut video
Q14. At which membership level do you gain access to download the databases used in TechHelp videos?
A. Gold
B. Silver
C. Basic
D. Bronze
Q15. What is a key difference between InputBox and MessageBox in Access VBA?
A. InputBox lets users type text, MessageBox is for responses like Yes or No
B. InputBox is more secure than MessageBox
C. Both are used only for displaying errors
D. MessageBox stores input as a variable
Answers: 1-A; 2-B; 3-A; 4-D; 5-A; 6-B; 7-B; 8-A; 9-A; 10-B; 11-A; 12-A; 13-A; 14-A; 15-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone explores how to use the Input Box function in Microsoft Access to request user input, such as prompting for a manager password. The question we're addressing today involves hiding sensitive information on certain forms, like employee payroll data, by protecting those specific forms with a password.
In Microsoft Access, the most straightforward way to do this is by showing an Input Box when someone tries to access a sensitive form. While Access used to offer built-in user-level security, that's no longer available in modern versions. However, you can still set up a basic password prompt for these forms. If you need more robust security with custom user accounts, you would need to look into something like my full Security Seminar, where I cover creating proper logins and user groups. For now, though, if all you need is a simple password gate for a manager or a select few, it's very easy to implement.
Before setting this up, I suggest reviewing a few free resources I offer. If you haven't already, watch my Simple Security video. It walks through important basics, like hiding the navigation pane to keep nosy users from browsing all your objects. Then, check out my Intro to VBA video if you're not comfortable with programming in Access. It covers fundamentals like writing If...Then statements which you will need to handle password validation. Understanding variables is also key, since you'll be storing the password temporarily in one. Lastly, if you're not familiar with MessageBox, learn about it too. It's similar to Input Box but doesn't collect typed input; instead, it's for messages and simple Yes/No responses.
Once you're comfortable with those foundations, we can put the password check into practice. For this example, suppose you have a special 'Manager Menu' form in your database (maybe copied and edited from your main menu). You'd likely want to give it a distinct title and appearance to set it apart and use it to house sensitive features like payroll info or confidential notes.
Protecting this form involves adding a password prompt as soon as the form is opened. You do this via the OnOpen event in the form's property sheet. Using OnOpen is best because it allows you to cancel the form's loading if the wrong password is entered. The relevant code uses a variable to store whatever the user types into the Input Box. You decide the password, for example, "599CD". If what the user enters matches, the form opens as normal. If it doesn't, a MessageBox pops up saying access is denied, and the form does not open.
It's important to remember to distribute your application as an ACCDE file, not an ACCDB. ACCDE files lock the design view so users can't easily find or bypass your password in the code. If this is new to you, be sure to review the Simple Security material.
You can set up this password prompt on any form you want to protect. Just copy over the basic structure for the OnOpen event. It's a practical method for basic access control on particular parts of your database.
If you ever want to set up per-user access or more advanced group security, my full Security Seminar covers everything from tracking logins to detailed permission control.
A common issue you may notice: if the form is opened with a button from another form and the user cancels the password prompt, Access can throw an error about the command being canceled. To avoid that, you'll want to use error handling so that the canceled OpenForm command is ignored gracefully.
Another question that comes up is whether you can mask the password input with asterisks so that someone looking over your shoulder cannot see what you are typing. This is possible, but it takes more coding. I cover how to create a custom password form with masking and session management so you only need to enter the password once per session in the Extended Cut available to members.
For more in-depth explanations of the Input Box function and other related programming topics, take a look at my Access Developer Level 3 class. Also, remember that in my full course series, topics are covered in a logical order with no need to jump around between videos.
Members get additional perks. Silver members and higher have access to all my extended cut videos and receive one free beginner class each month. Gold members can download sample databases and get access to my code vault, plus higher priority for TechHelp questions and one free Expert class per month. Platinum members receive all those benefits, along with even higher priority and free access to full-length courses for Access and other subjects.
You can find full details about memberships on my website. TechHelp videos will always stay free, and as long as there's interest, I'll keep making new ones. If you have questions, feel free to post them.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Creating a password-protected form in Microsoft Access
Using InputBox to prompt for manager password
Writing VBA code to validate password input
Setting up OnOpen event for password checking
Displaying error messages with MsgBox for incorrect passwords
Using the Cancel parameter to prevent form opening
Implementing Exit Sub to stop form loading on failed validation
Protecting sensitive forms with manual password checks
Handling OpenForm canceled errors with On Error Resume Next
Giving users encrypted ACCDE files for security
Adding password checks to multiple sensitive forms
Customizing form captions and appearance for manager menus
Ensuring users cannot access design view or see the password |
