Case Study: Coding a Login Interface

This case study shows one approach to coding a login interface with Access. It uses both standalone class modules and form class modules. The login process and the class module contents use coding techniques that are applicable to any task requiring the use of data with unbound forms.

To highlight the role of class modules and to keep the process transparent, the sample does not use built-in Access security. Instead, it relies on a pair of tables and three forms. The Passwords table has just two fields: EmployeeID and Password. The Employees table, which is imported directly from the Northwind database, contains EmployeeID as a primary key along with other business, personal, and contact information about employees. The three forms refer to the contents of these tables to manage the login process.

NOTE

---

The case study excludes error trapping, which any operational system should have. Error trapping is especially important if the system is dependent on user input. After you learn the basics of class modules from this chapter, you might want to review the information in Chapter 1 about error-trapping logic.

The First Login Form

Figure 7-1 shows the first form, along with two message boxes that it can generate. A user enters values into both text boxes on the form and clicks Let Me In. If the password matches the saved one for the EmployeeID field, the application presents a welcome message box. If it does not match the stored password for an employee ID, the user can try again or change the password.

Figure 7-1. The first login form with two possible reply messages.

The password mask

In addition to the code behind the form and the class module invoked by the form, you should closely examine the Password text box. It has a password mask that displays an asterisk for each character entered in the box. You assign this mask to a text box from the Data page of its Properties dialog box. Click the Build button next to the Input Mask property to open a dialog box that lets you select it.

The code behind the form

The module behind the form, shown below, contains three event procedures—one for each button. The Exit button merely closes the form. The Change Password button opens a second form and copies the value of a field from the current form into it. The procedure that opens the frmWhoAmI form also moves the focus to an empty text box. Then it closes the current form.

The Let Me In button invokes a standalone class module (MyTestClass3). Notice that the procedure passes the contents of its two text boxes to the cpw method procedure in the class module. This module looks up the password for the employee ID and determines whether it matches the password on the form. The class replies with one of the two possible messages. (See Figure 7-1.) The class module simplifies the code in the event procedure. This points to another benefit of class modules—they facilitate team development. Advanced developers can write more involved procedures in class modules, and beginning developers can perform basic development tasks and simply reference class modules to incorporate advanced ones.

Private Sub cmdExit_Click()     DoCmd.Close End Sub Private Sub cmdNewPassword_Click()     DoCmd.openform "frmWhoAmI"     Forms("frmWhoAmI").txtEmpID = Me.txtEmpID     Forms("frmWhoAmI").txtHireDate.SetFocus     DoCmd.Close acForm, "frmInputPassword" End Sub Private Sub cmdLetMeIn_Click() Dim PWT As New MyTestClass3     PWT.cpw Me.txtEmpID, Me.txtPassword      End Sub 

Invoking the class module

The cpw procedure in MyTestClass3, shown below, uses a parameter query to look up the password for an employee ID in the Passwords table. One of the two arguments passed to the procedure is the employee ID. The procedure sets its parameter equal to the value of this argument. After executing the Command object with a select query, the procedure assigns the return set to a Recordset object. Since the EmployeeID field in the Passwords table is a primary key, the select query always returns a single record.

The cpw procedure closes by comparing the password returned by the query with password typed on the form as the condition of an If...Then statement. If there is a match, the procedure welcomes the user into the application. In practice, you open another form or some other database object to which you are restricting access with password security. If there is no match, the procedure asks the user to resubmit the password or change the password.

Sub cpw(empid As Long, pw As String) Dim cmd1 As Command Dim strSQL As String Dim prm1 As ADODB.Parameter Dim rst1 As ADODB.Recordset 'Assign the command reference and connection.     Set cmd1 = New ADODB.Command     cmd1.ActiveConnection = CurrentProject.Connection 'Write out SQL statement with parameters & assign to cmd1.     strSQL = "Parameters Secret Long;" & _         "Select EmployeeID, Password from Passwords " & _         "Where EmployeeID=Secret"     cmd1.CommandText = strSQL     cmd1.CommandType = adCmdText          Set prm1 = cmd1.CreateParameter("Secret", adInteger, adParamInput)     prm1.Value = empid     cmd1.Parameters.Append prm1 'A handy line for catching SQL syntax errors '    Debug.Print cmd1.CommandText          cmd1.Execute          Set rst1 = New ADODB.Recordset     rst1.Open cmd1     If rst1.Fields("Password") = pw Then         MsgBox "Welcome on in.", vbInformation, _             "Programming Microsoft Access 2000"     Else         MsgBox "Invalid password.  Try again or " & _             "change password.", vbCritical, _             "Programming Microsoft Access 2000"     End If      End Sub 

The Second Login Form

Figure 7-2 shows the form that appears when a user opts to change the password for the employee ID. This form merely asks users to confirm their identity. The system requires this confirmation before it permits users to change a password. The form has two text boxes. Under normal circumstances, the first text box is always filled by the form that loads it. (See the cmdNewPassword_Click procedure above.) All users do is enter their hire date and click Submit. The main point here is to use a field whose value is known only by the employee. Use one or more other fields if you have better alternatives available.

Figure 7-2. The second login form, which asks users to confirm their identity.

The code behind the form

The form launches a query when a user clicks the Submit button. A form class module processes the query and matches the return set result to the user input. The event procedure behind the Submit button has a Dim statement that instantiates a copy of the MyTestClass3 module with a reference to ProcessMe. A second line invokes the WhoAmI method for the class, as shown below.

Private Sub cmdSubmit_Click() Dim ProcessMe As New MyTestClass3     ProcessMe.WhoAmI CLng(txtEmpID), _         CDate(txtHireDate) End Sub 

Invoking the form class module

The lookup procedure for the second form appears below. It uses a parameter query to perform the lookup of a hire date for an employee ID. By strongly typing the variables (notice the CLng and CDate functions in cmdSubmit_Click) before going into the class module, you can take advantage of the data typing option in a Parameters declaration as well as the data typing in the table. Without this data typing, Access must do internal transformations to the variant data type. The basic design for the lookup and return messages follows that for the password lookup. If the hire date on the form matches the one in the Employees table, the procedure opens the third form.

Sub WhoAmI(empid As Long, hd As Date) Dim cmd1 As Command Dim strSQL As String Dim prm1 As ADODB.Parameter Dim rst1 As ADODB.Recordset 'Assign the command reference and connection.     Set cmd1 = New ADODB.Command     cmd1.ActiveConnection = CurrentProject.Connection 'Write out SQL statement with parameters & assign to cmd1.     strSQL = "Parameters InEID Long;" & _         "Select EmployeeID, HireDate From Employees " & _         "Where EmployeeID=InEID"     cmd1.CommandText = strSQL     cmd1.CommandType = adCmdText          Set prm1 = cmd1.CreateParameter("InEID", adInteger, adParamInput)     prm1.Value = empid     cmd1.Parameters.Append prm1 'A handy line for catching SQL syntax errors     Debug.Print cmd1.CommandText 'Execute command.     cmd1.Execute 'Check Input vs. Table HireDate.     Set rst1 = New ADODB.Recordset     rst1.Open cmd1     If rst1("HireDate") = hd Then         DoCmd.openform "frmChangePassword"         Forms("frmChangePassword").txtEmpID = Forms("frmWhoAmI").txtEmpID         DoCmd.Close acForm, "frmWhoAmI"     Else         MsgBox "HireDate not valid for EmployeeID.  Try " & _             "again or Quit.", vbCritical, _             "Programming Microsoft Access 2000"     End If End Sub 

The Third Login Form

Figure 7-3 shows the final form, which appears when a user clicks the Submit button on the second form after entering the correct hire date. The form has three text boxes. One is for the employee ID. (This box fills in automatically under normal circumstances.) The second text box is for a new password and a third text box is for confirming the password. If those text boxes do not match, the system alerts the user. If the user clicks the Submit button without entries in all three boxes, another reminder message appears. Finally, if the form satisfies these two requirements, the class module referenced by the form updates the password for an employee ID in the Passwords table.

Figure 7-3. The third form, which lets users update their password.

The code behind the form

The module behind this form is the most interesting one in the case study. The module does data validation instead of passing the data off to a class module. The procedure still invokes a class module for the SQL statement that updates the password for an employee.

This split of data validation from database updates shows another way to apply class modules—by performing sensitive tasks using a class module. This standardizes the tasks and ensures proper performance. Other application elements that do not require standardization are candidates for customization by end-user departments.

Using Property Get and Property Let functions—This particular data validation logic relies on a pair of Property Let and Property Get functions. The AfterUpdate event for each of the three text boxes invokes the Property Let function, which updates the value of the AllFilled variable to True or False. (It's True if all the boxes are filled with legitimate values; it's False otherwise.)

A Property Get function reflects the status of all three text boxes with the form's filledCheck property. The cmdSubmit_Click procedure checks this single value to determine whether all three boxes are checked. If the value is False, the procedure displays a message reminding the user to complete all boxes. Otherwise, the click event procedure tests whether the password and confirm password text boxes match. If they do not, another message reminds the user to make them match. Finally, when a user clears these two obstacles, the procedure invokes the NewPS method of the local instance of the MyTestClass3 module.

Private AllFilled As Boolean Private Sub txtConfirm_AfterUpdate()     Me.filledCheck = txtConfirm End Sub Private Sub txtEmpID_AfterUpdate()     Me.filledCheck = txtEmpID End Sub Private Sub txtPassword_AfterUpdate()     Me.filledCheck = txtPassword      End Sub Public Property Let filledCheck(vntNewValu)     If (IsNull(txtEmpID) Or txtEmpID = "") Or _         (IsNull(txtPassword) Or txtPassword = "") Or _         (IsNull(txtConfirm) Or txtConfirm = "") Then         AllFilled = False     Else         AllFilled = True     End If End Property Public Property Get filledCheck()     filledCheck = AllFilled End Property Private Sub cmdSubmit_Click() Dim UpdatePW As New MyTestClass3     If Me.filledCheck = False Then         MsgBox "Please complete all entries before " & _             "submitting your new password.", vbInformation, _             "Programming Microsoft Access 2000"     ElseIf txtPassword <> txtConfirm Then         MsgBox "Password and Confirm Password do not " & _             "match.  Re-enter one or both.", vbInformation, _             "Programming Microsoft Access 2000"     Else         UpdatePW.NewPW txtEmpID, txtPassword     End If          End Sub Private Sub cmdLogin_Click()     DoCmd.openform "frmInputPassword"     Forms("frmInputPassword").txtEmpID = txtEmpID     Forms("frmInputPassword").txtPassword = txtPassword     DoCmd.Close acForm, "frmChangePassword" End Sub Private Sub cmdExit_Click()     DoCmd.Close End Sub 

Transferring values to another form

Two remaining procedures complete the functionality of the module behind the third form. A click event procedure behind the Employee Authentication button takes a user back to the first form and fills in the employee ID and password text boxes with their values from the third form. This feature relieves the user from having to reenter this data just after confirming it, but going back to the first form offers a single point of entry into the application. This simplifies maintenance in the long run. The form's Exit button simply closes the form.

Invoking the class module

The class module invoked by the module behind the third form uses a string expression to compute the SQL statement that a Command object uses to update an employee's password. This is one way to represent a string (such as the password value) inside another string (the overall SQL statement). Notice the multiple double apostrophes both before and after the new password value. These are escape codes for representing a double apostrophe inside another pair of double apostrophes. Aside from this VBA requirement for nesting one string inside another string, the code is easy to read. A message block statement at the procedure's close confirms the password change and advises the user how to proceed.

Sub NewPW(eid As Long, NuPassword As String) Dim cmd1 As Command Dim strSQL As String 'Assign the command reference and connection.     Set cmd1 = New ADODB.Command     cmd1.ActiveConnection = CurrentProject.Connection      'Define the SQL string; notice 'the insertion of passed arguments.     strSQL = "UPDATE Passwords " & _         "SET Passwords.Password = """ & NuPassword & """ " & _         "WHERE EmployeeID=" & eid & ";"     Debug.Print strSQL      'Assign the SQL string to the command and run it.     cmd1.CommandText = strSQL     cmd1.CommandType = adCmdText     cmd1.Execute      'Confirmation message     MsgBox "Your new password is accepted.  " & _         "Return to Employee Authentication or " & _         "Exit this form.", vbInformation, _         "Programming Microsoft Access" End Sub