ACC2000: Cannot Call Class Module Methods from MS Access Objects (208202)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q208202
Advanced: Requires expert coding, interoperability, and multiuser skills.

SYMPTOMS

When you call a class module method directly from a query, form, report, or macro, you receive an error message.

CAUSE

In order to call a class module procedure, the calling object must initialize an instance of the class. Access objects, such as queries, forms, reports, and macros, cannot automatically initialize new instances of a user-defined class. Only a Visual Basic for Applications procedure can initialize a new instance of a user-defined class.

RESOLUTION

The following are two possible workarounds:

Method 1

If you plan to call the procedure from a query, form, report, or macro, then store it in a standard module. Having it in a standard module avoids the need to create a new instance of a user-defined class every time you call it. This is the recommended method.

Method 2

Create a procedure in a standard module that initializes an instance of the class. The procedure in the standard module then calls the procedure stored in the class module and passes it any necessary arguments. This is typically known as a "wrapper" procedure.

Using a wrapper procedure in this manner is not recommended because additional overhead is created when the object is initialized. In some instances, this creates more overhead than expected. For instance, calling a wrapper procedure from a query creates additional overhead for each record contained in the query. To increase query efficiency and to lessen resource usage, move the code in the class module to a standard module; this will eliminate additional overhead.

The following example demonstrates creating a class module method named MultiplyByTen and a wrapper procedure named CallMultiplyByTen, that makes the class method available to other Microsoft Access objects. It next demonstrates calling the wrapper procedure from a query.

  1. Open the sampe database Northwind.mdb.
  2. On the Insert menu, click Class module.
  3. Type the following line in the Declarations section if it is not already there: 
     Option Explicit
  4. Type the following procedure: 
     Function MultiplyByTen(clsVar As Variant) As Variant
    MultiplyByTen = clsVar * 10
 End Function
  5. Close and save the class module as MultiplyClass.
  6. Create a standard module and type the following line in the Declarations section if it is not already there: 
     Option Explicit
  7. Type the following procedure: 
     Function CallMultiplyByTen(stdVar As Variant) As Variant
    Dim clsMultiply As New MultiplyClass
    CallMultiplyByTen = clsMultiply.MultiplyByTen(stdVar)
 End Function
  8. To test this function, type the following line in the Immediate window, and then press ENTER. 
     ?CallMultiplyByTen(5)
    Note that the procedure returns the number 50 to the Immediate window.

  9. Close and save the module as Module1.
  10. Create a new query based on the Orders table with the following fields: 
       Query: Query1
   -----------------------------------------
   Type: Select Query

   Field: OrderID
   Table: Orders

   Field: Freight
   Table: Orders

   Field: EXPR1: CallMultiplyByTen([Freight])
  11. Run the query. Note that the class module method returns a value for each record.

MORE INFORMATION

Steps to Reproduce Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Create a class module method

  1. Open the sample database Northwind.mdb.
  2. On the Insert menu, click Class module.
  3. Type the following line in the Declarations section if it is not already there: 
    Option Explicit
  4. Type the following procedure: 
     Function MultiplyByTen(clsVar As Variant) As Variant
   MultiplyByTen = clsVar * 10
 End Function
  5. Close and save the class module as MultiplyClass.

Call the Class Module Method from a Query

  1. Create a new query based on the Orders table: 
       Query: ClassTestQuery
   --------------------------------------
   Type: Select Query
   Field: Freight
   Table: Orders
 
   Field: Expr1: MultiplyByTen([Freight])
  2. Run the query. Note that you receive the following error message:
    Undefined function 'MultiplyByTen' in expression.

Call the Class Module Method from a Form

  1. Create a new form based on the Orders table: 
       Form: ClassTestForm
   -------------------------
   Caption: TestForm
   ControlSource: Test Table

   Text box:
   ---------------------------
   Name: Freight
   Caption: Freight
   ControlSource: Freight

   Text box:
   ------------------------------
   Name: Text1
   Caption: Text1
   ControlSource: =MultiplyByTen([Freight])
  2. Switch the form to Form view. Note that the error "#Name?" appears in Text1.

Call the Class Module Method from a Report

  1. Create a new report based on the Orders table: 
       Report: ClassTestReport
   ---------------------------------------
   ControlSource: Orders Table

   Text box:
   ---------------------------------------
   Name: Freight
   Caption: Freight
   ControlSource: Freight

   Text box:
   ---------------------------------------
   Name: Text1
   Caption: Text1
   ControlSource: =MultiplyByTen([Freight])
  2. Preview the report. An Enter Parameter Value dialog box appears and prompts you to enter the value of MultiplyByTen. Click OK.

    NOTE: You see Text1 contains "#Error."

Call the Class Module Method from a Macro

  1. Create a new macro: 
       Macro Name            Action
   --------------------------------
   ClassTestMacro        MsgBox

   ClassTestMacro Action Arguments
   --------------------------------
   MsgBox
   Message: =MultiplyByTen(5)
   Beep: Yes
   Type: None
  2. Save the macro and run it. Note that you receive the following error message:
    The expression you entered has a function name that Microsoft Access can't find.
    You may also receive the following error message when you call a class module method directly from a macro:
    Microsoft Access can't find the name <class name> you entered in the expression.

REFERENCES

For more information about class modules, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type class modules in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For additional information about programming with class modules, please see the following article in the Microsoft Knowledge Base:

160007 Introduction to Stand-Alone Class Module Programming

Modification Type:MinorLast Reviewed:7/16/2004
Keywords:kbcode kbprb kbProgramming KB208202
©2004 Microsoft Corporation. All rights reserved.