MS-Access / Getting Started

Understanding Recursion in Data Macros

When you're designing data macros, you have the potential to run into a recursion issue. Access runs into a recursion issue when it tries to execute the same data macro logic continually over and over in a repeated loop. For example, suppose that you created data macro logic attached to the After Update event of a table that changed data in the current record of the same table. Access makes the field changes and then commits the data. Access then fires the After Update again because data in the table changed. The After Update event fires again, changes the data, and the cycle begins again. Access is now in a perpetual loop executing the data macro in the After Update event. Access could also get into a loop, for example, when working with two tables that have After Update events that update each other, or even with complex named data macros that end up repeating themselves.

Data macros are limited to 10 levels of recursion, which means Access stops the data macro execution after 10 iterations through a recursion loop. Access does not display any messages in the user interface to indicate that it fell into a recursive loop, so you might not even be aware that anything is wrong. Access does, however, display the New Application Errors message in the status bar and logs an error into the USysApplicationLog table with the following error description:

A data macro resource limit was hit. This may be caused by a data macro recursively calling itself. The Updated(<Field>) function may be used to detect which field in a record has been updated to help prevent recursive calls.

In most cases, you can correct recursive calls by using the Updated function to determine which field or fields Access changed in the last record update. You can add conditional logic with If blocks to determine if a field was changed and perform different actions, or no actions, based on the evaluation of the condition. As you are designing and testing your data macro logic, it's a good idea to check the USysApplicationLog continually for any errors relating to recursion.

[Previous] [Contents] [Next]

In this tutorial:

  1. Creating Table Data Macros
  2. Uses of Data Macros
  3. The Data Macro Design Facility
  4. Access New Logic Designer
  5. Working with Before Events
  6. Grouping Macros
  7. Using If Blocks to Create Conditional Expressions
  8. Raising Errors in Data Macros to Cancel Events
  9. Testing Your Data Macro
  10. Defining Multiple Actions
  11. Collapsing and Expanding Actions
  12. Moving Actions
  13. Preventing Duplicate Records Across Multiple Fields
  14. Before Delete
  15. Working with After Events
  16. After Update
  17. After Delete
  18. Working with Named Data Macros
  19. Saving Named Data Macros
  20. Calling Named Data Macros
  21. Renaming and Deleting Named Data Macros
  22. Analyzing Errors in the USysApplicationLog Table
  23. Using Parameters
  24. Using Local Variables
  25. Working with Return Variables
  26. Debugging Data Macros
  27. Understanding Recursion in Data Macros
  28. Sharing Data Macro Logic