A login form is a must for databases that contain sensitive information. Follow the steps below so you can create your own login form to protect your database from invalid access.
Download Now
If you want to start to use the feature as soon as possible, then you can:
Download the Sample Database with VBA Codes Now
Otherwise, if you want to DIY, you can read the contents below.
Steps to Create a Login Form in Access
1. Create a login table
The first thing that we need to do is to create a table that will store all the login information.
- Create a new table and name it as tbl_login.
- Open the table in design view.
- Add the following fields with their corresponding data types:
| Field Name | Data Type |
| UserID | AutoNumber |
| FirstName | ShortText |
| LastName | ShortText |
| UserName | ShortText |
| Password | ShortText |
- Edit the password’s input mask so that it will be displayed as a series of asterisks (*).
- Select the Password field.
- Go to the General tab > Input Mask > …
- When the Input Mask Wizard pops up, select Password.
- Click Finish.
- Open the table in Data Sheet view and enter the first record (your login details).
- Close the table and save the changes made.
2. Create the Login form
Create a blank form with 2 textboxes and 2 command buttons (as shown below). Skip the command button wizard.
We are going to refer to these form controls in our code later so it would be best to rename them so that they can be easily identified:
- txt_username
- txt_password
- cmd_login
- cmd_cancel
Next, change the input mask of the password textbox so that it will also be displayed as a series of asterisks (*).
- While in design view, select the password textbox.
- Open the Property Sheet (ALT + Enter).
- Go to the Data tab > Input Mask.
- Select Password and click Finish.
Now, we are going to change some of the properties of the form for aesthetic purposes and most importantly, for making sure that users cannot bypass this form without entering the correct login details.
- While in design view, select the form (as shown below).
- Open the Property Sheet (ALT + Enter) and update the following properties:
- Under the Format tab
- Caption: Login
- Record Selectors: No
- Navigation Buttons: No
- Scroll Bars: Neither
- Border Style: Dialog
- Close Button: No
- Under the Other tab
- Pop Up: Yes
- Modal: Yes
- Shortcut Menu: No
- Save the form as frm_login.
3. Add the code
Add an On Click event to the Login button.
- While in design view, select the Login button.
- Go to the Property Sheet > Event Tab > On Click > …
- Select Code Builder from the Choose Builder menu and click OK.
- This will open the VBA Editor.
- Copy the code below.
Private Sub cmd_login_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, title:="Username Required"
Me.txt_username.SetFocus
Exit Sub
End If
If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, title:="Password Required"
Me.txt_password.SetFocus
Exit Sub
End If
'query to check if login details are correct
strSQL = "SELECT FirstName FROM tbl_login WHERE Username = """ & Me.txt_username.Value & """ AND Password = """ & Me.txt_password.Value & """"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
If rst.EOF Then
MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="Login Error"
Me.txt_username.SetFocus
Else
MsgBox prompt:="Hello, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, title:="Login Successful"
DoCmd.Close acForm, "frm_login", acSaveYes
End If
Set db = Nothing
Set rst = Nothing
End Sub
This code will:
- Check if the username and password fields are not left blank. If they are, a prompt will appear.
- Check if the login details are correct by comparing them with the values in the login table (tbl_login).
- Once the login details are verified, a prompt will appear welcoming the user.
The next step is to add the code for the Cancel button so that once it is clicked, the database will close.
- Add an On Click event to the Cancel button (same procedure with the Login button).
- Copy the code below.
Private Sub cmd_cancel_Click() Â Â DoCmd.Quit acQuitSaveAll End Sub
4. Set the Login Form as the Display Form
Finally, set the Login Form as the default form that users will see upon opening the database.
- Go to File > Options > Current Database.
- Set Display Form to frm_login.
- Click OK.
You will have to close the database and reopen it to see how it works.
Recover Your Access Database
Congratulations! You now know how to protect your precious database from invalid access. Now, I’m going to give you an extra tip. If, for an unfortunate reason, your database has been corrupted, DO NOT panic. DataNumen Inc., an expert in data recovery technologies, provides a software that repairs corrupted Access database. Your database can still be recovered.
Author Introduction:
Jayme Stack is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit www.datanumen.com
1
This site really has all the info I wanted about this subject and didn’t know who to ask.
Works nicely, now how to restrict what they see is the next step??
Is this coming……
o.web20.services
the page is a scam,lost all my tables.a whole 3 months work
Hi Works perfectly, thanks. I’ve been looking for a way to protect my DB for a while and this was the easiest to do. I would like it to open another form when the login form closes. Can you tell me what code needs to go at the end of the login?
It absolutely works. i have successfully protected my database…..
I simply followed all the steps and created my own work (not use sample) and it works….
despite i am not a ITechy….
you can use sample too, but have you tweeked it to suit your database first ???
Good morning to all. Can someone give us and example of database where the code works? i tried it to no avail.
. I cannot open the Access file. It took me 3 hours create tables and forms but now I lost the database. Please help or it is a trap
Reply
User Name : johnjac
Password : johnjac
Hi, user: johnjac; Password: johnjac
My database won’t open up
PLZ Downloaded file
user name and password
Not work
Works perfectly thank you for this 🙂
Again for someone whose loosen to do anything for the sample that you’re activated and failed for running, don’t worry just change or rename the sample login and reopen again do as the suggestion below.
Suggestions for someone who download sample to test run on your computer.
1.don’t hurry to activate content, made right click on frm_login and choose close to close this form and go to tbl_login open it and change data in records that you need and easy to remember it then turn back to activate content on the top you can use this login as well. Good luck to you all.
you got to love people that dont read. he gives you the password in step 1.5 and if you download the sample database and just copy it into yours then it will work!!
I have lost my the work..,the log in is not working
Sure… mine too the same…it not working…its not allowing me to open the work again
I’ve done everything listed but when I try to test this out, I keep getting Run-Time error ‘3061’ Too Few parameters. Expected 1. When I click on the debug option, it highlights: Set rst = db.OpenRecordset(strSQL) as the culprit. Problem is, I’m not sure what I’m supposed to do here as I am not good with coding at all. I see some recent comments saying that it worked for some, would anyone happen to know how to fix this?
I have lost my Database bcz this code locked my file.
This is so amaizing. Has provided the solution I have been looking for over 3 days. Thumbs up
Hey i got a question
The code works fine for me, everything is ok, but i want to add a feature and i don t know to code
So i want that after the Login is successful i want it to auto open another form, how do i do this ?
WOW
Josh- I have begun the build of a new database with your front end – however, certian functions are not usable, like in the OLD object, where you right click to add and item, it doesnt work for anyone but myself, and only on my machine – if we login to someone elses computer, and login with my access it then refuses to allow the Right click as well. Only through the machine I built it on can I actually do it.
Do you have any information on why this is occuring I am sure its just a setting on the DB Options.
Hi Joshua Wolski, Really Good, Thank you for sharing these steps with us. Keep it Up.!!
i dont recommend this…….i lost my files and information.
i was unable to open nor clse the database
what is the username and password for the demo?
Hello
After using the code, it gives error and I can’t close the database neither do I work on it. What do I do
This is to Joshua Wolski, Hi this is Mark and for a while I was working on my database. Got a lot done. I wanted to password secure it so I used the above code and instruction. I did secure it but to my shock there is not password popup to enter. Wow did I get pissed. I was about to purchase this software to recover my database until I scrolled down and read your first post. Hold down the shift key and then you can work on it. I did and I was able to delete that one form. Now I got it back and no more secure locked out. Thanks for posting that tip.
Another way I have found is toprevent the code from executing on open, by holding shift when you open the access Database. As long as you havent disabled this feature. Also do not set your PW to blank for testing, as the code trims the text value and looks for vbnullstring, if vbnullstring the code says you have to enter a value.
to close the form. Make sure the form has the same name in the code and as the form. When the code debugs, move the code line down to DoCmd.Close acForm, “tbl_login”, acSaveYes and debug through the code. It will close the form and give you access again
If the form is not closing make sure your form name is the same as in the code. I just fixed that the closign issue myself noticing that the two name were differnt. in the code :
Else
MsgBox prompt:=”Hello, “, buttons:=vbOKOnly, title:=”Login Successful”
DoCmd.Close acForm, “tbl_login”, acSaveYes
End If
make sure DoCmd.Close acForm, “tbl_login” <——–make sure it doesn't say "frm_login"
Yeah wow locked out now to! Wish I read these comments before and
Hi,
The login form is not really work.
After login successfully with greeting, the login form is not close. Nothing happen. I cannot open the Access file. It took me 3 hours create tables and forms but now I lost the database. Please help or it is a trap