Adding a new property to provide extra information
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 example, 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. 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.
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's 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 - read-only, write-only, or read/write - that provide functionality not otherwise available. Again, the consumer of the class doesn't have to know anything about the underlying data structures, and all the data management is handled through the class module.
Adding a new method to the product class
In the "Recognizing the Benefits of Object-Oriented Programming" section, earlier in this tutorial, I discussed some of the advantages of encapsulation. 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.
Assume that the hypothetical SellProduct method (introduced in the "Following the rules" sidebar, earlier in this tutorial) has 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 db.Execute strSQL 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.
In this tutorial:
- Object-Oriented Programming with VBA
- Introducing Object-Oriented Programming
- Defining objects with class modules
- Adding a class module to a database
- Using the product object
- Creating bulletproof property procedures
- Recognizing the Benefits of Object-Oriented Programming
- Managing a class's interface
- Using Property Procedures
- Exploring property-value persistence
- Modifying the Product Class
- Adding a new property to provide extra information
- Class Events
- The Class_Terminate event procedure
- Adding Events to Class Modules
- Creating custom events
- Trapping custom events
- Exploiting Access class module events