MS-Access / Getting Started

---

Understanding Class Modules

Whenever you create event procedures behind a form or report, you're creating a class module. A class module is the specification for a user-defined object in your database, and the code you write in the module defines the methods and properties of the object. Of course, forms and reports already have dozens of methods and properties already defined by Access, but you can create extended properties and methods when you write code in the class module attached to a form or report.

You can also create a class module as an independent object by clicking the Class Module button in the Macros & Code group on the Create tab or by clicking Class Module on the Insert menu in the VBE. In the Conrad Systems Contacts sample database (Contacts.accdb), you can find a class module called ComDlg that provides a simple way to call the Open File dialog box in Windows from your Visual Basic code.

As previously discussed, you define a method in a class module by declaring a procedure (either a function or a sub) public. When you create an active instance of the object defined by the class module, either by opening it or by setting it to an object variable, you can execute the public functions or subs you have defined by referencing the function or sub name as a method of the object. For example, when the frmContacts form is open, you can execute the cmdCancel_Click sub by referencing it as a method of the form's class. (The cmdCancel_Click sub is public in all forms in the sample database so that the Exit button on the main switchboard can use it to command the form to clear edits and close itself.) The name of any form's class is in the form Form_formname, so you execute this method in your code like this:

Form_frmContacts.cmdCancel_Click

When you create a class module that you see in the Modules list in the Navigation pane, you can create a special sub that Visual Basic runs whenever code in your application creates a new instance of the object defined by your class. For example, you can create a private Class_Initialize sub to run code that sets up your object whenever other code in your application creates a new instance of your class object. You might use this event to open recordsets or initialize variables required by the object. You can also create a private Class_ Terminate sub to run code that cleans up any variables or objects (perhaps closing open recordsets) when your object goes out of scope or the code that created an instance of your object sets it to Nothing. (Your object goes out of scope if a procedure activates your class by setting it to a nonstatic local object variable and then the procedure exits.)

Although you can define properties of a class by declaring public variables in the Declarations section of the class module, you can also define specific procedures to handle fetching and setting properties. When you do this, you can write special processing code that runs whenever a caller fetches or sets one of the properties defined by these procedures. To create special property processing procedures in a class module, you need to write Property Get, Property Let, and Property Set procedures as described in the following sections.

Property Get

Use a Property Get procedure to return a property value for the object defined by your class module. When other code in your application attempts to fetch the value of this property of your object, Visual Basic executes your Property Get procedure to return the value. Your code can return a data value or an object.

Syntax

[Public | Private | Friend] [Static] Property Get propertyname
  ([<arguments>]) [As datatype]
    [<property statements>]
    [propertyname = <expression>]
    [Exit Property]
    [<property statements>]
    [propertyname = <expression>]
End Property

where <arguments> is

{[Optional][ByVal | ByRef][ParamArray] argumentname[()] [As datatype][= default]},...

Notes

Use the Public keyword to make this property available to all other procedures in all modules. Use the Private keyword to make this property available only to other procedures in the same module. When you declare a property as private in a class module, you cannot reference that property from another module. Use the Friend keyword to declare a property that is public to all other code in your application but is not visible to outside code that activates your project via automation.

Include the Static keyword to preserve the value of all variables declared within the property procedure, whether explicitly or implicitly, so long as the module containing the procedure is open. This is the same as using the Static statement (discussed earlier in this tutorial) to explicitly declare all variables created in this property procedure.

You can use a type declaration character at the end of the propertyname entry or use the As datatype clause to declare the data type returned by this property. Valid datatype entries are Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, or one of the object types described earlier in this tutorial. If you do not declare a data type, Visual Basic assumes that the property returns a variant result. The data type of the returned value must match the data type of the propvalue variable you declare in any companion Property Let or Property Set procedure. You can set the return value in code by assigning an expression of a compatible data type to the property name.

You should declare the data type of all arguments in the property procedure's parameter list. Note that the names of the variables passed by the calling procedure can be different from the names of the variables known by this procedure. If you use the ByVal keyword to declare an argument, Visual Basic passes a copy of the argument to your procedure. Any change you make to a ByVal argument does not change the original variable in the calling procedure. If you use the ByRef keyword, Visual Basic passes the actual memory address of the variable, allowing the procedure to change the variable's value in the calling procedure. (If the argument passed by the calling procedure is an expression, Visual Basic treats it as if you had declared it by using ByVal.) Visual Basic always passes arrays by reference (using ByRef).

Use the Optional keyword to declare an argument that isn't required. All optional arguments must be the Variant data type. If you declare an optional argument, all arguments that follow in the argument list must also be declared as optional. You can specify a default value only for optional parameters. Use the IsMissing built-in function to test for the absence of optional parameters. You can also use the ParamArray argument to declare an array of optional elements of the Variant data type. When you attempt to access this property in an object set to the class, you can then pass it an arbitrary number of arguments. The ParamArray argument must be the last argument in the argument list.

Use the Exit Property statement anywhere in your property procedure to clear any error conditions and exit your procedure normally, returning to the calling procedure. If Visual Basic runs your code until it encounters the End Property statement, control is passed to the calling procedure but any errors are not cleared. If this procedure causes an error and terminates with the End Property statement, Visual Basic passes the error to the calling procedure.

Examples

To declare a Filename property as a string and return it from a variable defined in the Declarations section of your class module, enter the following:

Option Explicit
Dim strFileName As String
Property Get Filename() As String
    ' Return the saved file name as a property
    Filename = strFilename
End Property

You can see an example of Property Get in the ComDlg class where we added similar code.

To establish a new instance of the object defined by the ComDlg class module and then fetch its Filename property, enter the following in a function or sub:

Dim clsDialog As New ComDlg, strFile As String
  With clsDialog
    ' Set the title of the dialog box
    .DialogTitle = "Locate Conrad Systems Contacts Data File"
    ' Set the default file name
    .FileName = "ContactsData.accdb"
    ' .. and start directory
    .Directory = CurrentProject.Path
    ' .. and file extension
    .Extension = "accdb"
    ' .. but show all accdb files just in case
    .Filter = "Conrad Systems File (*.accdb)|*.accdb"
    ' Default directory is where this file is located
    .Directory = CurrentProject.Path
    ' Tell the common dialog that the file and path must exist
    .ExistFlags = FileMustExist + PathMustExist
    ' If the ShowOpen method returns True
    If .ShowOpen Then
      ' Then fetch the Filename property
      strFile = .FileName
    Else
      Err.Raise 3999
    End If
  End With