MS-Access / Getting Started

---

Modifying the Product Class

Earlier in this tutorial, built a simple product class representing a Northwind product. The initial class is included in the Access .accdb file accompanying this tutorial as the clsProduct1 class module. In this section, 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. Also expand the basic application by adding a few other classes needed to support the Northwind Traders application.

The example application accompanying this tutorial includes a form named Products_OOP, which is based on the Products form included with Northwind Traders. This form utilizes the majority of OOP techniques described in this tutorial and can serve as a model for your OOP endeavors.

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 is that the form (which is the consumer of the product data) has to know a great deal about how the product data is stored. The form holds a hard-coded SQL statement, creates a recordset with product data, and 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 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 is relatively unimportant, larger, more ambitious applications generally require significant control over the data.

Looking at 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 work like this: If a value greater than zero is assigned to the ProductID property, the class retrieves all 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")
  'Seek the Product record matching
  'the m_ProductID value.
  rs.FindFirst "ProductID =" & 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 a 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 is 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 is 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.