Take the 2-minute tour ×

Is there a way to track all calls to subs and functions (kind of like a historical "Call Stack") that occur during the opening of a form? I'm not interested in functions that get called in queries populating controls, only ones at form or control level.

My form is taking much longer to load than I believe it should. I think some of the event procedures are running multiple times in response to controls being requeried and specific "on_click" procedures being called.

I inserted the following code at the beginning of each sub and function in the VBA for the form:

sProcList = sProcList & "lstContacts_Click" & nl


  • sProcList is a global string variable
  • "lstContacts_Click" is the name of the sub/function as a string
  • nl is functionally vbCRLF

When I check the variable once the form has opened completely, sProcList is this:

ChangeSortOrder 1

This is actually what I want to be happening, but I don't believe that this is accurate. I don't know a better way to do it.

share|improve this question

1 Answer 1

Since performance is an issue, you want something a little stronger that will track no only the subroutine names, but the time spent within those subroutines. That will result in you finding the biggest offender(s), then you can hone in by adding timers around sections of code within those routines. Years ago I was hired to fix the performance of a newly developed Access database that was delivered to over 70 countries, but had some 2 minute response times under certain conditions. Briefly, I did the following (I will skip the full description): 1. Created a table with fields to store "Start or End" Module Name, Subroutine, Date, Timer, Elapsed, BytesSent, Received, I/O's, etc. 2. Designed a form that could open everything that contains code (Forms, Reports, Modules); provided ability to select desired objects. 3. Unless excluded, code was automatically inserted at the top of the subroutine to journalize a 'Start' record, and wherever you could exit it journalized an 'End'. The form was smart enough to customize the code to capture the object name and the subroutine name. 4. Added a subroutine to process the start/end records and calculate the times, bytes, I/O's. As a simple solution, you could: 1. Add subroutine StartTimer variable to save Timer 2. At top of Sub, save Timer to StartTimer 3. Add Debug.print 'Start XYZ ' & StartTimer 4. At the exit point Calc Timer - StartTimer and debug.print elapsed time. 5. Also could just write lines to some text file.

share|improve this answer

Your Answer


