MS-Access / Getting Started

---

Using the product object

After you've assembled the class module from properties and methods, you can create new objects from the class.

Creating a new product object requires you to use the New keyword. This statement is one way to create a new instance of a product object from the clsProduct1 class module:

Private Product As New clsProduct1

Alternatively, you can first declare the Product object, and then instantiate as separate statements. For example, place this statement in the module's Declarations section to establish the clsProduct1 object:

Private Product As clsProduct1

The object instantiates in the form's Load event procedure:

Set Product = New clsProduct1

Tip Prefer using separate statements for declaration and instantiation, because it isn't possible to trap errors when declaration and instantiation are processed as a single statement. If you use a single statement for declaration and instantiation, your application may exhibit instability in some situations.

The code creates the new instance of the Product object when the New keyword executes. The code behind frmProductUnbound uses the two-statement approach to creating the Product object: In frmProductUnbound, you declare the product in the form's Declarations section as a module-level variable, and then the object instantiates during the form's Load event. Therefore, the Product object is available as soon as the form opens on the screen, and it's accessible to all the code behind the form.

The code in the form's Load event procedure also fills a recordset object with records from tbl- Products. You then use this recordset to set the Product object's properties. A private subroutine named SetObjectProperties retrieves values from the recordset and sets the object's properties to those values:

Private Sub SetObjectProperties()
  'Set the product object's properties:
  With Product
    .ProductID = rs.Fields("ProductID").Value
    .Name = rsFields("ProductName").Value
    .Supplier = rsFields("Supplier").Value
    .UnitPrice = rsFields("UnitPrice").Value
    .UnitsInStock = rsFields("UnitsInStock").Value
    .ReorderLevel = rsFields("ReorderLevel").Value
    .Discontinued = rsFields("Discontinued").Value
  End With
End Sub

After you create the product, you can reference its properties and methods. References to the product object's properties are similar to property references anywhere else in VBA. This statement retrieves the current value of the product's UnitPrice property and assigns it to the text box named txtUnitPrice on frmProductUnbound:

txtUnitPrice.Value = Product.UnitPrice

You can find a number of similar statements in the form's FillForm procedure:

Private Sub FillForm()
  'Fill the form with the product's properties:
  txtID.Value = Product.ProductID
  txtName.Value = Product.Name
  txtSupplier.Value = Product.Supplier
  txtUnitPrice.Value = Product.UnitPrice
  txtUnitsInStock.Value = Product.UnitsInStock
  txtReorderLevel.Value = Product.ReorderLevel
  txtDiscontinued.Value = Product.Discontinued
End Sub

frmProductUnbound makes several property assignments from the form's Load event procedure. The following code listing shows the entire Form_Load sub from frmProductUnbound. Notice how the code builds the recordset, makes the property assignments, and fills the text boxes on the form through the SetObjectProperties and FillForm procedures.

Private Sub Form_Load()
  Set Product = New clsProduct1
  Set rs = CurrentDb.OpenRecordset("tblProducts")
  If rs.RecordCount > 0 Then
    Call SetObjectProperties
    Call FillForm
  End If
End Sub

Similarly, selling a product involves using the object's Sell method. The code below shows how a form might use the Sell method. Notice that the code passes a parameter: txtNumberToSell. The user has entered the number of items to sell into a text box named txtNumberToSell. That value becomes the UnitsSold argument for the Sell method I mentioned in the "Looking at a simple class module" section, earlier in this tutorial.

Private Sub cmdSell_Click()
  Product.Sell txtNumberToSell
  Call FillForm
End Sub 'cmdSell_Click

The FillForm procedure is called to refresh the form's contents after the Sell method executes.