This post is a compilation of two of my previous posts:
http://vbahowto.com/ms-access-object-security/
http://vbahowto.com/ms-access-login-form-revised/
This elaborates on the two posts a bit:
First, here are all of the database objects:
There are 2 tables here:
tblLogin and tblAdmin
Here are the main forms:
frmLogin – this is the form that opens when the database opens
Here is the code that gets executed when the “Login” button is clicked:
Private Sub btnLogin_Click() Dim rst As Recordset Dim strSQL As String strSQL = "SELECT UserName, UserPWD FROM tblLogin WHERE UserName = '" & Me.txtName & "' AND UserPWD = '" & Me.txtPWD & "'" Set rst = CurrentDb.OpenRecordset(strSQL) If Not rst.EOF Then 'open m_LoggedInUser = Me.txtName DoCmd.Close acForm, Me.Name MsgBox "Welcome: " & m_LoggedInUser DoCmd.OpenForm "frmMain" Else MsgBox "Your password is wrong" End If End Sub
The way it works is that the “tblLogin” table is checked for the existence of the user entered into the form. If a user record is found, they can proceed.
THIS FORM IS LOADED AS A DIALOG FORM WHEN THE DATABASE IS OPENED:
Once that form is passed, the user goes to the main user form, “frmMain”
The administrators form is called “frmAdmin”
Here the admin can add and modify user access rights to the forms and reports.
Here is the code:
Private Sub lblPolicy_Click() DoCmd.OpenTable "tblAdmin" End Sub Private Sub lblRegister_Click() DoCmd.OpenForm "frmRegister" End Sub Private Sub lblCurrent_Click() DoCmd.OpenTable "tblLogin" End Sub
This form will open another form called “frmRegister”, which will allow you to enter new users:
This is the code that executes when you click the “Register” button:
Private Sub btnclose_Click() DoCmd.Close acForm, Me.Name End Sub '================= 'frmRegister code: '================= Private Sub btnOK_Click() Dim strNewUser As String Dim strPassword As String Dim strSQLInsert As String strNewUser = Me.txtUser strPassword = Me.txtPassword strSQLInsert = "INSERT INTO tblLogin (UserName,UserPWD) VALUES ('" & strNewUser & "','" & strPassword & "')" CurrentDb.Execute strSQLInsert MsgBox "Thanks for registering!" DoCmd.Close acForm, Me.Name End Sub Private Sub txtPassword_AfterUpdate() Me.lblPassword.Caption = Me.txtPassword End Sub
That’s about all. I’ll load the database for you all here below.
Let me know if you need help.

Here is the database access database users and permissions.accdb