I use Access 2000 and do the following which works great:
**********MODULE********** (called LogIn with following coding)
Option Compare Database
Option Explicit
Public lngMyEmpID As Long
' Below moved to Gloval Variables Module
' Global intLogonAttempts As Integer
Public lngMyLogInID As Long
**********MODULE********** (called GlobalVariables, which includes following coding for use with my log in form, but as above could include it in the LogIn module)
Global intLogonAttempts As Integer
**********TABLE********** (User Name and Password table called - tblEmployees)
There are three Name Fields:
lngEmplID Data Type: AutoNumber
Field Size: Long Integer
New Values: Increment
Indexed: Yes(No Duplicates)
strEmpName Data Type: Text
Field Size: 50
Required: No
Allow Zero Length: No
Indexed: No
Unicode Compression: Yes
strEmplPassword Data Type: Text
Field Size: 20
Input Mask: Password
Required: No
Allow Zero Length: No
Indexed: No Unicode Compression: Yes
**********LOG IN FORM**********
On my log in form I have two labels, a combo box, a text box, and a command button (I use a picture graphic for the command button which shows a button with the word ENTER on it)
There is a label ("lblEmployeeName") with text "User Name:" followed by a combo box ("cmbxEmployeeName"). This is coded to allow either to begin typing in an Employee/User Name so auto fills or by the Employee/User Name by selecting with the drop down of the comb box (names from the table), with following coding:
Format Tab (below coding plus my formatting - ensure width of comb box is sufficient for longest Employee/User Name to fully appear) :
Decimal Places: Auto
Column Count: 3
Column Heads: No
Column Widths: 0";1";0"
List Rows: 6
List Width: Auto
Visible: Yes
Display When: Always
Data Tab:
Row Source Type: Table/Query
Row Source: SELECT [tblEmployees].[lngEmpID], [tblEmployees].[strEmpName], [tblEmployees].[strEmpPassword] FROM tblEmployees ORDER BY [tblEmployees].[strEmpName];
Bound Column: 1
Limit to List: Yes
Auto Expand: Yes
Enabled: Yes
Locked: No
Event Tab:
After Update - Event Procedure/Code
Private Sub cmbxEmployeeName_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub
On Not in List - Event Procedure Code
Private Sub cmbxEmployeeName_NotInList(NewData As String, Response As Integer)
MsgBox "Please re-enter.", vbCritical, "User Name Not in List!"
Me.cmbxEmployeeName = ""
Me.cmbxEmployeeName.SetFocus
End Sub
Then below that is a label ("lblPassword") with the text "User Password:" followed by a text box ("txtPassword") with PASSWORD Input Mask, for them to type in the password - ensure the width of the text box is sufficient for longest possible password - strEmplPassword Text Size.
For the All Tab for textPassword:
Allow Auto Correct: Yes
Enabled: Yes
Locked: No
Filter Lookup: Database Default
I then use the Enter command button to check the two fields, including case sensitive password check. This allows use of upper and lower case and special keys (such as $%^&*() etc.) in the password.
Private Sub btnEnter_Click()
'Check to see if User Name entered in combo box
If IsNull(Me.cmbxEmployeeName) Or Me.cmbxEmployeeName = "" Then
MsgBox "You must enter a User Name.", vbCritical, "User Name Missing!"
Me.cmbxEmployeeName.SetFocus
Exit Sub
End If
'Check to see if data is entered into the User Password box
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter User's Password.", vbCritical, "User Password Missing!"
Me.txtPassword.SetFocus
Exit Sub
End If
' Verification of case-sentive password data
Dim i As Integer
Dim Counter As Integer
Counter = 0
' Start of GTS verification code
If cmbxEmployeeName.Column(2) = txtPassword Then
For i = 1 To Len(txtPassword)
If Asc(Mid(cmbxEmployeeName.Column(2), i)) <> Asc(Mid(txtPassword, i)) Then
Counter = Counter + 1
End If
Next
Else
Counter = 1
End If
If Counter < 1 Then
MsgBox "Password Verified.", vbExclamation, "Password Verification Successful!"
gsEmployeeName = cmbxEmployeeName.Column(1) 'NOTE - I use the global variable called gsEmployeeName for use by another module to record in a different table use of subforms by an Employee/User, so should delete from coding
Me.Parent.MainMenu.SourceObject = "fMainForm1_MainMenu_SubForm" 'NOTE: I use a Main Form and load Subforms into and out of the Main Form; if you use Form to Form, then use the coding to load your next Form
Else
MsgBox "Please Re-Enter Password.", vbCritical, "Password Verification Failed!"
Me.txtPassword = ""
Me.txtPassword.SetFocus
End If
' End of GTS verification code
'If User Enters incorrect password 3 times database will shutdown
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "Security Block: Too Many Attempts to Log In - Program Locked! You do not have access to this database. Please contact your program administrator. Program will close.", vbCritical, "Entry Prohibited!"
Application.Quit
End If
End Sub
For the Form:
All Tab:
Record Source: tblEmployees
Allow Filters: Yes
Default View: Single View
Views Allowed: Both
Allow Edits: Yes
Allow Additions: Yes
Allow Deletions: Yes
Data Entry: No
Recordset Type: Dynaset
Record Locks: All Records
Record Selectors: No
Navigation Buttons: No
Dividing Lines: No
Auto Resize: Yes
Auto Center: No
Pop Up: No
Modal: No
Cycle: All Records
Has Module: Yes
All Design Changes: Design View Only
*****
Hope this helps.
