My code is possessed (1 Viewer)

[Alisa]

How do you find out what called a procedure?

There is one spot in my application where a screen gets "stuck" in an endless refresh cycle. What is strange is that when the code gets to the end of the on current event, and it should be done, for some reason this other procedure starts again and then the on current event starts again. I can't find anything that should be causing this to happen by stepping through the code. How do I debug this? Is there anything that can tell me what is triggering the procedure to run? Or have I finally lost my mind?

 

[RuralGuy]

Yes you have officially lost your mind. Have you tried a Compact and Repair? How about inporting everything into a new db?

 

[boblarson]

The free MZ Tools has a menu item which will let you see what is calling your procedure.
http://www.mztools.com/v3/mztools3.aspx

Also, you need to make sure you don't have something that uses the After Update event to make an update. That can get you into a nasty loop.

 

[Alisa]

Thanks for responding guys.
Yes I have tried compact and repair, along with decompiling.
I have not tried importing everything into a new database because it would be a big pain but I may try that.
I do have mztools, and I have looked at everywhere the procedure is called. What is strange is that when the procedure starts, nothing that I can see has called it. I put a break point on end sub of the form current event. When it gets there, I step once, and it starts this other procedure. That procedure does NOT call the form current event, but for some reason, when that procedure finishes, the form current event starts again. What I was hoping to find is some trick where once you are in the procedure, you can find out what triggered it.

There are lots of after update events that trigger this procedure, but if the after update event itself isn't running, that can't be what is causing the procedure to run, right?

 

[RuralGuy]

It only take a minute or two to import an entire db into a fresh new db.

 

[Alisa]

It only take a minute or two to import an entire db into a fresh new db.

Yes but I have security set up on this db, doesn't that make it more complicated?

 

[boblarson]

For the MZ Tools procedure caller tool, in the VBA Window:

1. Go to the procedure you want info about and insert your cursor.

2. Click TOOLS

3. Select MZ TOOLS

4. From the menu that appears select PROCEDURE CALLERS

I would also try importing to a new, blank file. But if that doesn't work then perhaps you should post the db here and someone can take a look and see.

 

[RuralGuy]

Yes but I have security set up on this db, doesn't that make it more complicated?

Hmmm...could have a point there. I'm not familiar with security.

 

[Alisa]

Ok, I imported everything into a blank database, that was a lot easier than I thought it would be, but it didn't solve the problem.

Yes, I have used mztools and looked up every procedure that calls this one, but the problem is that it appears that the procedure is starting without being called.

I stepped through the whole thing and this is what I found:
The form current event calls this other procedure.
The other procedure runs and completes.
Then the form current event completes.
After the form current event completes, it starts the second procedure again for no discernable reason (the last line of code to run is Exit Sub).
After the other procedure completes, the form current event starts again, but not from the begining, it starts from the line of code AFTER this second procedure was called in the first place.

I think what I will do is comment out the calls to this other procedure to try to narrow down where it is going awry. If you have any other thoughts, I would be grateful to hear them.
Thanks,
Alisa

 

[RuralGuy]

Sounds to me like you are hot on the right trail Alisa.

 

[Alisa]

Ok it gets stranger, but maybe this is actually leading somewhere:

When I comment out every single call to the procedure, AND comment out the procedure itself, it doesn't fix the behaviour - the same exact thing happens except it is with the next procedure that is called from the form current event.

The situation is not actually as simple as I made it sound before, and maybe this is more to the point of the problem: The form that I am dealing with is actually a subform on a main form. The current event of this subform triggers a requery of a second subform on the same main form because there is a parent/child relationship between the 2 subforms. The current events of the two subforms are almost identical in that they call many of the same procedures. Now I am wondering if that is actually the root of the problem here.

 

[RuralGuy]

A loud roar is heard coming from the Alisa rooting section.

 

[Banana]

Sounds to me there'a a loop that touches several procedures in a step before starting again.

Some more possibilities to investigate:

1) Are you using Load or Open events? If so, do they call the same procedures used in Current for any three OnCurrent events?

2) In a normal firing sequence, the subform's current event fires before the main form's current event. I can't remember if the main form's Load event fires before or after subform's Load event as well. Thus I can see a case where a subform's Current procedure triggering another subform's current event, which in turns trigger main form's current which cannot be completed because the first subform's current event has not been completed.

Hope that gives you some clues...

PS In such case, one has to be quite careful with how the loading of forms are executed. In general when we have procedures that deals with loading, those procedures should be in one Load event and that event should be the last Load event (I'm thinking main form, but can't remember if that's right) so we know that all forms in questions are ready to be edited with and there's no premature calls. Likewise, when procedures deals with current event (such as requery for example), we have to ensure that it does not act prematurely by placing in it in single event that is last or at least has a check in place to handle exceptions where other form isn't ready for whatever reason.

 

[ByteMyzer]

Ok, I imported everything into a blank database, that was a lot easier than I thought it would be, but it didn't solve the problem.

Yes, I have used mztools and looked up every procedure that calls this one, but the problem is that it appears that the procedure is starting without being called.

I stepped through the whole thing and this is what I found:
The form current event calls this other procedure.
The other procedure runs and completes.
Then the form current event completes.
After the form current event completes, it starts the second procedure again for no discernable reason (the last line of code to run is Exit Sub).
After the other procedure completes, the form current event starts again, but not from the begining, it starts from the line of code AFTER this second procedure was called in the first place.

I think what I will do is comment out the calls to this other procedure to try to narrow down where it is going awry. If you have any other thoughts, I would be grateful to hear them.
Thanks,
Alisa

The first clue that jumps out at me is the fact that the other procedure is being called from the form's Current event. There are a number of VBA operations in relation to a form that can re-trigger the Current event, which could cause the program to fly up its own @$$.

You might try something like the following approach:

[COLOR="Navy"]Private Sub[/COLOR] Form_Current()

    [COLOR="navy"]Static[/COLOR] bIsExecuting [COLOR="navy"]As Boolean

    If[/COLOR] bIsExecuting = [COLOR="navy"]False Then[/COLOR]

        bIsExecuting = [COLOR="navy"]True[/COLOR]

        [COLOR="DarkGreen"]' Insert your code here[/COLOR]
        [COLOR="navy"]Call[/COLOR] MyOtherProcedure(MyParameter)
        [COLOR="darkgreen"]' etc.[/COLOR]

        bIsExecuting = [COLOR="navy"]False

    End If

End Sub[/COLOR]

 

[Alisa]

The first clue that jumps out at me is the fact that the other procedure is being called from the form's Current event. There are a number of VBA operations in relation to a form that can re-trigger the Current event, which could cause the program to fly up its own @$$.

You might try something like the following approach:

[COLOR="Navy"]Private Sub[/COLOR] Form_Current()

    [COLOR="navy"]Static[/COLOR] bIsExecuting [COLOR="navy"]As Boolean

    If[/COLOR] bIsExecuting = [COLOR="navy"]False Then[/COLOR]

        bIsExecuting = [COLOR="navy"]True[/COLOR]

        [COLOR="DarkGreen"]' Insert your code here[/COLOR]
        [COLOR="navy"]Call[/COLOR] MyOtherProcedure(MyParameter)
        [COLOR="darkgreen"]' etc.[/COLOR]

        bIsExecuting = [COLOR="navy"]False

    End If

End Sub[/COLOR]

Wow I can't believe that worked.
I put isexecuting variables in the current events of both subforms and the problem is gone.
Thank you thank you thank you!

 

[RuralGuy]

Taa...Daa...the rooting section starts to do the wave.

 

[ByteMyzer]

You are most welcome. I'm glad it worked for you.