Chapter 28: Object-Oriented Programming with VBA
993
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 prop-
erty, 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