Hello Guys,

I use this method and it is working fine but could not understand the role of Administrator Rights and how it works bcz i have try with two user and both find with the same rights.
This Microsoft Access security log on solution will work with all versions of Access

Four steps to create this solution:

Step One
Create a new table called “tblUsers”
Add these fields:
Field Name Data Type


UserName Text
Password Text
Administrator_Rights Yes/No

Make this a hidden table and do not show hidden table in your Access program (See the video below on how to do this.)
http://www.youtube.com/watch?v=kJjQaAWHmG8

Step Two

Create a new module and paste this code into it:

Public user As Variant
Public pw As Variant

Public Function getuser()
getuser = user
End Function

Public Function getpw()
getpw = pw
End Function

Step Three
Create a new query based on this SQL statement:


SELECT tblUsers.UserName, tblUsers.Password, tblUsers.Administrator_Rights
FROM tblUsers
WHERE (((tblUsers.UserName)=getUser()) AND ((tblUsers.Password)=getPW()));

The above is done by going to the SQL View of the query and pasting in the above SQL statement.
Name the query “qryRS”
Step Four

Create a form “frmLogOn”

Add a text box control and call it “txtUserName”
Place this code in it’s After Update event:

user = txtUserName

Add a text box control and call it “txtPassword”
Place this code in it’s After Update event:

pw = txtPassword

Add a button and place this code in it’s Click event:
On Error GoTo errline
If IsNull(Me.txtUserName) Then
MsgBox “Please enter a User Name.”, vbInformation, “Missing User Name”
Exit Sub
End If

If IsNull(Me.txtPassword) Then
MsgBox “Please enter a Password for the user name ” & Me.txtUserName & “.”, vbInformation, “Missing User Name”
Exit Sub
End If

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(“qryRS”)
With rs
If .EOF And .BOF Then
MsgBox “Your log on information was incorrect. Try again.”, vbCritical, “Failed Log On”
GoTo exitline
Else
If !Administrator_Rights = False Then
DoCmd.OpenForm “frmMenu”
DoCmd.Close acForm, “frmLogOn”
GoTo exitline
End If
End If
End With

If chbxEditTable = -1 Then
DoCmd.OpenTable “tblUsers”, acViewNormal
DoCmd.Close acForm, “frmLogOn”
Else
DoCmd.OpenForm “frmMenu”
DoCmd.Close acForm, “frmLogOn”
End If

exitline:


If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

Exit Sub
errline:

Select Case Err.Number
Case Else
MsgBox “There was an error in the program. Please notify database administrator of the following error: Error Number: ” & Err.Number & “ ” & Err.Description, vbCritical, “Please write this error down and note what you were doing at the time.”

GoTo exitline
End Select