Custom Properties and Methods | Programming Microsoft Access 2000 (Microsoft Programming Series)

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

Sub MyOrder() Dim OrderIt As New MyTestClass     OrderIt.PO1 1, 10      End Sub

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

'A method for updating a table Public Sub PO1(ProductID, units) Dim cmd1 As Command Dim strSQL As String 'Assign the command reference and connection.     Set cmd1 = New ADODB.Command     cmd1.ActiveConnection = CurrentProject.Connection      'Define the SQL string; notice 'the insertion of passed arguments.     strSQL = "UPDATE Products " & _         "SET UnitsInStock = " & _         "UnitsInStock-" & units & " " & _         "WHERE ProductID=" & ProductID          'Assign the SQL string to the command and run it.     cmd1.CommandText = strSQL     cmd1.CommandType = adCmdText     cmd1.Execute      End Sub

Updating data with a parameter query

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

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

Public Sub PO2(ProductID As Long, units As Integer) Dim cmd1 As Command Dim strSQL As String Dim prm1 As ADODB.Parameter, prm2 As ADODB.Parameter 'Assign the command reference and connection.     Set cmd1 = New ADODB.Command     cmd1.ActiveConnection = CurrentProject.Connection 'Write out SQL statement with parameters & assign to cmd1.     strSQL = "Parameters PID Long,Quantity Integer;" & _         "UPDATE Products " & _         "SET UnitsInStock = " & _         "UnitsInStock-Quantity " & _         "WHERE ProductID=PID;"     cmd1.CommandText = strSQL     cmd1.CommandType = adCmdText      'Declare parameters; must have same order as declaration.     Set prm1 = cmd1.CreateParameter("PID", adSmallInt, _         adParamInput)     prm1.Value = ProductID     cmd1.Parameters.Append prm1     Set prm2 = cmd1.CreateParameter("Quantity", adInteger, _         adParamInput)     prm2.Value = units     cmd1.Parameters.Append prm2      'Run update query.     cmd1.Execute      End Sub

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

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.