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.
If you want to start to use the feature as soon as possible, then you can:
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|
- 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:
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.
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