Microsoft KB Archive/170532

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Article ID: 170532

Article Last Modified on 1/20/2007



APPLIES TO

  • Microsoft Access 97 Standard Edition



This article was previously published under Q170532

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. Microsoft 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

There are two possible workarounds.

Method 1

Store the procedure in a standard module if you plan to call it from a query, form, report, or macro. If the procedure is in a standard module, you do not 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 can cause more overhead to be created than expected. For instance, calling a wrapper procedure from a query causes additional overhead to be created for each record that the query contains. To make the query more efficient and use less resources, move the code in the class module to a standard module so that the additional overhead can be eliminated.

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

  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.
  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 Debug window, and then press ENTER.

    ?CallMultiplyByTen(5)

    Note that the procedure returns the number 50 to the Debug 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

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
          -------------------------------------
          ControlSource: Orders 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 that Text1 contains "#Error."

Call the class module method from a macro:

  1. Create a new macro:

          Macro Name            Action
          ----------------------------
          ClassTestMacro        MsgBox
    
          ClassTestMacro Action
          -----------------------------
          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, search the Help Index for "class modules, overview."

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

160007 ACC97: Introduction to Stand-Alone Class Module Programming

Keywords: kbcode kbprb kbprogramming KB170532