Daily Access Tips

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

A demononstration of this solution is here:
http://www.youtube.com/watch?v=0RwU1roBJ6I

Hide Table Video:
http://www.youtube.com/watch?v=kJjQaAWHmG8

from youtube
Log On Demo:
http://www.youtube.com/watch?v=0RwU1roBJ6I

from youtube

Bonus article from Smart Access

Even some experienced Access developers shy away from writing SQL directly. That’s a shame, because, unless you’re willing to write SQL, you can’t use subqueries, which are a powerful tool for solving some especially thorny data retrieval problems. In this article, Mike Gunderloy introduces subqueries and shows how you can use them in Access.

http://www.vb123.com/smart/go.asp?rfdby=myap