MS-Access / Getting Started

---

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.