ACC2000: Number of Times a Custom Function Runs in a Query (210554)

MORE INFORMATION

To optimize a query, Microsoft Access does not rerun a custom function unless the value passed to it changes. If the function accepts a field as a parameter, 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, 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 that you can expect when you use custom functions in a query. Many variables can affect the number of times Access reruns a custom function. For example, if you first minimize and them maximize Access, the process of repainting the screen also includes re-executing any custom function that is part of the query result.

To use examples A, B, and C, follow these steps:

1. Start Microsoft Access and open the sample database Northwind.mdb
2. Create a new global module called RecordNumbers.
3. Add the following lines to the module's Declarations section if they are not already there:

   Option Explicit
   Global RecordNum
   					

Example A: An Expression That Calculates Only Once per Query

1. Add the following function to the global module that 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 then 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 following 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 Immediate Window. Type the following statement to initialize the variable RecordNum, and then press ENTER:

   RecordNum = 0
   					

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

   RecordNumber: DoesIncrement([EmployeeID])

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 then add a column with the following expression:

   RecordNumber: DoesIncrement([EmployeeID])

   NOTE: You need to complete step 1 of example B to have the DoesIncrement() function available.
   
   
2. Open the module RecordNumbers in Design view. On the View menu, click Immediate Window. Type the following statement to initialize the variable RecordNum, and then press ENTER:

   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, Access runs the expression twice, once to create the recordset and again to check the criteria that you specified.