Microsoft Access 2000: Class, Form, and Report Modules
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
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.
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:
- Standard modules. These hold subprocedures and function procedures that you want to make available throughout a database file. Standard modules can also contain variables defined with a Public declaration that you want to make available to procedures in other modules.
- Stand-alone class modules. These let you create custom objects. You can define properties, methods, and events for these objects, and you can use the New keyword to create instances of the form objects.
- Class modules for forms and reports (often called form and report modules). Forms and reports by default all have modules behind them (their HasModule property is set to True by default). You can use the Me keyword when referring to the modules behind forms and reports.
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:
- Class modules are a good choice for computing critical business expressions. You will generally use class modules for encapsulating a more sophisticated computation than the one for extended price.
- The second procedure, which is in the standard module, starts by referencing the class module, MyTestClass. The New keyword instantiates an object based on the class. In the sample, the variable named MyPriceComputer references the class.
- You can use the object reference for the instance of the class to invoke methods and set or read property values. You reference the class's EP method with the standard dot notation. You list arguments after the method's name and you reference properties with the same basic notation.
- Creating a property for a class can be as simple as declaring a Public variable in the class module.
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 UnitsInStockfield for the Products table based on a ProductIDfield 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 (MyTestClass2instead 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 MyTestClass2module. 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 PO2procedure below uses a parameter query to performwith 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:
- The procedure makes variable declarations and assigns references.
- It specifies the SQL string for the update query and assigns that string to a command property.
- It creates and assigns values to the parameters declared in the second step.
- It runs the command to update the database.
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: EmployeeIDand Password. The Employees table, which is imported directly from the Northwind database, contains EmployeeIDas 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 cpwprocedure 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 EmployeeIDfield 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_Clickprocedure 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 MyTestClass3module.
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 InvokeAddReferenceprocedure to create the reference. The procedure calls a procedure, AddReference, with the syntax for adding an item to the References collection via the RefEventsclass 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 RefEventsclass by using the AddReferenceand RemoveReferenceprocedures to accommodate a broader and more flexible selection process. For example, your application can derive the input to the AddReferenceprocedure 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 EnumerateAllFormsand EnumerateAllTablesprocedures 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 EnumerateAllModulestoInsertprocedure loops through the members of the AllModules collection and calls the other procedure, which actually updates the target modules. Since the InsertIntoModulesprocedure 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