ACC: Number of Times a Custom Function Runs in a Query

ID: Q98788

The information in this article applies to:

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

A custom function called from a query will run as follows:

MORE INFORMATION

To optimize a query, Microsoft Access will not rerun a custom function unless the value passed to it changes. If the function accepts a field as a parameter, Microsoft Access must rerun the custom function for each record because the data may change from record to record. If criteria are placed on the result of the function, Microsoft Access must rerun the function when applying the criteria.

The examples below (examples A, B, and C) demonstrate each of the three scenarios, using a custom function designed to number the records in a query.

NOTE: These examples are simplified to demonstrate the results you can expect when you use custom functions in a query. Many variables can affect the number of times Microsoft Access reruns a custom function. For example, if you first minimize and them maximize Microsoft Access, the process of repainting the screen also includes re-executing any custom function that is part of the query result.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

Complete the following steps to use examples A, B, and C:

1. Create a new global module called RecordNumbers in the sample database 

   Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0)
2. Add the following lines to the module's Declarations section if they are 
   not already there:

      Option Explicit
      Global RecordNum
NOTE: In Microsoft Access version 2.0, the Employees table contains more Employees. If you are performing the following examples in version 2.0, expect more records to be returned.

Example A: An Expression that Calculates Only Once Per Query

1. Add the following function to the global module you created in the 

   previous procedure:

      Function ShouldIncrement ()
         RecordNum = RecordNum + 1
         ShouldIncrement = RecordNum
      End Function
2. Create a new query based on the Employees table. Drag the Last Name 
   field to the query grid and add a column with the following expression:

      RecordNumber: ShouldIncrement()
3. Run the query. Note that you receive the following result: 

      Last Name    RecordNumber
      -------------------------
      Davolio      1
      Fuller       1
      Leverling    1
      Peacock      1
      Buchanan     1
      Suyama       1
      King         1
      Callahan     1
      Dodsworth    1
Note that the function IncrementRecNum() is run only once.

Example B: An Expression that Calculates Once Per Record

1. Add the function DoesIncrement() to the module RecordNumbers that you 

   created in the first procedure:

      Function DoesIncrement (AnyValue)
         RecordNum = RecordNum + 1
         DoesIncrement = RecordNum
      End Function
2. Open the module RecordNumbers in Design view. On the View menu, 
   click Debug Window (or Immediate window in versions 1.x and 2.0).
   Type the following statement to initialize the variable RecordNum:

      RecordNum = 0
3. Create a new query based on the Employees table. Drag the Last 
   Name field to the query grid and add a column with the following
   expression:

      RecordNumber: DoesIncrement([EmployeeID])

      NOTE: In versions 1.x and 2.0, there is a space in Employee ID.
4. Run the query. Note that you receive the following result: 

      Last Name    RecordNumber
      -------------------------
      Davolio      1
      Fuller       2
      Leverling    3
      Peacock      4
      Buchanan     5
      Suyama       6
      King         7
      Callahan     8
      Dodsworth    9

Example C: An Expression that Calculates More Than Once Per Record

1. Create a new query based on the Employees table. Drag the Last Name 

   field to the query grid and add a column with the following expression:

      RecordNumber: DoesIncrement([EmployeeID])

   NOTE: You will need to complete step 1 of example B in order to
   have the DoesIncrement() function available.
2. Open the module RecordNumbers in Design view. On the View menu, 
   click Debug Window (or Immediate window in versions 1.x and 2.0).
   Type the following statement to initialize the variable RecordNum:

      RecordNum = 0
3. Run the query. Note that you receive the following result: 

      Last Name    RecordNumber
      -------------------------
      Davolio      1
      Fuller       2
      Leverling    3
      Peacock      4
      Buchanan     5
      Suyama       6
      King         7
      Callahan     8
      Dodsworth    9
4. Repeat Step 2 in this example (example C).

5. Add the following criteria under RecordNumber: 

      >=0
6. Run the query. Note that you now receive the following result: 

      Last Name    RecordNumber
      -------------------------
      Davolio      10
      Fuller       11
      Leverling    12
      Peacock      13
      Buchanan     14
      Suyama       15
      King         16
      Callahan     17
      Dodsworth    18
In this case, Microsoft Access runs the expression twice, once to create the recordset and again to check the criteria you specified.

Additional query words: top 20 limit counter 

Keywords          : kbusage
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo

Last Reviewed: November 20, 1998