Microsoft Access 2010 Bible

Chapter 28: Object-Oriented Programming with VBA

979

Tip
I 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 acces-

sible 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 subrou-

tine 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 prod-

uct 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