MS-Access / Getting Started

---

Adding a class module to a database

You can open a new class module in one of two ways:

• In the editor window, choose Insert → Class Module.
• In the Other group of the Access Create ribbon tab, select the Macro drop-down list, and choose Class Module.

Tip: It's a good idea to click on the Save button on the Code Editor toolbar and assign a name to the class module early in its development cycle. The class name should be descriptive but not excessively long. Finally, the name should be meaningful to you - users never see the name of the class, so use a name that means something to you or another developer.

The name you provide for the class module becomes the name of the object's class when creating objects from the class module. The name of the class module is similar to the names you've given other objects in your databases.

The class module is in the code editor window. Notice that the class module looks just like any other module in the editor window. Your only indication that it isn't a normal module is the tiny icon in the left corner of the module as it appears in the code editor. It's a little box icon, instead of the tinkertoy icon you see in standard modules.

You'll use the name you provide for the class module as the object's class name.

Creating simple product properties

The easiest way to establish the properties of a class, and the technique you'll use in your first class example, is to simply declare each of the properties as a public variable in the clsProduct1 class module. Adding a public variable to a class module creates a new property for the class. The variable's public scope makes it accessible to other routines in the database. In the "Using Property Procedures" section, later in this tutorial, you'll see an alternate way to create properties for your class modules.

    Public ProductID As Long
    Public Name As String
    Public Supplier As String
    Public UnitPrice As Currency
    Public UnitsInStock As Integer
    Public ReorderLevel As Integer
    Public Discontinued As Boolean

Access treats each public variable in a class module as a property of the objects created from the class. Because you declare the public variables in a class module, Access uses the variables as properties of the class's objects without further work on your part. The IntelliSense displays the properties in the Auto List Members drop-down list in a module using an object created from the class.

Because the object's properties are variables in the class module, the names you assign to these items must conform to VBA's variable naming requirements:

• Property names must be 64 or fewer characters.
• Property names can contain only alphanumeric characters and the underscore character.
• Property names must begin with an alphabetic character and can never begin with the underscore character or a number.

Tip: The names you provide for an object's properties and methods should be descriptive and easy to recognize.

Creating methods

The clsProduct1 class includes two methods: Sell and Discount. These methods, like all object methods, define actions supported by the objects created from the class. Each method is nothing more than a public procedure in the object's class module.

• Sell: The following code example shows the procedure implementing the Sell method. Because all procedures in a class module are public by default, the Public keyword is optional and you add it to the Sell method to clarify the status of the procedure.

Public Sub Sell(UnitsSold As Integer)
  Me.UnitsInStock = Me.UnitsInStock - UnitsSold
End Sub

Notice there's nothing special about the Sell method. There's no special declaration for this procedure, nor is there reference to its status as a method of the class. Methods are an example of how Access treats class modules differently from simple code modules. As long as you haven't declared the procedure (sub or function) with the Private keyword (remember that the Public keyword is the default), Access treats the procedure as a method of the objects created from the class module.

Because it's a subroutine, the Sell method doesn't return a value. If you declare it as a function, it could return any valid Access data type. The Sell procedure requires an argument specifying how many items were sold.

Note: Notice the use of the Me keyword in the previous code example. In this context, Me refers to the object instance created from the class module.

You may have noticed an obvious bug in the Sell method. If the UnitsSold is larger than the UnitsInStock, the UnitsInStock value will be a negative number after the method runs. To fix this bug, you must add a couple of lines of code to the method:

Public Sub Sell(UnitsSold As Integer)
  If UnitsSold > Me.UnitsInStock Then
    Exit Sub
  End If
  Me.UnitsInStock = Me.UnitsInStock - UnitsSold
End Sub

This change causes the Sell method to simply exit and not deduct any units when the UnitsSold value would result in a negative value for the UnitsInStock.

• Discount: The Discount method is similar to Sell:

Public Sub Discount(Percent As Integer)
  If Percent < 1 _
  Or Percent > 99 Then
    Exit Sub
  End If
  Me.UnitPrice = _
    Me.UnitPrice - ((Percent / 100) * Me.UnitPrice)
End Sub

In this case, the method ends immediately if the Percent is less than 1 or larger than 99. Otherwise, the object's UnitPrice property is discounted by an expression derived from the Percent and current UnitPrice.