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]