MS-Access / Getting Started

---

Using Property Procedures

The concept of property procedures is fundamental to object-oriented programming. As the name implies, a property procedure is a VBA procedure that defines a property for a class. Most classes contain several to many property procedures.

Note Property procedures are always public by default. Even if you omit the Public keyword, your property procedure is exposed to the other elements of your applications. You should, however, always use the Public keyword to clarify the property procedure's scope. It never hurts to be very explicit in your code.

Looking at the types of property procedures

There are three types of property procedures:

• Property Get: Retrieves the value of a property. A Property Get works very much like any function and follows the same pattern as any VBA function.
• Property Let: Assigns a new value to the property. Property Let works only for simple data types such as numeric, strings, and date properties.
• Property Set: Assigns a value to an object property. You would use a Property Set for a property defined as a recordset or other object data type.

The concepts behind property procedures are illustrated in Figure below. Each time your code references a property, the class module responds by running the appropriate property procedure.

Each time your code references a property, the class module responds by running the appropriate property procedure.

Read/write, read-only, and write-only
The properties you add to your classes can be read/write, read-only, or write-only, depending on how you expect the property to be used.

• Read/write: Including both a Property Get and a Property Let (or Property Set) makes a property read/write. The Property Get lets a consumer read the property's value, while the Property Let (or Property Set) lets a value (or object) be assigned to a property.
• Read-only: Omitting the Property Let (or Property Set for object properties) makes a property read-only. A consumer can read the property's value through the Property Get procedure but can't assign a new value to the property.
  Obviously, because there is no way to assign a value to a read-only property, the class must provide the read-only property's value. This is often done by extracting a value from a database, or from the System Registry, or by reading a value from an .ini file or the operating system. Because a Property Get is a procedure, you can add any logic your class requires to obtain the property's value.
• Write-only: Omitting a Property Get makes a property write-only. You may decide to use a write-only property for sensitive information such as passwords and login identities. Making a write-only property is an excellent way to preserve the security of sensitive data. Write-only properties are also used to provide a class with information that it needs to support its activities, such as a connection string or database name.

Property Let

The Property Let procedure assigns a value to a property. The property's value is passed into the procedure as an argument, and the value is then assigned to the class module's private variable that stores the property's value.

The following example is a prototype for any Property Let procedure:

Public Property Let <PropertyName>(Value As <DataType>)
  <PrivateVariable> = Value
End Property

Tip: The property's argument can be named anything you want. Always use Value as the argument name. Consistently using Value is simpler than assigning a meaningful name to the argument and is consistent with how property values are assigned to built-in Access properties.

The following example is from the Employee class module:

Public Property Let LastName(Value As String)
  m_LastName = Left$(Value, 20)
End Property

This small example hints at the power of property procedures. Notice that the Value argument is a string. The statement within the property procedure assigns only the 20 leftmost characters of the Value argument to the m_LastName variable. This is because the LastName field in the Northwind Employees table only accepts 20 characters. Many database systems generate errors if more characters are sent to a field than the field can hold.

Tip Adding a little bit of logic to a property procedure can go a long way toward bulletproofing an application.

Property Set

The syntax of Property Set is parallel to the Property Let procedure. The only difference is that the argument is an object data type, and the VBA Set keyword is used for the assignment within the body of the Property Set. The following is an example of hypothetical Property Set procedure that accepts a recordset object and assigns it to a private variable named m_ Products:

Public Property Set Products(Value As ADO.Recordset)
  If Not Value Is Nothing Then
    Set m_Products = Value
  End If
End Property

In this small example, the argument is validated before it is assigned to the private variable.

Property Get

This is the basic syntax of the Property Get:

Public Property Get <PropertyName>() As <DataType>
  <PropertyName> = <PrivateVariable>
End Property

Notice the similarities between a Property Get and a VBA function. The Property Get is declared as a particular data type, and the property is assigned a value within the body of the property. The syntax is identical to any VBA function.

This is the Property Get from the Employee class module in the example application accompanying this tutorial:

Public Property Get LastName() As String
  LastName = m_LastName
End Property

The Property Get executes whenever the property's value is assigned to a variable or otherwise used by the application. For example, the following VBA statement executes a Property Get named LastName in the Employee class module (objEmployee has been declared and instantiated from the Employee class):

strLastName = objEmployee.LastName

Notice that this statement doesn't directly reference the Property Get. Because the obj Employee object was created from the Employee class, the VBA engine knows to run the Property Get because a variable is assigned the value of the LastName property. In other words, the VBA engine gets the LastName property value from the class.

In this example, the Property Get is very simple and only returns the value of the private variable. However, you could have a much more complex Property Get that performs data transformation on the value or retrieves the value from a database file, an .ini file, the operating system, or some other source.

This example also illustrates the simplified programming possible with object-oriented techniques. A single VBA statement in the application's consumer code is enough to run whatever complex operation is necessary to retrieve the value of the property. The consumer is never aware of the logic supporting the property.