Chapter 32: Object-Oriented Programming with VBA
A major incentive in all modern application development is to produce robust, reusable code. Microsoft Access provides a number of ways to make code more reusable, beginning with simple import or export of code modules on through building runtime code libraries.
This chapter covers one approach to creating code modules you can reuse from any Access database. The code modules we describe in this chapter define new types of objects for your Access applications. These objects include properties and methods, and you can copy the objects into other Access applications or add them to Access code libraries.
The objects you create enforce modular, object-based programming. You’ve likely noticed how Access is based on objects. Microsoft defines just about everything in an Access application as some kind of object. All the forms, the controls on the forms, the reports, and other visible parts of your programs are objects.
In addition, there are any number of hidden objects (such as table relationships) lurking in your program. These objects are one of the ways Access is modular in nature. Each built-in Access object (such as a table, query, or form) performs some task in the application.
In this chapter, we dive into the important topic of object-oriented programming (OOP) in Access. Here you’ll learn what objects are and how to use them in your programs. You’ll also learn how to build your own objects using Access VBA code. Although this chapter discusses objects such as forms and controls as examples, the emphasis is on the technology of creating and using custom objects in your Access applications.
You create the custom objects in your applications by adding code to a special class module. In the lexicon of object-oriented programming, a class is a code element that defines an object. A good analogy for a class module is the engineering specification that defines a car or airplane. You create an object using the class as its specification. You add code to the class module to define the object’s properties and methods. Modifying the code in a class module modifies how the object defined by the class module behaves.
This chapter uses the database named Chapter32.accdb. If you haven’t already copied it onto your machine from the CD, you’ll need to do so now.
Benefits of Object-Oriented Programming
You might be wondering why it’s important to bother with objects. What are the advantages of Access object-oriented programming? Why complicate things by introducing the complexity of building and maintaining custom objects when traditional procedural programming techniques have worked so well in your Access applications?
You’ve already seen how Access’s object-based programming benefits database developers. You do all the Access data access through Data Access Objects (DAO) or ActiveX Data Objects (ADO) recognized by the Jet database engine. Other built-in Access objects such as forms and controls include properties you can easily manipulate at design time. As the application runs, these properties determine the object’s behavior. Creating a form or report requires nothing more than dropping control objects on the form or report’s surface and setting properties to bind the control to data and establish the control’s appearance.
The greatest benefit from using objects is encapsulation, which is the ability to wrap all aspects of the object’s functionality into an entity. For example, dropping a text box onto an Access form adds several new properties, methods, and events to the form. The text box control encapsulates all the relevant properties (for example, ForeColor, BackColor, and so on), methods (for example, SetFocus), and events (for example, BeforeUpdate, LostFocus, and so on) required to support a text box type of object. Although you add these new items to the form, you can access the new properties, methods, and events through the new text box control.
The textbox control encapsulates everything a text data-entry control requires to do its job. In addition, Access text box controls incorporate a lot of hidden capabilities, such as binding to a data source, applying validation rules, and so on. In other words, there’s a lot going on in the humble text box control that you probably seldom recognize or appreciate.
A custom Access object lets you encapsulate complex activities and tasks as a simple, compact entity you can use in any other Access database. An encapsulated object is often much easier to maintain than a traditional module or VBA procedure. Because the object contains all its functionality as a single entity, there’s just one module for you to modify or maintain as you make improvements to the program.
Although you can’t create new form controls using the Access object-oriented development tools, you can add many capabilities to your applications through class modules alone.
For instance, most applications include extensive data-validation routines. Depending on the type of data the user enters, data validation ranges from one line of code to extensive modules containing dozens or hundreds of lines of code. Using Access’s OOP features, you can wrap all data-validation routines into a single object you can use by setting its properties and invoking its methods.
Custom objects, therefore, provide a simplified interface to complex operations. When properly designed and implemented, you can use the custom objects you create in Access in virtually any compatible VBA programming system, exposing the same properties and methods you work with when incorporating the objects in your Access databases.
Object basics
Our world is filled with objects. The car you drive, the computer you use, and the radio you listen to are all examples of objects. Some objects, such as a desk lamp, are relatively simple, while other objects, such as a stealth bomber, are considerably more complex.
In addition to physical objects, our world is filled with objects you can’t feel or touch. Electricity, sound, and light are all examples of objects people can produce, measure, and use, but you can’t sense them as physical entities. An object’s visible characteristics have little to do with its value to people. The electricity coursing through your computer’s circuitry can be as valuable as the car you drive, under the right conditions.
You’ll find any number of visible and invisible objects in most Access databases. And, just as with the objects that make up our environment, the invisible objects in an Access database can be as valuable as the forms, menus, and ribbons the user sees.
An Access object is a programmable entity of one sort or another. The Err object is an example of an invisible, but valuable, object built into Access. You use the Err object’s properties (Number, Description, and so on) to determine which error has occurred. The Clear method resets the Err object, preparing it for the next error to occur. Even though the Err object never appears on an Access form or report, it has an important role in every professional Access application.
What’s an object?
Although there’s an endless variety of objects, all objects have a number of features in common. An object is a programmable entity; most objects contain a number of properties you can read or set at runtime. In addition, most objects include methods you can execute to perform tasks. An object’s properties and methods define the object’s interface to the rest of the program.
You can write custom objects to adapt to changing environments and user requirements. Most often you can exploit an object’s programmable nature by changing its properties and invoking its methods. But you can engineer a custom object in such a way that the object automatically adapts to differing conditions by running different internal routines.
You can create most object types multiple times in an application. Each time you create the object, Access assigns it a unique name to distinguish it from other instances of the object. In other words, a single Access program can host more than one instance of the object, each object operating independently of the others (possibly even cooperating with the other objects), and maintaining its own set of properties and other data.
For example, say the Northwind Traders database (included with Microsoft Access) contains a Product object. The class module supporting the Product object defines the Name, Supplier, UnitPrice, and other properties of the product. There are any number of Product objects in the Northwind Traders database, each with its own name, price, and supplier.
To carry the analogy further, another class module might define a ProductInventory collection object that contains a number of Product objects. The ProductInventory class would feature a Count property that tells you how many Product objects are in the collection. The ProductInventory class module might contain a Sell method that deducts a certain Product item from the ProductInventory.
Using objects in applications
Every time you’ve written code setting a label’s Caption property or returning the contents of a text box’s Value, you’ve worked with objects. Although a label or text box control is a simple type of object, the principles behind these objects are the same as using more complex and intelligent objects you create yourself.
The following Access VBA code shows a series of statements that are typical of how you’d use objects in Access applications.
Dim ObjectName As ObjectClass
Set ObjectName = New ObjectClass
‘Setting a property of the object:
ObjectName.SomeProperty = SomeValue
‘Invoking a method of the object:
ObjectName.SomeMethod
In this code, the name of the object is ObjectName and its object class (described in the next section) is ObjectClass. You declare the object in the Dim statement and the New keyword instantiates (creates) it. SomeProperty is a property of the object, and SomeMethod is a method of the object.
Class module basics
You define an object by the code in a class module. You must add a class module to your Access application, and then add the property and method code to the module before using the object the class module defines. The name of the class module is the name of the object’s class.
A class module is a special type of code module. Access recognizes the module as an object’s definition and lets you create new instances of the object from the code in the module. Any of the object’s special features—including properties, methods, and events—are exposed as procedures tagged with the Public keyword in the class module. You should declare any code in the class module you intend for only the object to use, and won’t expose to the outside world, with the Private keyword.
Each object you create from the class module is an instance of an object class. For example, the Nissan Sentra is a particular class of automobile. The Nissan Sentra that your Uncle Joe owns is a particular instance of the Nissan Sentra class of automobile. Even though Uncle Joe’s car looks pretty much like every other Nissan Sentra, certain attributes of his car set it apart from all the other Nissan Sentras on the road.
Carrying the car analogy a bit further, consider the properties and methods of the automobile object class. A car has a color property that defines the color of the car’s exterior. It’s likely that the color of any car matches the color applied to other cars produced by the car’s manufacturer. A car also has a vehicle identification number (VIN) that isn’t shared with any other car anywhere in the world.
An object’s property values, therefore, are a combination of values shared with other objects of the same class and values unique to the particular instance of the class. In fact, there must be a property or some other attribute of the object that sets it apart from all other instances of the same type of object in the application. Otherwise, Access can’t know which instance you’re referring to in your code.
If you were to construct a Product class module, you’d include properties such as Name (a string), UnitPrice (a currency data type), UnitsInStock (an integer or long integer), ReorderLevel (also an integer or long integer), and Discontinued (a Boolean value). Depending on how you’ll use the product object in the application, you may add properties to contain the quantity per unit, the category ID, and other information relevant to the application. You’ll also want to add the ProductID property to uniquely identify each instance of the product object.
You may have noticed that all the properties we mention in the preceding paragraph correspond to the fields in the Products table in the Northwind Traders database. In fact, often each instance of the object represents a record contained in a database table.
Because you’re constructing the class in VBA code, you can add any properties necessary to support the application and the data you’re constructing. When you build Access classes, you have access to all the power and utility available through the Access data types and features. Adding new public procedures to the class module extends the properties and methods available to the object. You can, therefore, define new data types to accommodate whatever peculiarities your application requires.
In the class module, private variables handle property values. As you can see in the “Persisting property values” section, later in this chapter, the mechanism for implementing properties is part of the special attributes of class modules. You must follow certain rules and coding conventions to successfully implement properties in Access class modules.
In addition to properties, most objects support a number of methods, which are the actions that the class performs. An airplane has a number of rather obvious methods: ascend, descend, and land, among others. The classes you construct in Access implement whatever functionality you want the class’s objects to support. The Product object we describe earlier might have Sell or Discount methods not shared with a Customer object in the same database.
The methods of a custom object exist as public procedures (functions and subroutines) in the class module. And, just as with properties, you have the full power and flexibility of VBA at your disposal as you write the methods of your custom classes.
A simple class module
Most often, the classes in your applications will model some real-world object, such as customers, contacts, employees, and products. Your knowledge and understanding of the physical object translate directly into Access VBA code and become the properties and methods of the Access objects you create from the class module’s code.
This chapter’s database (Chapter32.accdb) implements a Product class similar to the one we describe in the previous sections. The product class module (clsProduct1) in Chapter32.accdb includes the properties and methods in Table 32-1 and Table 32-2, respectively.
The Product class object in Chapter32.accdb doesn’t completely model a real product. You can add many other properties and methods to this class to more effectively model a real product, but this simple class does show you how to approach modeling a physical object in Access.
You can describe the product you’d create from the class like this: A product Name and ProductID identify the product. A certain manufacturer (the Supplier) produces the product, and it is intended to be sold for a certain UnitPrice. Northwind Traders keeps track of the number of units in stock (UnitsInStock) and has determined the minimum number of units to keep in stock (ReorderLevel). The manufacturer may discontinue a product, in which case its ReorderLevel is set to zero and the UnitsInStock is allowed to decrease to zero as items are sold. Periodically, a product may be sold (the Sell method) and may also be discounted through the Discount method.
Adding a class module to a database
Choose Insert⇒Class Module to open a new class module in the editor window, or select the Macro drop-down list in the Other group of the Access Create ribbon tab, and choose Class Module.
It’s a good idea to click on the Save button on the Code Editor toolbar and assign a name to the class module early in its development cycle. The name you provide for the class module becomes the name of the object’s class when creating objects from the class module (see Figure 32-1). The name you provide for class module is similar to the names you’ve given other objects in your databases.
Figure 32-1
You’ll use the name you provide for the class module as the object’s class name.
The class name should be descriptive but not excessively long. Furthermore, the name should be meaningful to you. Users never see the name of the class, so use a name that means something to you or another developer.
The class module is in the code editor window in Figure 32-1. Notice the class module looks just like any other module in the editor window. Your only indication that it isn’t a normal module is the tiny icon in the left corner of the module as it appears in the code editor. It’s a little box icon, rather than the “tinkertoy” icon you see in standard modules.
Creating simple product properties
The easiest way to establish the properties of a class, and the technique you’ll use in your first class example, is to simply declare each of the properties as a public variable in the clsProduct1 class module. Adding a public variable to a class module creates a new property for the class. The variable’s public scope makes it accessible to other routines in the database. Later in this chapter, in the “Using Property Procedures” section, you’ll see an alternate way to create properties for your class modules.
Public ProductID As Long
Public Name As String
Public Supplier As String
Public UnitPrice As Currency
Public UnitsInStock As Integer
Public ReorderLevel As Integer
Public Discontinued As Boolean
Figure 32-2 shows the class module after you’ve added the public variables.
Figure 32-2
Public variables in a class module become properties.
Access treats each public variable in a class module as a property of the objects created from the class. Because you declare the public variables in a class module, Access uses the variables as properties of the class’s objects without further work on your part. Figure 32-3 shows how IntelliSense displays the properties in the Auto List Members drop-down list in a module using an object created from the class.
Figure 32-3
IntelliSense shows you the properties and methods created for the new object class.
The names you provide for an object’s properties and methods should be descriptive and easy to recognize. Because the class’s properties are variables in the class module, the names you assign to these items must conform to VBA’s variable naming requirements. That is, property names should be 64 or fewer characters and contain only alphanumeric characters and the underscore character. Property names must begin with an alphabetic character and should never begin with the underscore character or a number.
Creating methods
The clsProduct1 class includes two methods. These methods, like all object methods, define actions supported by the objects created from the class. Each method is nothing more than a public procedure in the object’s class module.
The following code example shows the procedure implementing the Sell method. Because all procedures in a class module are public by default, the Public keyword is optional and you add it to the Sell method to clarify the status of the procedure.
Public Sub Sell(UnitsSold As Integer)
Me.UnitsInStock = Me.UnitsInStock - UnitsSold
End Sub
Notice there’s nothing special about the Sell method. There’s no special declaration for this procedure, nor is there reference to its status as a method of the class. Methods are an example of how Access treats class modules differently from simple code modules. As long as you haven’t declared the procedure (sub or function) with the Private keyword (remember the Public is the default!), Access treats the procedure as a method of the objects created from the class module.
Because it’s a subroutine, the Sell method doesn’t return a value. If you had declared it as a function, it could return any valid Access data type. The Sell procedure requires an argument specifying how many items were sold.
Notice the use of the Me keyword in the previous code example. In this context, Me refers to the object instance created from the class module.
You may have noticed an obvious bug in the Sell method. If the UnitsSold is larger than the UnitsInStock, the UnitsInStock value will be a negative number after the method runs. To fix this bug, you must add a couple of lines of code to the method:
Public Sub Sell(UnitsSold As Integer)
If UnitsSold > Me.UnitsInStock Then
Exit Sub
End If
Me.UnitsInStock = Me.UnitsInStock - UnitsSold
End Sub
This change causes the Sell method to simply exit and not deduct any units when the UnitsSold value would result in a negative value for the UnitsInStock.
Obviously, there’s much more you could add to the product class. I’ve included the complete class module in the Chapter32.accdb example database as the clsProduct1 module in the Modules tab of this database.
The Discount method is similar to Sell (see the following code example). In this case, the method ends immediately if the Percent is less than 1 or larger than 99. Otherwise, the object’s UnitPrice property is discounted by an expression derived from the Percent and current UnitPrice.
Public Sub Discount(Percent As Integer)
If Percent < 1 _
Or Percent > 99 Then
Exit Sub
End If
Me.UnitPrice = _
Me.UnitPrice - ((Percent / 100) * Me.UnitPrice)
End Sub
Eventually, with enough work and attention to detail, you can refine the product class to the point where it would support all the features and requirements of a real product sold by Northwind Traders. Other classes could model other data in the Northwind database such as customers, employees, and orders. Later in this chapter, you’ll see some of the advantages of using class modules in your Access applications.
Using the product object
After you’ve assembled the class module from properties and methods, you can create new objects from the class. Figure 32-4 shows frmProductUnbound, a form included in Chapter32.accdb, the database accompanying this chapter. The text boxes along the left side of this form display the object’s properties. The buttons to the right side of this form invoke the object’s methods.
Figure 32-4
frmProductUnbound creates an object from clsProduct1 and provides an interface to its properties and methods.
The code behind frmProductUnbound is quite simple.
Creating a new product object requires you to use the New keyword. This statement is one way to create a new instance of a product object from the clsProduct1 class module:
Private Product As New clsProduct1
Alternatively, you can first declare the Product object, then instantiate as separate statements. For instance, place this statement in the module’s Declarations section to establish the clsProduct1 object:
Private Product As clsProduct1
The object instantiates in the form’s Load event procedure:
Set Product = New clsProduct1
We prefer using separate statements for declaration and instantiation. It isn’t possible to trap errors when declaration and instantiation are processed as a single statement, which means your application may exhibit instability in some situations.
In either case, the code creates the new Product object instance at the instant the New keyword executes. The code behind frmProductUnbound uses the two-statement approach to creating the Product object: In frmProductUnbound, you declare the product in the form’s Declarations section as a module-level variable, and then the object instantiates during the form’s Load event. Therefore, the Product object is available as soon as the form opens on the screen, and it’s accessible to all the code behind the form.
The code in the form’s Load event procedure also fills a recordset object with records from tblProducts. You then use this recordset to set the Product object’s properties. A private subroutine named SetObjectProperties retrieves values from the recordset and sets the object’s properties to those values:
Private Sub SetObjectProperties()
‘Set the product object’s properties:
With Product
.ProductID = rs.Fields(“ProductID”).Value
.Name = rsFields(“ProductName”).Value
.Supplier = rsFields(“Supplier”).Value
.UnitPrice = rsFields(“UnitPrice”).Value
.UnitsInStock = rsFields(“UnitsInStock”).Value
.ReorderLevel = rsFields(“ReorderLevel”).Value
.Discontinued = rsFields(“Discontinued”).Value
End With
End Sub
After you create the product, you can reference its properties and methods. References to the product object’s properties are similar to property references anywhere else in VBA. This statement retrieves the current value of the product’s UnitPrice property and assigns it to the text box named txtUnitPrice on frmProductUnbound:
txtUnitPrice.Value = Product.UnitPrice
You can find a number of similar statements in the form’s FillForm procedure:
Private Sub FillForm()
‘Fill the form with the product’s properties:
txtID.Value = Product.ProductID
txtName.Value = Product.Name
txtSupplier.Value = Product.Supplier
txtUnitPrice.Value = Product.UnitPrice
txtUnitsInStock.Value = Product.UnitsInStock
txtReorderLevel.Value = Product.ReorderLevel
txtDiscontinued.Value = Product.Discontinued
End Sub
frmProductUnbound makes several property assignments from the form’s Load event procedure. The following code listing shows the entire Form_Load sub from frmProductUnbound. Notice how the code builds the recordset, makes the property assignments, and fills the text boxes on the form through the SetObjectProperties and FillForm procedures.
Private Sub Form_Load()
Set Product = New clsProduct1
Set rs = CurrentDb.OpenRecordset(“tblProducts”)
If rs.RecordCount > 0 Then
Call SetObjectProperties
Call FillForm
End If
End Sub
Similarly, selling a product involves using the object’s Sell method. The code below shows how a form might use the Sell method. Notice the code passes a parameter (txtNumberToSell). The user has entered the number of items to sell into a text box named “txtNumberToSell. That value becomes the UnitsSold argument for the Sell method we discuss earlier in this chapter.
Private Sub cmdSell_Click()
Product.Sell txtNumberToSell
Call FllForm
End Sub ‘cmdSell_Click
The FillForm procedure is called to refresh the form’s contents after the Sell method executes.
Create bulletproof property procedures
In many cases, assigning an invalid value to a property results in a runtime error or other bug. If you’re lucky, the invalid value causes the application to halt and display an error message to the user. It’s much worse to have the application continue operating as if nothing is wrong when, in fact, the class module is working with invalid data. The best situation is when the class module itself validates property values as they’re assigned, instead of waiting until the properties are used by forms, reports, and code in the application.
For instance, consider a banking application that calculates exchange rates for foreign currency deposited in the bank’s vault. A class module is the ideal vehicle for handling foreign currency exchange calculations. Keeping these calculations in a class module isolates these complicated routines from the rest of the application and makes it easy to maintain the calculations as currency values fluctuate. And, because class modules support IntelliSense, it’s much easier to work with objects defined by class modules than public procedures stored in standard modules.
Ideally, the exchange rate class module wouldn’t accept invalid exchange ratios or would check the exchange ratios that the user inputs at runtime. Perhaps the class module could check online sources such as The Wall Street Journal or other financial publications to verify that the data the user input is correct.
Property errors might occur if the code passes a string when a numeric value is required or when a property value is less than zero. The following methods help bulletproof properties and avoid runtime errors:
• Set default property values if the code passes an inappropriate data type. Use a conversion routine to correct the value, if possible.
• Use private procedures in the class module to validate data types. These data-validation routines are often class-specific.
• Use error trapping everywhere in the class module, especially on the class’s properties and methods. The property procedures and methods (the public procedures in the class) are where most unexpected behaviors occur.
Keep in mind that a basic principle of using object-oriented programming is encapsulating functionality. Whenever possible, you should include anything that affects how the class operates in the class module. Keeping the property validation, method error handling, and other features in the class module makes the class more portable and reusable.
Encapsulation isn’t well implemented in the clsProduct1 example presented in this section. For instance, the form’s code retrieves the data, and assigns values to the product object’s properties. A better approach would be to have all the data management performed by the class itself, isolating the form from the data-management operations. A form using a properly-constructed class shouldn’t have to know which database table contains the product data; instead, the form should be a strict consumer of the product data.
Other Advantages of Object-Oriented Programming Techniques
The simplest way to add properties to a class is to include public variables within the class module. In fact, anything declared with the Public keyword is exposed by the class as either a property or a method. In the “Creating simple product properties” section, earlier in this chapter, you can see public variables used to define properties. The following sections explain using property procedures, a more robust and sophisticated way to define properties, and explain in detail the requirements and rules governing the properties in a class.
The mix of properties (and their data types), methods (and the arguments accepted or returned by the methods), and the events supported by a class are referred to as the class’s interface. A developer working with an object created from a class module is typically unable to access the class’s interface, and not the code within the class (unless, of course, the class’s creator and the developer working with the class are the same person). Very often, class modules are bundled as Access libraries, or distributed as .mde or .accde files, and the interface is the only hint a developer has of the operations supported by the class (unless printed or online documentation accompanies the class).
A class’s interface is revealed by the Object Browser (press F2 with the Code Editor window open). Figure 32-5 shows the Object Browser open to the Product2 class, revealing the properties, methods, and events supported by this class.
At the bottom of the Object Browser, you’ll see that ProductName is defined as a public property and is a string data type. This area is where you’d see that a property is read-only or write-only. Also, all private elements are identified accordingly. Finally, notice how all the property variables are sorted together because of the m_ prefix. You can read about property variables in the “Using Property Procedures” section, later in this chapter.
Figure 32-5
The Object Browser reveals a class’s interface.
Figure 32-6 illustrates one of the most valuable aspects of object-oriented programming. Notice how the IntelliSense Auto List Members drop-down list shows you all of the appropriate interface elements as soon as the object is identified and the dot is typed. This is a huge benefit to anyone working with your class module.
Figure 32-6
The Auto List Members drop-down list makes it easy to select an object’s properties or methods.
Furthermore, if you position the input cursor anywhere within the property name (such as ProductName) and press Shift+F2, the class module opens, showing you the code associated with the property (see Figure 32-7).
Figure 32-7
Shift+F2 shows you the code associated with an object’s property.
The class module’s VBA code must be available for the Shift+F2 shortcut to work, of course. If the class has been bundled as an .mde or .accde file or is otherwise unavailable, Shift+F2 will not work.
Generally speaking, object-oriented programming techniques are most often applied to unbound applications. Although it is possible to build an Access application with a mix of bound, unbound, and object-oriented techniques, using bound forms misses one of the main advantages of object-oriented programming. Most developers turn to object-oriented programming techniques because they want more control over how the data are used by their applications. Using bound forms negates many of the considerable advantages of using object-oriented programming techniques without really adding anything of value to the project.
Also, most developers using object-oriented programming techniques are fairly advanced and are comfortable building unbound applications. The extra code involved in building classes containing properties and methods is not a hindrance to the majority of advanced Access developers.
Object-Oriented Programming Rules
There are two cardinal rules that you must obey when applying object-oriented programming techniques. We didn’t make up these rules, but we know from personal experience that you’re asking for trouble when you fail to pay adequate attention to them.
Never reveal a user interface component, such as a message box, from a class module
This rule is perhaps less important in Access applications than in other systems, but ignoring this rule may cause problems later on.
Here’s why this is important: Consider a class that opens a message box to the user, indicating that a problem has arisen. Although this works fine in Access environments, this practice may cause problems if the class is ported to other environments.
All Access applications run locally on the user’s computer. Therefore, opening a dialog box from an Access class module is guaranteed to open on the user’s computer. In an Access application, there’s no way to cause a message box to appear on another computer.
However, other development platforms support the notion of remoting, which means running code on an application server. Most often, the remoted component is implemented as a set of compiled classes, and if one of those classes opens a dialog box, the dialog box opens on the remote application server.
In this case, the application freezes in front of the user, and the user has no idea what happened. All the user knows is that the code stopped running. The code on the remote machine has stopped running, waiting for a response to the dialog box that has opened on the application server.
For obvious reasons, you’re not going to make many friends if your application causes an application server to stop running!
Preserve the class’s interface as the class is updated
You can add to the interface by introducing new properties, methods, and events, but you should never alter the data type of existing properties or method arguments, or remove an event from a class module.
It is very difficult to know where a class may be used, and once a class has been distributed, any changes to the class may break code in many different places without warning.
Sometimes it’s impossible not to change a property’s value or modify a method’s arguments. As an example, users may require an additional argument to be passed to the SellProduct method so that shipping charges can be accurately calculated. Unless you take care to preserve backward compatibility, the consumer code referencing the original version of the SellProduct method is sure to fail.
One technique I’ve seen used to ensure backward compatibility is to duplicate the property or method, suffixing a numeric value to its name. For example, you might add SellProduct1 to the class module, leaving the unchanged, original SellProduct for older code. New code will use the updated SellProduct1 to take advantage of the shipping charges calculation.
Using Property Procedures
The concept of property procedures is fundamental to object-oriented programming. As the name implies, a property procedure is a VBA procedure that defines a property for a class. Most classes contain several to many property procedures.
There are three types of property procedures:
• Property Get: Retrieves the value of a property. A Property Get works very much like any function, and follows the same pattern as any VBA function.
• Property Let: Assigns a new value to the property. Property Let works only for simple data types such as numeric, strings, and date properties.
• Property Set: Assigns a value to an object property. You would use a Property Set for a property defined as a recordset or other object data type.
The concepts behind property procedures are illustrated in Figure 32-8. Each type of property is detailed a bit later in this chapter. In the meantime, be aware that each time your code references a property, the class module responds by running the appropriate property procedure.
Figure 32-8
Each time you read or write an object’s properties, the class module runs a property procedure.
Property procedures are always public by default. Even if you omit the Public keyword, your property procedures is exposed to the other elements of your applications. You should, however, always use the Public keyword to clarify the property procedure’s scope. It never hurts to be very explicit in your code.
The properties you add to your classes can be read/write, read-only, or write-only, depending on how you expect the property to be used. Omitting either the Property Get or Property Let (or Property Set, for that matter) makes the property read-only or write-only, respectively.
Omitting the Property Let (or Property Set for object properties) makes a property read-only. A consumer can read the property’s value through the Property Get procedure, but cannot assign a new value to the property.
Obviously, because there is no way to assign a value to a read-only property, the class must provide the read-only property’s value. This is often done by extracting a value from a database, or from the System Registry, or by reading a value from an .ini file or the operating system. Because a Property Get is a procedure, you can add any logic your class requires to obtain the property’s value.
Omitting a Property Get makes a property write-only. You may decide to use a write-only property for sensitive information such as passwords and login identities. Making a write-only property is an excellent way to preserve the security of sensitive data. Write-only properties are also used to provide a class with information that it needs to support its activities, such as a connection string or database name.
Persisting property values
At this point, we know that properties can be read/write, read-only, or write-only. What hasn’t been explained is where the property persists the value when the property is written, and where the property gets its value when the property is read.
In a VBA project, property value persistence is mediated through private variables contained within the class module. Generally speaking, each property is accompanied by a private variable that is the same data type as the property. This means that a property that reads or writes a string value will be accompanied by a private string variable, and each date property will be accompanied by a private date variable.
As you’ll see in the next sections, the property variables are either assigned or returned by the property procedures. A property variable should be given a name that indicates which property owns the variable. In the examples accompanying this chapter, each property variable has exactly the same name as its property, and is tagged with an m_ prefix. For example, the property variable for the CustomerID property is named m_CustomerID. Furthermore, because the CustomerID property is a string, m_CustomerID is also a string.
There are cases, of course, where a property is not accompanied by a variable. For instance, a read-only property may extract the value from a database file or retrieve it from the operating system. Or, the property might be write-only, in which case the property may act immediately on the value passed to the property procedure, and no storage is necessary.
Property Let syntax
As described earlier, the Property Let procedure assigns a value to a property. The property’s value is passed into the procedure as an argument, and the value is then assigned to the class module’s private variable that stores the property’s value.
The following example is a prototype for any Property Let procedure:
Public Property Let <PropertyName>(Value As <DataType>)
<PrivateVariable> = Value
End Property
The property’s argument can be named anything you want. We always use Value as the argument name. Consistently using Value is simpler than assigning a meaningful name to the argument, and is consistent with how property values are assigned to built-in Access properties.
The following example is from the Employee class module:
Public Property Let LastName(Value As String)
m_LastName = Left$(Value, 20)
End Property
This small example hints at the power of property procedures. Notice that the Value argument is a string. The statement within the property procedure assigns only the 20 leftmost characters of the Value argument to the m_LastName variable. This is because the LastName field in the Northwind Employees table only accepts 20 characters. Many database systems generate errors if more characters are sent to a field than the field can hold. Adding a little bit of logic to a property procedure can go a long way toward bulletproofing an application.
Property Set syntax
The syntax of Property Set is parallel to the Property Let procedure. The only difference is that the argument is an object data type, and the VBA Set keyword is used for the assignment within the body of the Property Set. The following is an example of hypothetical Property Set procedure that accepts a recordset object and assigns it to a private variable named m_Products:
Public Property Set Products(Value As ADO.Recordset)
If Not Value Is Nothing Then
Set m_Products = Value
End If
End Property
In this small example, the argument is validated before it is assigned to the private variable.
Property Get syntax
This is the basic syntax of the Property Get:
Public Property Get <PropertyName>() As <DataType>
<PropertyName> = <PrivateVariable>
End Property
Notice the similarities between a Property Get and a VBA function. The Property Get is declared as a particular data type, and the property is assigned a value within the body of the property. The syntax is identical to any VBA function.
This is the Property Get from the Employee class module in the example application accompanying this chapter:
Public Property Get LastName() As String
LastName = m_LastName
End Property
The Property Get executes whenever the property’s value is assigned to a variable or otherwise used by the application. For instance, the following VBA statement executes a Property Get named LastName in the Employee class module (objEmployee has been declared and instantiated from the Employee class):
strLastName = objEmployee.LastName
Notice that this statement does not directly reference the Property Get. Because the objEmployee object was created from the Employee class, the VBA engine knows to run the Property Get because a variable is assigned the value of the LastName property. In other words, the VBA engine gets the LastName property value from the class.
In this example, the Property Get is very simple and only returns the value of the private variable. However, you could have a much more complex Property Get that performs data transformation on the value or retrieves the value from a database file, an .ini file, the operating system, or some other source.
This example also illustrates the simplified programming possible with object-oriented techniques. A single VBA statement in the application’s consumer code is enough to run whatever complex operation is necessary to retrieve the value of the property. The consumer is never aware of the logic supporting the property.
Property procedure rules
There are just a few rules that apply to property procedures. First of all, the name assigned to a property procedure is the name of the property. Therefore, you should use a descriptive, helpful name for all of your properties. Typically, a developer using objects created from a class you create does not have access to the VBA code in the class and has to rely on the names you’ve assigned to its properties and methods for guidance.
Also, the data type of the Property Let, Property Get, and the private variable must coincide. For example, if the property is defined as a string, the private variable must be a string. Figure 32-9 illustrates this concept.
Figure 32-9
The property variable data type must coincide with the property’s data type.
Note the following points in Figure 32-9:
• The property variable is declared as some data type (labeled “A” in Figure 32-9).
• The argument to the Property Let procedure is the same data type as the property variable (“B” in Figure 32-9).
• The property variable is assigned its value in the body of the Property Let (“C” in Figure 32-9).
• The Property Get procedure returns the same data type as the property variable (“D” in Figure 32-9).
• The Property Get is assigned the value of the property variable (“E” in Figure 32-9).
You’ll get the following error if the data type assigned by the property procedures does not coincide:
Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalid Set final parameter.
Although you can use an incorrectly typed private variable for your property procedures, you’ll encounter side-effect bugs if the variable does not match the data type used for the property procedures.
Extending the Product Class
Earlier in this chapter, we built a simple product class representing a Northwind product. The initial class is included in the Access .accdb file accompanying this chapter as the clsProduct1 class module. In this section, we extend the initial class (as the clsProduct2 class module) by making its properties more intelligent and useful.
Specifically, this section extends the property procedures within the Product class module, and adds methods to the module. We also expand the basic application by adding a few other classes needed to support the Northwind Traders application.
The example application accompanying this chapter includes a form named Products_OOP, which is based on the Products form included with Northwind Traders (see Figure 32-10). This form utilizes the majority of OOP techniques described in this chapter and can serve as a model for your OOP endeavors.
Figure 32-10
The Products_OOP form demonstrates unbound object-oriented techniques.
Retrieving product details
The first enhancement to the Product class is to update the process of retrieving product details, given a particular ProductID. In the initial example, the user selected a product from a combo box, and the form used an inline SQL statement to extract the details for the selected product.
The problem with having the form directly manage data are that the form (which is the consumer of the product data) has to know a great deal about how the product data are stored. The form holds a hard-coded SQL statement, creates a recordset with product data, then assigns the recordset’s data to the product object’s properties. This is far too much to entrust to the user interface.
Consider an application with perhaps hundreds of forms. Using the design described in the previous paragraph, each form in the application has to manage its own data. Changing anything in the database means many different changes have to be made to the user interface, greatly complicating maintenance.
Two of the primary objectives of object-oriented programming are code-reuse and data abstraction. We all know and understand code reuse: Write the code once, and use it many different places. Data abstraction is a bit more complex, but it’s based on the notion that each layer of an application (data management, business logic, and user interface) should do what it does best, and not have to worry about other parts of the application. The data layer should concern itself with getting data into and out of the data source. The business logic should concern itself with the rules that drive the application, and the user interface presents data from the user and manages the application’s interaction with the user.
Bundling all of those operations behind or within a form violates the notion of data abstraction. Every form in a bound application knows everything about the data managed by the form. Although this works well in small applications where complete control over the data are relatively unimportant, larger, more ambitious applications generally require significant control over the data.
The new ProductID property
The ProductID property enhancement is quite simple, even though the implementation requires a bit of code. The Property Get procedure simply returns the value of m_ProductID, as described earlier in this document. The real change comes with the Property Let.
The enhancements works like this: If a value greater than zero is assigned to the ProductID property, the class retrieves all of the product details matching the assigned ProductID. Each product detail selected from the database is assigned to the corresponding product property. If a value zero or less is assigned, the class assumes the product entity is a new product, and default values are assigned to each property.
The updated Product class is utilized behind a form named Products_OOP.
The code contained in the ProductID Property Let is fairly extensive. It begins by opening a recordset against the ProductID value, and then determines whether any data was selected. A small bit of logic then either assigns the found data to the property variables, or sets the property variables to default values:
Public Property Let ProductID(Value As Long)
Dim db As DAO.Database
Dim rs As DAO.Recordset
m_ProductID = Value
If m_ProductID <= 0 Then
Exit Property
End If
Set db = CurrentDb()
Set rs = db.OpenRecordset( _
“Products”, dbOpenTable)
rs.Index = “PrimaryKey”
‘Seek the Product record matching
‘the m_ProductID value.
rs.Seek “=”, m_ProductID
If Not rs.NoMatch Then
‘Assign database data to object properties:
If IsNull(rs.Fields(“ProductName”).Value) Then
m_ProductName = vbNullString
Else
m_ProductName = rs.Fields(“ProductName”).Value
End If
<This pattern is repeated for each property>
Else ‘Product not found!
‘Assign default values to
‘each property variable:
m_ProductName = vbNullString
m_SupplierID = -1
m_CategoryID = -1
m_QuantityPerUnit = vbNullString
m_UnitPrice = -1
m_UnitsInStock = -1
m_UnitsOnOrder = -1
m_ReorderLevel = -1
m_Discontinued = False
‘Also assign default value to ProductID.
‘This will serve as a signal to the consumer
‘that an product was not found:
m_ProductID = -1
‘An alternate approach would be to raise an
‘event telling the consumer that the product
‘could not be found.
End If
End Property ‘Property Let ProductID
This is a good example of encapsulation. Instead of requiring a consumer of the Product class to select the product data, the Product class easily supplies the data through the ProductID setting.
This small example also illustrates one of the major benefits of object-oriented programming. In a well-designed application, the only way to retrieve product data should be through the Product class. No other portion of the application needs to know anything about where the product data are stored, how to select or insert product data, and so on. In the future, should the need arise to change the product data source, only the Product class is updated, and all other portions of the application continue to function as before, without any changes.
Consider the time savings in a large application where the product data are used in dozens or even hundreds of different places. Good object-oriented design enforces modular programming and provides significant efficiencies when maintaining medium to large applications.
A new property
One of the things that bothers me about the Northwind Traders application is that it relies very heavily on Access-only constructs. In particular, most of the tables, when viewed in datasheet view, display related data. For instance, opening the Products table in datasheet view shows the product category and supplier information, and not the ID values associated with each of these items. For instance, the supplier name is shown in the Products table because the lookup properties of the SupplierID field are set to display a combo box containing the supplier names.
We’ve found these constructs to be confusing to users, especially people new to Access. Most people, when they see the supplier’s name in the Products table, expect to find the supplier name among the data stored in the table. However, the only type of supplier information in the Products table is the SupplierID. If the supplier name is required, you must extract it the from the Suppliers table, using the SupplierID as the criterion.
An enhancement to the Product class is to make the supplier and category names accessible as read-only properties. You probably can guess how this is done: Simply extract this information from the respective tables, using the property variables for the SupplierID and CategoryID properties.
Here is the Property Get procedure for the new SupplierName property. The Property Get for the CategoryName property is virtually identical:
Public Property Get SupplierName() As String
Dim varTemp As Variant
If m_SupplierID <= 0 Then
SupplierName = vbNullString
Exit Property
End If
varTemp = DLookup(“CompanyName”, “Suppliers”, _
“SupplierID = “ & m_SupplierID)
If Not IsNull(varTemp) Then
SupplierName = CStr(varTemp)
Else
SupplierName = vbNullString
End If
End Property
The Property Get uses DLookup to retrieve the CompanyName from the Suppliers table that matches the m_SupplierID property variable. The property variable is first checked to make sure its value is greater than zero, and the property ends if this condition is not met.
The SupplierName property is an example of how a class module can be enhanced by introducing new properties—either read-only, write-only, or read/write—that provide functionality not otherwise available. Again, the consumer of the class needn’t know anything about the underlying data structures, and all of the data management is handled through the class module.
Product Methods
Another major advantage of encapsulation is that, because all data operations required by the entity are contained within the class, it’s quite easy to update business logic.
Earlier in this chapter, you read about a hypothetical SellProduct method that had to be updated to accommodate a new sales tax. Whichever technique you use to update the method, the end result is the same. Because the method is an integral part of the class, there is only one update needed to update all uses of the SellProduct method in the application.
The previous section dealt with an update to the ProductID property. In the new ProductID Property Let, the property variable was assigned –1 when it appeared that the product was a new product. Here’s how the SaveProduct method would handle the various values of the m_ProductID variable:
Public Function SaveProduct() As Boolean
Dim db As DAO.Database
Dim strSQL As String
On Error GoTo HandleError
Set db = CurrentDb()
If m_ProductID > 0 Then
‘Update existing record:
strSQL = _
“UPDATE Products SET “ _
& “ProductName = ‘“ & m_ProductName & “‘“ _
& “SupplierID = “ & m_SupplierID _
& “CategoryID = “ & m_CategoryID _
& “QuantityPerUnit = ‘“ _
& m_QuantityPerUnit & “‘“ _
& “UnitPrice = “ & m_UnitPrice _
& “UnitsInStock = “ & m_UnitsInStock _
& “UnitsOnOrder = “ & m_UnitsOnOrder _
& “ReorderLevel = “ & m_ReorderLevel _
& “Discontinued = “ & m_Discontinued _
& “WHERE ProductID = “ & m_ProductID
Else
‘Insert new record:
strSQL = _
“INSERT INTO Products (“ _
& “ProductName,” _
& “SupplierID, “ _
& “CategoryID,” _
& “QuantityPerUnit, “ _
& “UnitPrice,” _
& “UnitsInStock, “ _
& “UnitsOnOrder,” _
& “ReorderLevel, “ _
& “Discontinued, “ _
& “)VALUES(“ _
& m_ProductName & “, “ _
& m_SupplierID & “, “ _
& m_CategoryID & “, “ _
& m_QuantityPerUnit & “, “ _
& m_UnitPrice & “, “ _
& m_UnitsInStock & “, “ _
& m_UnitsOnOrder & “, “ _
& m_ReorderLevel & “, “ _
& m_Discontinued & “)”
End If
SaveProduct = True
ExitHere:
Exit Function
HandleError:
SaveProduct = False
Resume ExitHere
End Function
The code in the SaveProduct method is straightforward. If the m_ProductID variable is larger than zero, the record in the Products table matching the ProductID is updated. Otherwise, a new record is inserted into the Products table.
Class Events
There are two very important built-in events that accompany every Access class module. These are the Initialize and Terminate events. As you’ll soon see, these two events provide invaluable assistance in many object-oriented programming projects.
Using class events is one of the things that is completely different from using standard code modules. Not only do class modules maintain their own data states, they provide events that provide a great deal of control over how the data are initialized and cleaned up within the class.
The Class_Initialize event procedure
Very often the property variables or other resources used by a class need to be initialized or set to some beginning state. Other than adding a method to trigger initialization, it may not seem obvious how to add initialization operations to your classes.
For instance, let’s say you create a class module that needs to have a recordset open the entire time the class is used. Perhaps it’s a class where the data needs to be frequently selected from a database. Frequently opening and closing connections and recordsets can be a unnecessary drain on performance. This is especially true when the selected data set doesn’t change from operation to operation. It’d be much more efficient to open the recordset one time, leave it open while the class is being used, and then close it at the conclusion of the session.
That’s where the class’s Initialize event comes in. The Initialize event fires whenever an object is instantiated from the class module. In the following consumer code example, the Class_Initialize event procedure runs when the object is set to a new instance of the class:
Dim objProduct As Product
Set objProduct = New Product
Select Class from the object drop-down list in the upper-left corner of the VBA editor, then select the Initialize event from the Events drop-down list in the upper-right corner. There’s nothing else you must do other than add the code you want to run when an object is instantiated from your class module. Figure 32-11 shows an example of a Class_Initialize event procedure in the Product class.
Figure 32-11
The Class_Initialize event procedure runs whenever an object is instantiated from the class module.
The sequence indicated by the numbers in Figure 32-11 is:
• The object is instantiated (A). Before this statement is completed by the VBA engine, the Class_Initialize event is invoked.
• Notice that Class_Initialize (B) is a private subroutine. It is owned by the class, and executes independently of the consumer code. No arguments are passed to Class_Initialize.
• Execution is passed back to the consumer code when Class_Initialize ends (C).
• Execute recommences in the consumer code at the statement following the object instantiation (D).
In this small example, you’ll notice that numeric property variables are set to –1, rather than VBA’s default of zero for numeric variables. This is because certain logic in the class module uses –1 to determine when certain states, such as when the user is entering a new product, are in effect.
The Class_Terminate event procedure
The opposite of the Initialize event is the Terminate event. The Terminate event fires whenever an object created from the class is set to Nothing, or goes out of scope. In the following code fragment, the Class_Terminate event procedure runs when the object is set to Nothing:
Set objProduct = Nothing
Use the Terminate event to clean up your class module. For instance, if a Database or Recordset object has been opened, but hasn’t been closed by the class, use the Terminate event to perform these operations.
The Terminate event fires as the statement dismissing the object runs, not after. VBA processes one statement at a time, no matter where the statement takes the execution point. Therefore, when the Set objProduct = Nothing executes, the Class_Terminate event procedure runs before the statement ends. This sequence ensures that the class is cleaned up before execution is returned to the code using the class. This process is illustrated in Figure 32-12.
Figure 32-12
The Class_Terminate event procedure passes control back to the consumer code when it ends.
Just as with the Class_Initialize event procedure, the sequence of Class_Terminate’s execution is important:
• The object is set to Nothing, or goes out of scope (A). Before the statement causing these states executes, control is passed to Class_Terminate.
• Just as you saw with Class_Initialize, notice that Class_Terminate (B) is a private subroutine. It is owned by the class, and executes independently of the consumer code. No arguments are passed to Class_Terminate.
• Execution is passed back to the consumer code (C) when Class_Terminate ends.
• Execution recommences in the consumer code at the statement following the object’s dismissal (D).
Adding Events to Class Modules
We’re all familiar with the interfaces supported by the objects built into Microsoft Access. A TextBox object, for example, supports ForeColor and BackColor as properties. The DoCmd object provides a wide variety of methods (such as OpenForm) that perform a number of essential actions in Access applications.
Beginning with Access 2000, developers have been able to add events to the class modules in their applications. (Although Access 97 supported class modules with properties and methods, Access 97 did not provide for custom events in class modules.) Adding events to your class modules is an excellent way to enhance and strengthen the object-oriented elements you add to your applications.
An Access events primer
Events are a bit more complex than properties or methods. Even though we constantly use events in our applications, you never see an event (because events do not exhibit a use interface), and under most circumstances, you don’t deliberately invoke an event through your code. Events just sort of happen when a user clicks on a command button or tabs off of a control. Events are just there, and we use them as needed.
A reasonable analogy for events is the ringer on your cell phone. Your phone rings whenever someone wants to talk to you. The ring alerts you to the incoming call, and you decide whether to respond to the ring or ignore it.
From an object-oriented perspective, you add events to your objects so that the object has some way of notifying its consumer that something has happened within the object or has happened to the object. For instance, consider a data management object that reads and writes data from a data source. The properties are easy to understand and may include the path to the data source, the name of a table, and an ID value to use when extracting or saving data.
In this case, you may add an event to the data management object that is triggered when the data source is unavailable, or when a record matching the ID value cannot be found. Using events is much cleaner and more direct than relying on errors to be thrown when the data management object fails to complete its task.
The need for events
To my knowledge, there is no limit on the number of events you can add to a class module. You declare events in a class module’s header, and invoke the events within the class’s properties and methods.
This process may make more sense if we consider a property procedure built earlier in this chapter:
Public Property Get SupplierName() As String
Dim varTemp As Variant
If m_SupplierID <= 0 Then
Exit Property
End If
varTemp = DLookup(“CompanyName”, “Suppliers”, _
“SupplierID = “ & m_SupplierID)
If Not IsNull(varTemp) Then
SupplierName = CStr(varTemp)
End If
End Property
This property procedure returns the name of a product supplier, given the SupplierID (notice that the SupplierID is obtained through the class-level m_SupplierID variable). The SupplierName property assumes that the m_SupplierID property variable has already been set through the SupplierID Property Let procedure. The If..End If at the top of this procedure handles cases where the m_SupplierID variable has not been properly set to a value greater than zero.
So far, so good. But, what happens if the SupplierID cannot be found in the supplier table? The only way the class’s consumer can determine that the supplier does not exist is by examining the value of the SupplierName property. If the SupplierName property is an empty string, the consumer can assume the supplier cannot be found in the supplier table, and notify the user accordingly.
The problem with this scheme is that a lot of work is left up to the consumer. The consumer must first set the SupplierID property, then ask for the SupplierName property, and then finally examine SupplierName to see if a non-zero-length string was returned by the SupplierName Property Get.
One of the basic tenets of object-oriented programming is that a class module should encapsulate most, if not all, of the processing required by the entity represented by the class. In the case of our Product class, a consumer should not be required to examine a property’s return value to verify its validity. The class should notify the consumer when a problem (such as missing or invalid data) arises within the class.
And, that’s one of the primary purposes of events. The InvalidSupplierID event is invoked whenever the class determines that a problem exists with the SupplierID value supplied by the consumer code.
Creating custom events
Events must be declared within a class module. Although an event declaration may occur anywhere within a VBA module, it only makes sense to position event declarations near the top of the module where they are easily seen by other developers. An event declaration is actually quite simple:
Public Event InvalidSupplierID()
That’s all there is to an event declaration. The Public keyword is needed, of course, to expose the event to the class’s consumers. In effect, the Public keyword adds the event to the class’s interface. The Event keyword, of course, specifies that the declaration’s identifier (InvalidSupplierID) is an event, and should be managed by VBA’s class module hosting mechanism.
You may recall that I’ve asserted that class modules were special in a number of regards. Events are clearly one of the special characteristics of VBA class modules.
A quick look through the Object Browser at the class module (see Figure 32-13) shows that the class’s interface does, indeed, include the InvalidSupplierID event.
Figure 32-13
The InvalidSupplierID event appears in the Object Browser.
You’ll notice a couple other events (InsufficientStockAvailable and ProductSold) in the Product class module. We’ve added the other events in exactly the same manner as the InvalidSupplierID event. An event declaration is all that is required to add an event to a class’s interface. The class module never even has to trigger an event shown in the Object Browser.
Raising events
It should be obvious that an event that is never invoked by a class module’s code isn’t much use to anybody. Events are typically triggered (or raised) whenever circumstances indicate that the consumer should be notified.
Raising an event requires a single line of code:
RaiseEvent <EventName>(<Arguments>)
We’ll discuss event arguments in the “Passing data through events” section, later in this chapter. In the meantime, take a look at raising the InvalidSupplierID event from the SupplierName Property Get:
Public Property Get SupplierName() As String
Dim varTemp As Variant
If m_SupplierID <= 0 Then
RaiseEvent InvalidSupplierID()
Exit Property
End If
varTemp = DLookup(“CompanyName”, “Suppliers”, _
“SupplierID = “ & m_SupplierID)
If Not IsNull(varTemp) Then
SupplierName = CStr(varTemp)
Else
RaiseEvent InvalidSupplierID()
End If
End Property
The SupplierName property raises the InvalidSupplierID under two different situations: when the SupplierID is zero or a negative number, and when the DLookup function fails to locate a record in the Suppliers table.
There is no requirement that consumer code respond to events raised by class modules. In fact, events are very often ignored in application code. We doubt you’ve ever written code for every single event raised by an Access TextBox control, and custom events raised from class modules are no different.
But, again, that’s one of the nice things about object-oriented programming. You can add as many events as needed by your classes. Consumer code working with your classes can ignore irrelevant events and trap only those events that are important to the application.
Trapping custom events
About the only place where event-driven programming with Access classes becomes tricky is when it’s time to capture events (also called “sinking” events) in consumer code. There are a number of rules governing event consumption:
• The class hosting events must be declared within another class module.
• The object variable created from the class must be module-level and cannot be declared within a procedure.
• The object variable declaration must include the WithEvents keyword.
Let’s examine these requirements. It shouldn’t be surprising that events can only be captured by code within class modules. After all, class modules are special critters and have capabilities beyond simple code modules. You’ve never seen a stand-alone VBA code module directly respond to events raised by controls on an Access form, so there’s no reason to expect a plain code module to be able to consume events raised by the classes you add to an application.
However, a plain code module can very well create and use objects derived from class modules. It’s just that VBA code modules cannot capture events raised from class modules.
This requirement is not quite as onerous as it first appears. After all, every form and report module is a class module. That means that forms and reports are ready-built for consuming the events thrown by your class modules.
Similarly, the second requirement (the object variable must be module-level) also makes sense. There’s no way to capture an event from within a procedure. Procedures know nothing about objects, and there’s no provision for hooking a locally declared object variable to its events.
When you look at the class module behind a form, it becomes obvious why object variables must be module-level before their events can be sunk by consumer code. You’ve seen the typical Access form module, as shown in Figure 32-14. Notice what appears in the code module’s event list when an object variable has been declared with the WithEvents keyword.
Figure 32-14
The WithEvents keyword instructs VBA to watch for events raised from the object’s class module.
As you’d expect, selecting an event from the Product object’s event list opens a new event procedure, enabling you to write code in response to the event. The Product_InvalidSupplierID event procedure notifies the user whenever the Product class determines that the SupplierID value cannot be used by the class.
Obviously, the code in the event procedure runs whenever the corresponding event is raised from the object’s class module. The consumer does not have to explicitly check the value returned by the SupplierName property. Instead, the event procedure linked to the InvalidSupplierID handles the event and takes appropriate action.
Also, because the same event can be raised from multiple places within the class module, a single event procedure may handle many different situations related to a single problem within the class module.
We suspect that, behind the scenes, Access does exactly the same thing for built-in objects such as text boxes and command buttons. As soon as you add a control to an Access form, you’re able to add code to event procedures hooked into the control’s events.
Passing data through events
You probably noticed that the event declaration example given earlier in this chapter included a set of empty parentheses:
Public Event InvalidSupplierID()
What may not be obvious is that event arguments may be added within the parentheses:
Public Event ProductSold(Quantity As Integer)
The RaiseEvent statement includes a value for the event argument:
RaiseEvent ProductSold(UnitsSold)
Event declarations may include multiple arguments, and (to our knowledge) can pass any valid VBA data type, including complex data such as recordsets and other objects.
The ability to pass data through event arguments is an incredibly powerful tool for developers. A class module can directly communicate with its consumers, passing whatever data and information is necessary for the consumer to benefit from the class’s resources.
Exploiting Access class module events
It is possible to add custom events to Access forms and to raise those events from code within the form. Custom events are declared with exactly the same syntax as declaring events within any class module and are raised with the RaiseEvent statement. The only tricky part is sinking custom events raised by a form in another form’s module.
Custom events can be exploited as a way to convey messages and data between forms. Recently, we responded to a reader’s question about dialog boxes with a relatively lengthy explanation of modally opening the dialog box, hiding the dialog box when the user was ready to return to the main form, and then reading a custom property from the hidden dialog box. Although this technique works well, it requires quite a bit of planning and preparation.
The dialog box operation can be more simply implemented by adding a custom event to the dialog form that is raised by the dialog and sunk by the main form. Information entered by the user on the dialog form is passed to the main form as an event argument. The event is raised when the user closes the dialog form and the information passed as the event argument is captured by the main form. There is no need for the main form to close or otherwise manage the dialog form.
Let’s start with the dialog form that raises a custom event. The dialog form is shown in Figure 32-15.
Figure 32-15
This form uses a custom event to pass data back to the main form.
The user types something into the text box and clicks either OK or Cancel. The OK button passes the text box’s contents to the main form, while the Cancel button passes a “No Data message, indicating that the user dismissed the dialog box without entering any data.
Here’s all of the code behind this simple dialog box:
Public Event FormClosing(Message As String)
Private Sub cmdOK_Click()
DoCmd.Close acForm, Me.Name
End Sub
Private Sub cmdCancel_Click()
txtSomeData.Value = Null
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Close()
If Not IsNull(txtSomeData.Value) Then
RaiseEvent FormClosing(txtSomeData.Value)
Else
RaiseEvent FormClosing(“No data”)
End If
End Sub
A public event named FormClosing is declared at the top of the dialog form’s module. This event returns a single argument named Message. The cmdOK_Click event procedure closes the form, while the cmdCancel_Click event clears the contents of the text box named txtSomeData before closing the form.
The FormClosing event is raised by the dialog form’s Close event procedure, ensuring that the event is raised whenever the form is closed. If the txtSomeData is not Null, the value of the text box is passed by the FormClosing event, while a default message is passed if the text box’s value is Null.
No other code is needed by the dialog form, and the form is allowed to close normally because the FormClosing event fires just before the form disappears from the screen.
The main form is shown in Figure 32-16.
Figure 32-16
The main form sinks the custom event raised by the dialog form.
The code behind the main form is also quite simple. Notice the WithEvents keyword applied to the form object’s declaration:
Private WithEvents frm As Form_frmDialogForm
Private Sub cmdOpenDialogForm_Click()
Set frm = New Form_frmDialogForm
With frm.Visible = True
End Sub
Private Sub frm_FormClosing(Message As String)
txtDialogMessage.Value = Message
End Sub
The dialog form must be declared as a module-level variable behind the main form. The WithEvents keyword notifies the VBA engine that you want the main form to capture (or sink) events raised by the frm object.
Also notice that the form’s class name is Form_frmDialogForm. This is the name of the class module behind frmDialogForm, and is the entity that actually raises the event. From the perspective of the VBA project driving the application, the form’s surface is just a graphic interface and has nothing to do with the class module that supplies the logic driving the form.
The WithEvents keyword is almost magical. Once you’ve qualified an object declaration with WithEvents, the name of the object appears in the drop-down list at the top of the class module, and the object’s events appear in the right drop-down list (see Figure 32-17).
Figure 32-17
The WithEvents keyword enables the main form’s class module to capture events raised by the object.
All Access developers are familiar with how the object drop-down list shows all of the controls placed on the surface of an Access form, as well as an entry for the form itself. In this case, the object drop-down list shows the form object declared with the WithEvents keyword in addition to controls on the form’s surface.
In this case, the form object named frm is declared and instantiated and is completely controlled by the main form. The main form captures the dialog form’s events, and uses the data passed through the FormClosing event. The main form could just as easily reference other properties of the dialog form.
Notice that this technique eliminates the infamous bang-dot notation that Access developers have suffered with for so many years. Treating a form as an OOP object eliminates a lot of overhead from the code behind the main form.
Access forms are objects
It’s important to understand that every Access form is actually an object created from a class and is not a physical entity stored within the .accdb file. Most of us think of forms as a UI object that is maintained somewhere within the .accdb file and used as needed. In reality, each form is stored as a class, and Access instantiates a form object and displays the form on the screen whenever we work with the form’s class. In design view, Access presents us with an editable interface to the form’s class, and we work with the form’s properties.
Interestingly enough, the code behind an Access form is nothing more than a property of the form’s class. The code behind an Access form is, itself, a class. There is nothing in the object-oriented paradigm supported by Access that prohibits a class from containing another class.
Summary
This chapter has taken on the important topic of creating and using object classes. Access’s object-oriented features are a powerful way to encapsulate functionality, letting you design modular applications that are easy to create and maintain. Breaking complex features into discrete objects is a powerful way to incrementally build applications from a series of components, each of which performs a single job in the application.
Property procedures and class events are at the core of any OOP project. Object-oriented programming enforces modular programming, and the only access a consumer has to an entity’s data are through a class’s interface. Assigning a value to an object’s property can run hundreds of lines of code in the class module, greatly simplifying programming tasks on the consumer side.
Also, because encapsulation means that all of an object’s logic is contained within its class module, maintenance is much simpler than with traditional linear programming practices.
There’s a lot to think about and learn when you begin using object-oriented programming in database applications. Sometimes the rewards are a bit difficult to see at first, but once you begin using OOP in your applications, you’ll wonder how you got along without it!
In case you’re wondering, class modules, properties, methods, and events are very similar in .NET applications. The major difference is that the .NET framework adds many, many capabilities that are not possible in VBA classes. However, the OOP code you write in Access would be quite comfortable in a .NET application.