Microsoft Access 2000: Class, Form, and Report Modules

Rick Dobson
CAB, Inc.

Applies to: Microsoft Access 2000

July 1999

Excerpted from Programming Microsoft® Access 2000. Copyright © 1999 by Rick Dobson. Reprinted with permission of Microsoft Press.

Microsoft Press® books are available worldwide wherever quality books are sold. To order direct, call 1-800-MSPRESS (United States) or 1-800-667-1115 (Canada). For more information, visit our site on the World Wide Web at http://mspress.microsoft.com/. All prices are subject to change without notice.

Rick Dobson, Ph.D., is Chief Technology Officer for CAB, Inc. CAB offers Office, Web, and database services as well as support for Office-based automated accounting packages. Rick is also the author of Programming Microsoft Access 2000 from Microsoft Press. CAB maintains two sites. One targets Access/Office 2000 development (www.programmingmsaccess.com/), and the other (www.cabinc.net/) focuses on Access/Office 97 and Dynamic HTML. Both sites offer samples, presentations, either full-length articles or tutorials, and prizes.

Summary: This chapter from Programming Microsoft® Access 2000 introduces form, report and class modules, demonstrates how to develop custom properties and methods, and shows simple ways to build classes into your Access applications. (34 printed pages)

Contents

Introduction
Module Types
Class Modules
Custom Properties and Methods
Case Study: Coding a Login Interface
Programming Events into Custom Classes
Using the All Collections
Programmatically Editing Modules

Introduction

To program Microsoft Access efficiently, you must manage your programmatic resources so that they are easy to use and reuse. The value of code grows in proportion to how much use you can get from it.

Class modules package code for easy reuse. The class module acts as a container that exposes the code and selected variables inside it in a way that is familiar to Microsoft Visual Basic® for Applications (VBA) developers. Basically, you invoke class procedures and assign values to variables with the same syntax used for the properties and methods of built-in Access objects. To use the code in a class module, you do not have to know anything about how it works. Also, since class modules expose properties, methods, and events like other objects do, even beginning VBA developers can use them.

This chapter first introduces stand-alone class modules and form and report class modules. Then it demonstrates simple ways to build classes into your Access applications and to develop custom properties and methods. Next comes a case study that uses three forms, a few custom Property Get and Property Let functions, and a couple of techniques based on ActiveX® Data Objects (ADO) to start building an application. The section after the case study shows the syntax for programming events into your custom classes and introduces the WithEvents keyword.

The focus then shifts to the containers for class, form, and report modules as we look at the All collections that are new to Access 2000. Just as there are AllForms and AllReports collections, there is an AllModules collection. (In fact, there are ten All collections altogether.) The chapter wraps up by explaining how to combine the AllModules collection with the Modules collection to manage code in an application.

Module Types

There are three basic kinds of modules:

Class Modules

Stand-alone class modules differ from form and report class modules in several ways.

First, stand-alone class modules do not have a built-in user interface, as form and report class modules do. This makes stand-alone class modules more suited to tasks that do not require an interface, such as performing calculations, looking up data, or modifying a database. When form or report modules require computationally intensive tasks, they can call a stand-alone class module.

Second, stand-alone class modules offer Initialize and Terminate events that enable operations that need to take place at the opening and closing of a class instance. Report and form modules do not have these events, but you can perform similar functions with the Load and Close events.

Third, you must use the New keyword to create instances of stand-alone class modules. Report and form class modules also let you create instances with the DoCmd OpenForm and OpenReport methods as well as by referencing the report or form class module's properties or methods. For example, Form_MyForm.SetFocus opens the MyForm form.

You can create a stand-alone class module from the Insert menu in the Visual Basic Editor. (The same menu offers commands for building a standard module or a procedure.) After creating a class module shell, you can populate it with procedures and declarations, which equip it with custom properties and methods.

Custom Property Functions and Custom Methods

Special property functions make it easier to develop any combination of read-only, write-only, and read/write properties for your classes. If your application permits, you can define properties by simply declaring a public variable. When a class module defines a property with a public variable, it is always a read/write property. The ability to declare custom properties lets you extend the basic Access functionality for forms and reports. In addition, these property functions allow you to create powerful stand-alone classes.

Your applications can also build custom methods into classes. You can use subprocedures or function procedures to accomplish this. By selectively exposing variables and procedures with the Public keyword, you can narrowly define what methods and properties they expose. This lets your applications define interfaces to your class objects that perform in very specific ways.

Instantiating Classes

The public methods and procedures support programmatic access by procedures outside the class. You must first instantiate the class in a host procedure within another module, using the New keyword. (You use the same keyword to instantiate objects from other classes, such as ADO Connection and Recordset objects. In fact, your applications can instantiate multiple copies of a custom class at the same time—just like the ADO classes.) After instantiating a class, the code in your host procedure manipulates the instance of the class, not the class itself. You can change a property in one instance of a form, but when you instantiate a second instance of the form it appears with the default property setting.

Custom Classes and Events

Although VBA in Access lets you build custom classes with their own properties and methods, you cannot build custom events within those classes. You can, however, design a class that hooks onto a built-in class or type library that you attach. For example, you can build a class module that launches VBA code in response to the ItemAdded and ItemRemoved events of the References collection. This collection tracks links to external type libraries and ActiveX controls. After referencing a library, such as the Microsoft ActiveX Data Objects 2.1 Library, you can build custom events around the ADO events for the Connection and Recordset objects. These events can enable asynchronous data access that lets your application respond to users even while it remains ready to respond to a completed connection or the availability of a fetched set of records.

You use the WithEvents keyword within a Public declaration to point to an object reference that monitors and reports events within an ActiveX control. This keyword is valid only in class modules. You can define multiple variables within a module with the WithEvents keyword, but you cannot create arrays with it. Also, a declaration cannot contain both the WithEvents and New keywords.

Custom Properties and Methods

When you use class modules, you inevitably work with two separate modules. The class module exposes properties and methods and propagates events. A second module references the class module; it assigns and reads property values as well as invokes methods. This module can initiate actions that fire events, and these, in turn, can invoke any associated event procedures in the class module.

Exposing a Property with a Public Variable

The following sample shows two listings. The first is from the MyTestClass module. It is a class module, and it starts with a couple of variable declarations—one for this sample and one for the next sample. The procedure named EP computes the extended price from three arguments passed to it: units, price, and discount. The procedure saves the result of its expression in the variable name ExtendedPrice. A declaration in the module's general area defines ExtendedPrice as a public variable. This enables a host procedure in another module that works with an instance of the MyTestClass object to read the variable's value.

FROM MyTestClass module (a class module)
Public ExtendedPrice As Currency
Private MyComputedPrice As Currency

Public Sub EP(units As Long, price As Currency, _
    discount As Single)
    
    'Compute with result in public variable.
    ExtendedPrice = units * price * (1 - discount)
End Sub


FROM Module1 (a standard module)
Sub ComputeExtendedPrice()
    'Create new instance of class module.
    Dim MyPriceComputer As New MyTestClass

    'Invoke EP method for class, and
    'print Extended Price property.
    MyPriceComputer.EP 5, 5, 0.02
    Debug.Print MyPriceComputer.ExtendedPrice
End Sub

The host procedure, ComputeExtendedPrice, resides in a standard module named Module1. This procedure instantiates an object based on the class defined by MyTestClass. Next, it invokes the EP method for the object. Finally, it prints the ExtendedPrice property for the object.

While this sample is very basic, it demonstrates several important points about using class modules:

Exposing a Property with a Property Function

The first of the following listings shows a different approach to the same task. It relies on a property defined with a Property Get function. The ep2 method is nearly identical to the EP method in the preceding sample. The only difference is that ep2 deposits the result of its expression into a private variable, ComputedPrice. (See the private variable declaration in the preceding sample.) All by itself, this means that instances of the class cannot expose the expression's result. You use a Property Get function to expose a private variable. Since there is no other property function defined for ComputedPrice, the property is read-only. If there were a Property Let function with the same name, the property would be read/write. Using read-only properties can help to secure the values of your properties—or at least the ways to set them.

FROM MyTestClass module (a class module)
Public Sub ep2(units As Long, price As Currency, _
    discount As Single)

    'Compute with result in private variable; expose
    'result through Property Get function.
    MyComputedPrice = units * price * (1 - discount)
End Sub


Property Get ComputedPrice()
    'This is how to return a read-only property.
    ComputedPrice = MyComputedPrice
End Property


FROM Module1 (a standard module)
Sub GetComputedPrice()
    Dim MyPriceComputer As New MyTestClass

    'Using a value defined by a property looks the same
    'as one defined with a public variable.
    MyPriceComputer.ep2 5, 5, 0.02
    Debug.Print MyPriceComputer.ComputedPrice
End Sub

Public Variables vs. Property Functions

The syntax for invoking the method and printing the property value is identical in the two samples, although the property is exposed differently. This confirms that properties work in the same way whether you define them with a public declaration or one or more property functions. Public variables might be a simpler way to implement properties in class modules, but property functions are a more flexible way to expose them. You use a Property Get function by itself for a read-only variable, and you use a Property Let function by itself for a write-only property. You use both types of property functions for a read/write property. If your property references an object instead of a scalar variable, you can use a Property Set function instead of a Property Let function. You use the Property Get function to return object property values whether you are working with a scalar variable or an object

Class Modules and Data Sources

Class modules are good for encapsulating any kind of code. They have special values that are useful for when you want to a make a data source available for updating or viewing, but you need to secure the data source from accidental or inadvertent damage by users.

Updating data with a SQL string

The following sample uses a class module to update the UnitsInStock field for the Products table based on a ProductID field and the quantity ordered. A procedure with two lines passes two arguments to a subprocedure in a class module. This sample uses a different class module from the two samples for calculating extended price (MyTestClass2 instead of MyTestClass). In practice, you divide your functions and declarations into homogeneous collections of method procedures and properties representing distinct object classes. The OrderIt variable represents the MyTestClass2 module. Within the module is a function named PO1. It takes two arguments, one for the ProductID and one for the units ordered.

Sub MyOrder()
    Dim OrderIt As New MyTestClass

    OrderIt.PO1 1, 10
End Sub

The next procedure, PO1, updates the Products database. Specifically, it decreases UnitsInStock by the number of units ordered. This procedure resides in the class module (MyTestClass2). Note the procedure's design: It uses a Command object with a SQL string that defines the update query. Although the procedure accepts two arguments, it does not apply a parameter query. Instead, it uses the passed arguments as variables in the string expression defining the SQL string. This design leads to a very compact procedure that is relatively easy to read.

'A method for updating a table
Public Sub PO1(ProductID, units)
    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 Products " & _
        "SET UnitsInStock = " & _
        "UnitsInStock-" & units & " " & _
        "WHERE ProductID=" & ProductID
        
    'Assign the SQL string to the command and run it.
    cmd1.CommandText = strSQL
    cmd1.CommandType = adCmdText
    cmd1.Execute
End Sub

Updating data with a parameter query

Many developers prefer a more traditional approach that relies on a parameter query. The PO2 procedure below uses a parameter query to perform with a SQL string the task accomplished by PO1. A parameter query lets you declare data types with traditional VBA conventions. Notice that the ADO constant adInteger represents a long data type, and the constant adSmallInt designates an integer data type. You must create the parameters with the CreateParameter method in the same order in which you declare them in the Parameters clause of the query statement. Failing to do so will generate a run-time error.

Note   Look up the Type property of the ADO Parameter object in the Access 2000 online Help to see the complete selection of data types for variable declarations.

Public Sub PO2(ProductID As Long, units As Integer)
    Dim cmd1 As Command
    Dim strSQL As String
    Dim prm1 As ADODB.Parameter, prm2 As ADODB.Parameter

    '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 PID Long,Quantity Integer;" & _
        "UPDATE Products " & _
        "SET UnitsInStock = " & _
        "UnitsInStock-Quantity " & _
        "WHERE ProductID=PID;"
    cmd1.CommandText = strSQL
    cmd1.CommandType = adCmdText
    
    'Declare parameters; must have same order as declaration.
    Set prm1 = cmd1.CreateParameter("PID", adSmallInt, _
        adParamInput)
    prm1.Value = ProductID
    cmd1.Parameters.Append prm1
    Set prm2 = cmd1.CreateParameter("Quantity", adInteger, _
        adParamInput)
    prm2.Value = units
    cmd1.Parameters.Append prm2
    
    'Run update query.
    cmd1.Execute
End Sub

There are four main components to the parameter query design of the update task:

Case Study: Coding a Login Interface

This case study shows one approach to coding a login interface with Access. It uses both stand-alone 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.

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 stand-alone 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 on the next page, 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 dates 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 passwords

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

Programming Events into Custom Classes

You can use VBA in Access to create classes for instantiating objects, but VBA objects cannot initiate their own events. However, you can build classes around type libraries and ActiveX controls that propagate their events to a host environment. For objects that propagate events, your VBA application can wrap code around events that occur within the class. When a host procedure for an instance of a class launches a method that causes the firing of a propagating event from inside the class, the event procedure works its way to the host for the class module. (See Figure 7-4.)

The References collection relates hierarchically to the Application object. You invoke the AddFromFile and Remove methods to enable an application to programmatically add and delete references to type libraries. These library files contain standard descriptions of exposed objects, methods, properties, and events. Recall that the ADODB object model is dependent on a library. You can add references to it and other libraries manually or programmatically.

Figure 7-4. The WithEvents keyword propagates an object's event procedures to the object's host when an event fires.

Chapter 2 explains how to add references manually to the three ADO libraries. The following section will explain how to add references programmatically to any library or ActiveX control. You can issue a confirmation message when your application finishes adding or removing a reference.

Two Built-In Class Events

Class modules have two built-in events: Initialize and Terminate. The Initialize event occurs when you create a new instance of a class module. You create a shell for the Initialize event procedure by selecting Class from the class's Object box and Initialize from the Procedure box. You can do anything necessary in an Initialize event procedure to prepare your class instance for use.

In the Terminate event procedure, you should clean up after your current application. This can be as simple as setting an object reference to Nothing. The Initialize and Terminate events occur just once at the beginning and end of the life of a class instance. Therefore, they are not particularly handy for generating interactive or dynamic behavior at any times other than the birth and death of the instance of a class.

Using the WithEvents Keyword to Trap Propagated Events

The following class module uses the WithEvents keyword to trap events propagated by the References collection. The References collection has a separate item for each checked item in the References dialog box. The ItemAdded and ItemRemoved events occur only when your code adds or removes references. If a user manually modifies the References collection, these events do not fire.

Option Compare Database
'Declare object variable to represent References collection.
Public WithEvents evtReferences As References

'When instance of class is created, initialize evtReferences
'variable.
Private Sub Class_Initialize()
    Set evtReferences = Application.References
End Sub


'When instance is removed, set evtReferences to Nothing.
Private Sub Class_Terminate()
    Set evtReferences = Nothing
End Sub


'Display message when reference is added.
Private Sub evtReferences_ItemAdded(ByVal Reference As _
        Access.Reference)
    MsgBox "Reference to " & Reference.Name & " added.", _
        vbInformation, "Programming Microsoft Access 2000"
End Sub


'Display message when reference is removed.
Private Sub evtReferences_ItemRemoved(ByVal Reference As _
        Access.Reference)
    MsgBox "Reference to " & Reference.Name & " removed.", _
        vbInformation, "Programming Microsoft Access 2000"
End Sub

Starting and ending a WithEvents reference

You use the WithEvents keyword in combination with a class that propagates events. The Public statement in the class module above declares a reference (evtReferences) to the References collection in the Access application object. The WithEvents keyword within the statement enables the class module to trap events propagated by the References collection. The Class_Initialize event procedure sets a reference. Recall that you cannot use the New keyword for a reference that you declare with WithEvents.

Wrapping code around captured events

Two event procedures in the class module, ItemAdded and ItemRemoved, invoke message block statements. These show messages naming the reference that a method adds or removes. The event procedures show the syntax for wrapping custom code around objects that propagate events. In this case, the object is the References collection. The event procedures merely write out the name of the library being added to or removed from the References collection.

Standard Modules Cause Events

As with any class module, you need one or more procedures in a standard module to instantiate the class (see the sample below) and to invoke methods, assign property values, or read property values. In the declarations area of the module hosting the class, you include a Dim or Public statement with the New keyword and the class name. This instantiates the class and sets an object reference (objRefEvents in the sample).

Standard module syntax for events

If the instance of the class propagates events from an embedded object, you should use a Public statement with the WithEvents keyword. This statement exposes the events to other modules referencing the class. When you invoke the methods from the underlying class, you must traverse the local object reference (objRefEvents), the reference within the class module exposing the events (evtReferences), and then a specific method name, such as AddFromFile or Remove. Unlike a normal reference to a class module, this one points to a method for the source object in the WithEvents declaration.

'Create new instance of RefEvents class.
Dim objRefEvents As New RefEvents

Sub InvokeAddReference()
    'Pass file name and path of type library to AddReference procedure.
    AddReference _
        "C:\Program Files\Common Files\System\ado\msjro.dll"
End Sub


Sub InvokeRemoveReference()
    'Pass name of existing reference. (Use internal name from File
    'Properties list; same name appears when adding reference.)
    RemoveReference "JRO"
End Sub


Sub AddReference(strFileName As String)
    objRefEvents.evtReferences.AddFromFile (strFileName)
End Sub


Sub RemoveReference(strRefName As String)
    objRefEvents.evtReferences.Remove _
        objRefEvents.evtReferences(strRefName)
End Sub

The sample above adds a reference to the library holding the JRO model. (This model enables Jet replication via ADO.) You run the InvokeAddReference procedure to create the reference. The procedure calls a procedure, AddReference, with the syntax for adding an item to the References collection via the RefEvents class module. The library holding the JRO model is a dynamic link library (DLL) that typically resides in the ADO folder of the System folder in the Common Files directory of the Program Files directory. Launching the InvokeRemoveReference procedure eliminates the JRO item from the References collection. The JRO designation is the Name property for the item in the References collection.

Note   To discover the arguments for the AddFromFile and Remove methods, you add references manually. Then you enumerate the items in the References collection with a For…Each statement while listing their Name and FullPath properties. You use these property values to uniquely identify arguments for the AddFromFile and Remove methods.

Extending the application

You can easily adapt the RefEvents class by using the AddReference and RemoveReference procedures to accommodate a broader and more flexible selection process. For example, your application can derive the input to the AddReference procedure from a collection of type libraries, executables, ActiveX controls, and even database files. A combo box can offer users a list of references to add or remove. Alternatively, your procedure can make a selection from a list based on other factors, such as what a user is trying to accomplish.

Using the All Collections

If you are the type of developer who likes to track your objects in a database project (most of us find this essential), you'll be happy to know that there is an AllModules collection, which is a counterpart to the AllForms and AllReports collections you learned about in Chapters 5 and 6. The members of the All collections are not database objects, such as forms, reports, and modules, but AccessObject objects that contain a minimal amount of detail about most types of saved objects in a database.

AccessObject Properties

You can quickly enumerate the AccessObject objects in any All collection. Since AccessObject objects point at saved objects, you cannot add or delete members. You perform these tasks through the open collections they point to.

When you encounter an AccessObject object that your application needs more detail on, you can use the IsLoaded and Name properties to examine the properties of the object the AccessObject object points to. These open object collections have a fuller set of properties and methods that are not available with the All collections.

AccessObject objects have a Type property that describes the type of AccessObject rather than the type of database object. The Type property value of any AllModules member is 5. This distinguishes an AccessObject member in the AllModules collection from one in the AllForms collection with a Type property value of 2. In either case, you cannot determine whether you are dealing with a class module or a form class module by simply examining the AccessObject member of the All collection. You must examine the Type property of a Module object and the HasModule property of a Form object.

The All Collections

There are seven All collections besides the AllModules, AllForms, and AllReports collections. This set of ten collections (see Figure 7-5 on the next page) divides naturally into two sets of five each. The AllForms, AllReports, AllMacros, AllModules, and AllDataAccessPages collections are members of the CurrentProject and CodeProject objects in the Access Application object. The AllTables, AllQueries, AllViews, AllStoredProcedures, and AllDatabaseDiagrams collections are members of the CurrentData and CodeData objects in the Access Application object. When you designate AccessObject objects in any of the ten All collections, you must set a reference that points at the appropriate antecedent object. Failing to do so will generate an error.

Figure 7-5. The ten All collections and their hierarchical relationship to Project and Data objects

The AllQueries, AllViews, AllStoredProcedures, and AllDatabaseDiagrams collections have restricted availability by Access file type. Recall that Access projects can be in a traditional .mdb file or in the new Access 2000 .adp file. (Chapter 4 touched on the .adp file type, and Chapter 12 will discuss it further.) The AllQueries collection is available in .mdb files but not .adp files. In contrast, you can tap the AllViews, AllStoredProcedures, and AllDatabaseDiagrams collections from .adp files but not from .mdb files. Your applications can still reference views and stored procedures in .mdb files by using the ADOX object library.

Note   You might wonder why Access 2000 permits Views and Procedures collections in .mdb files but does not offer AllViews and AllStoredProcedures in .mdb files. These two All collections were not exposed in .mdb files because of the need to satisfy higher priority requirements. Look for AllViews and AllStoredProcedures collections with .mdb files in future releases of, or updates to, Access.

Enumerating the All Collection Members

The following three procedures show the high degree of similarity in programming different All collections. The first procedure performs a simple enumeration of all the modules in the current project. Notice that it initially declares obj1 as an AccessObject type because it accepts the identity of elements in the AllModules collection, which contains AccessObject objects. Also note that the enumeration loop passes through the AllModules collection but the code reaches this collection through the Application and CurrentProject objects.

Sub EnumerateAllModules()
    Dim obj1 As AccessObject
    For Each obj1 In Application.CurrentProject.AllModules
        Debug.Print obj1.Name & vbTab & obj1.Type & _
            vbTab & obj1.IsLoaded
    Next obj1
End Sub


Sub EnumerateAllForms()
    Dim obj1 As AccessObject
    For Each obj1 In Application.CurrentProject.AllForms
        Debug.Print obj1.Name & vbTab & obj1.Type & _
            vbTab & obj1.IsLoaded
    Next obj1
End Sub


Sub EnumerateAllTables()
    Dim obj1 As AccessObject
    For Each obj1 In Application.CurrentData.AllTables
        Debug.Print obj1.Name & vbTab & obj1.Type & _
            vbTab & obj1.IsLoaded
    Next obj1
End Sub

The EnumerateAllForms and EnumerateAllTables procedures have the same structure as the EnumerateAllModules procedure. You should note some significant differences in content, however. First, the specific AccessObject collection changes from AllModules to AllForms in one procedure and AllTables in the other procedure. Second, the path to the AllTables collection passes through the CurrentData object rather than the CurrentProject object. If we were to switch the AllTables collection to either an AllViews or an AllStoredProcedures collection, the code would work in an .adp file but not in an .mdb file.

Adapting to .mdb and .adp File Types

Using the ProjectType property of the CurrentProject object, you can detect whether you are working with an .adp or an .mdb file. This lets you write single procedures that adapt to their environment. The following sample prints the names of all the views and stored procedures in an .adp file, but it switches to printing all the queries in an .mdb file. As you can see, the only trick required is to test for the value of the ProjectType property. The AccessObject Type property adds values by distinctly differentiating objects for views with a type value of 7 from objects pointing at stored procedures, whose type value is 9.

Sub EnumerateAllViews2()
    Dim obj1 As AccessObject, dbs1 As Object
    Set dbs1 = Application.CurrentData
    If Application.CurrentProject.ProjectType = acADP Then
        For Each obj1 In dbs1.AllViews
            Debug.Print obj1.Name & vbTab & obj1.Type & _
                vbTab & obj1.IsLoaded
        Next obj1
        For Each obj1 In dbs1.AllStoredProcedures
            Debug.Print obj1.Name & vbTab & obj1.Type & _
                vbTab & obj1.IsLoaded
        Next obj1
    Else
        For Each obj1 In dbs1.AllQueries
            Debug.Print obj1.Name & vbTab & obj1.Type & _
                vbTab & obj1.IsLoaded
        Next obj1
    End If
End Sub

Using AllForms and AllModules

The sample on the next page uses the All collections and the corresponding collections of open modules and forms to develop a list of all the modules (by type) and the class modules for forms in a project. Since the property for denoting standard class modules is different from the one for class modules for forms, the code requires different expressions to test for standard class modules versus class modules for forms.

Recall that modules have a Type property but forms have a HasModule property. The code must iterate through the members of the AllModules and AllForms collections because some, or even all, modules and forms can be closed. You check the IsLoaded status of the AccessObject objects in AllModules and AllForms to determine whether you need to open a module or form before assessing its module type, or to determine whether a form has a class module. The procedure recloses forms and modules after it examines them.

Sub ListAllModulesByTypeAndClassForms()
    Dim obj1 As AccessObject, dbs1 As Object
    Dim mod1 As Module, frm1 As Form
    
    Set dbs1 = Application.CurrentProject

    'Search for open AccessObject objects in AllModules collection.
    'Open and reclose those that are not open.
    
    For Each obj1 In dbs1.AllModules
        If obj1.IsLoaded = True Then
            ListTypeOfModule obj1.Name
        Else
            DoCmd.OpenModule obj1.Name
            ListTypeOfModule obj1.Name
            DoCmd.Close acModule, obj1.Name
        End If
    Next obj1

    'Search for open AccessObject objects in AllForms collection.
    'Open and reclose those that are not open.

    For Each obj1 In dbs1.AllForms
        If obj1.IsLoaded Then
            DoesFormHaveModule obj1.Name
        Else
            DoCmd.OpenForm obj1.Name
            DoesFormHaveModule obj1.Name
            DoCmd.Close acForm, obj1.Name
        End If
    Next obj1
End Sub


Sub ListTypeOfModule(modname)
    Dim strType As String

    'Decode module Type value.
    If Modules(modname).Type = 0 Then
        strType = "Standard Module"
    Else
        strType = "Class Module"
    End If
    
    'Print module name and type.
    Debug.Print Modules(modname).Name & vbTab & strType
End Sub


Sub DoesFormHaveModule(frmname)
    'Only print form name if it has a module.
    If Forms(frmname).HasModule = True Then
        Debug.Print frmname & vbTab & "Form Class Module"
    End If
End Sub

Programmatically Editing Modules

Since you can perform so many different tasks with standard modules, stand-alone class modules, and class modules for forms, your applications are likely to have many of these modules. This will eventually create a need for maintenance. One common maintenance requirement is the insertion or deletion of one or more lines of code in a set of modules. This section shows how to add a line to and remove a line from all the standard and stand-alone class modules, and then it shows the same for form class modules. Because the code for standard and stand-alone class modules is stored differently from the code for class modules for forms, the steps are slightly different.

Editing Approaches

The Module object offers an array of methods and properties that can help you programmatically edit modules. The samples in this section use the InsertLines, Find, and DeleteLines methods. These methods process both standard and class modules, including stand-alone class modules and report and form class modules. These are a subset of the methods and properties that support programmatically managing module content.

You use the InsertLines method with a Module object to insert one or more lines into the module. Module line numbers start with 1 and extend through the CountOfLines property value for the module. The method takes a line number and a string argument. If you need to insert multiple lines into a module, add vbCrLf constants into the string expression representing the method's string argument. When you insert lines with this method, it moves down the remaining lines in the module.

The Find method searches for a text string in a module. It returns a value of True if it finds the search text, and it returns False otherwise. If you know precisely where some text is, you can specify a starting line and column and an ending line and column. If you do not know where some search text resides in a module, leave the text position arguments blank and the function will return the values of the search text in the module. You can also designate pattern searches and case-restrictive searches.

The DeleteLines method removes one or more lines of text from a module. The method takes two arguments: a start line and the total number of lines to remove from a module. You can use the DeleteLines method in combination with the Find method. You use the Find method to search for text in a module. You can then base the invocation of the DeleteLines method on the return value from the Find method.

Inserting Text into Modules

The procedures below combine the AllModules and Modules collections to edit the text in a collection of modules. Specifically, they insert a comment line at the beginning of each module, proclaiming it a standard module or a class module. The EnumerateAllModulestoInsert procedure loops through the members of the AllModules collection and calls the other procedure, which actually updates the target modules. Since the InsertIntoModules procedure requires an open module, the first procedure opens the module if it is not already open. Then, when the second procedure returns control to the first one, it closes the module again to restore its initial state.

Sub EnumerateAllModulestoInsert()
    Dim obj1 As AccessObject
    
    'Loop through AllModules members.
    'If module is open, call sub to insert lines;
    'else open module first, then close afterwards.
    For Each obj1 In Application.CurrentProject.AllModules
        If obj1.IsLoaded = True Then
            InsertIntoModules obj1.Name
        Else
            DoCmd.OpenModule obj1.Name
            InsertIntoModules obj1.Name
            DoCmd.Close acModule, obj1.Name, acSaveYes
        End If
    Next obj1
End Sub


Sub InsertIntoModules(modname)
    Dim strType As String, mod1 As Module

    Set mod1 = Modules(modname)
    
    'Detect module type to determine which
    'string to insert.
    If mod1.Type = 0 Then
        strType = "'Standard Module"
    Else
        strType = "'Class Module"
    End If
    mod1.InsertLines 1, strType
    Set mod1 = Nothing
End Sub

The InsertIntoModules procedure accepts a single argument—the name of the module to edit. It performs no iteration because the first procedure calls it once for each member in the AllModules collection. The procedure begins by setting a reference to the module named in the passed argument. Then it determines the type of module to which the reference points and sets a string variable to a comment naming the module type. After determining the text to insert, the procedure invokes the InsertLines method for the referenced module.

Deleting Text from Modules

The following two procedures delete a line from a procedure. In fact, they remove the line added by the preceding pair of procedures. The design of these next two procedures is flexible enough so that you can easily extend them to accommodate the deletion of multiple selected lines from any set of modules.

The procedures follow the same general logic as the preceding pair, with one major difference: This pair uses the Find and DeleteLines methods to remove text instead of the InsertLines method. The Find method is often critical when you prepare to use the DeleteLines method because the Find method lets your code determine whether some text is there before it deletes any content. In this instance, the Find method looks for the word "Module" in the first 40 characters of the first line. The DeletefromModules procedure invokes the DeleteLines method to delete one line starting with the first line in the module. The DeleteLines method removes lines unconditionally. However, you can manually invoke the Undo Delete function to restore removed text.

Sub EnumerateAllModulestoDelete()
    Dim obj1 As AccessObject, dbs As Object
    Dim mod1 As Module, frm1 As Form

    'Loop through AllModules members.
    'If module is open, call sub to delete line;
    'else open module first, then close afterwards.
    For Each obj1 In Application.CurrentProject.AllModules
        If obj1.IsLoaded = True Then
            DeletefromModules obj1.Name
        Else
            DoCmd.OpenModule obj1.Name
            DeletefromModules obj1.Name
            DoCmd.Close acModule, obj1.Name
        End If
    Next obj1
End Sub


Sub DeletefromModules(modname)
    Dim mod1 As Module

    Set mod1 = Modules(modname)
    
    'Delete first line if first 40 characters
    'contain "Module".
    If mod1.Find("Module", 1, 1, 1, 40) = True Then
        mod1.DeleteLines 1, 1
    End If
    Set mod1 = Nothing
End Sub

Inserting Text into Form Class Modules

The following two procedures insert a line at the beginning of each form class module with the comment that it is a class module. Instead of looping through the AllModules collection, the first procedure loops through the AllForms collection. For each member of the AllForms collection, it calls the InsertIntoForms procedure.

This second procedure assesses whether the passed form name is a class module. If it is, the procedure sets a reference to the module behind the form. This step exposes that module. The procedure closes by inserting the comment line into the module and setting the reference to Nothing to free its resources.

Sub EnumerateAllFormsToInsert()
    Dim obj1 As AccessObject

    'Loop through AllForms members;
    'if form is loaded invoke module to insert line,
    'else open form first and then close afterwards.
    For Each obj1 In Application.CurrentProject.AllForms
        If obj1.IsLoaded Then
            InsertIntoForms obj1.Name
        Else
            DoCmd.OpenForm obj1.Name
            InsertIntoForms obj1.Name
            DoCmd.Close acForm, obj1.Name, acSaveYes
        End If
    Next obj1
End Sub


Sub InsertIntoForms(frmname)
    Dim mod1 As Module, strType As String

    'If Form has module, set reference to it
    'and insert line into the module.
    'Free reference resource when done.
    If Forms(frmname).HasModule = True Then
        Set mod1 = Forms(frmname).Module
        strType = "'Form Class Module"
        mod1.InsertLines 1, strType
        Set mod1 = Nothing
    End If
End Sub

Deleting Text from Form Class Modules

The two procedures below remove the "Class Module" comment line from the first line of modules behind forms. As you can see, this pair's design mimics critical elements from the preceding pairs of procedures for inserting and deleting lines. This pair iterates through the AllForms collection, like the pair that added a comment line to the beginning of all form class modules in a project.

However, the second procedure in this pair uses the Find and DeleteLines methods to remove the first line in a module if it contains the word "Module" in the first 40 characters of its first line. This resembles the procedure for deleting lines from the Modules collection.

Sub EnumerateAllFormstoDelete()
    Dim obj1 As AccessObject
    
    'Loop through AllForms members;
    'if form is loaded invoke module to remove line,
    'else open form first and then close afterwards.
    For Each obj1 In Application.CurrentProject.AllForms
        If obj1.IsLoaded Then
            DeletefromForms obj1.Name
        Else
            DoCmd.OpenForm obj1.Name
            DeletefromForms obj1.Name
            DoCmd.Close acForm, obj1.Name, acSaveYes
        End If
    Next obj1
End Sub


Sub DeletefromForms(frmname)
    Dim mod1 As Module, strType As String

    'If form has module, then check contents of first line
    'for "Module", and delete the first line if it is present.
    'Free module reference resource when done.
    If Forms(frmname).HasModule = True Then
        Set mod1 = Forms(frmname).Module
        If mod1.Find("Module", 1, 1, 1, 40) = True Then
            mod1.DeleteLines 1, 1
        End If
        Set mod1 = Nothing
    End If
End Sub

See also: "Microsoft Access 2000: Data Access Models"