Stop "Out of stack space" when paging thru records 1

[eksortso]

Hi, I don't know if anyone else is encountering this, but I could use your help.

I have a form in an Access 2002 database (an MDB) which changes the foreground colors of some of the textboxes when the OnCurrent event is triggered. The purpose of this is to warn users of (non-fatal) inconsistencies in their inputs.

When the PageDown key is held for a short length of time, Access pages through the records, which is its default behavior. But when the key is lifted, the textboxes flicker. So I suspect that the OnCurrent events are being queued in stack space while the updates are performed.

Now, when this action is taken for too long, eventually I get an error message, "Out of Stack Space." Since there is no recursion involved in these procedure calls, I can only assume that the build-up of unhandled events is responsible for the error.

What can I do to prevent this sort of behavior from occuring, short of telling users not to hold down the PageUp/PageDown keys?

 

[willir]

eksortso

You are probably correct. You seem to know enough that a recursion loop unchecked will crash a program. The On current event can be fired off several times for the current record.

Although you may not have recurssion, do you have a lot of calls in your form? Each call will reserve it own memory, and chew up resources -- resources are relased / returned to the pool as the top of the stack closes, and each subsequent call is allowed to finish it's routine.

Then the conidtional formatting and other code used by Microsoft, the code we never see, needs to occur. And unless you are a MS developer, we have no idea how this code is written -- but thes backend programs also have to compelte their tasks.

Perhaps "slow down" might be some advice to give...

 

[willir]

eksortso

Son-of-gun....
I ran into a weird problem last night that sounds related to yours. I have an unbound text box that calcuates the total for a subform. To advise the user of a negative or postiive number, I used Access 2000's conditional formatting. Under some situations, and I can duplicate the problem, the conditional formatting goes into an infinite loop until I break the condition causing the infinite loop.

The infinite loop is caused by Access itself, not my coding. Looks like a bug, smells like a bug, feels like a bug - it must be a bug!

I deleted the conditional formatting and the problem went away.

Richard

 

[eksortso]

Woah, an actual infinite loop within Access itself? I imagine that if the problem is similar enough to my own, what's happening is that the system is just very overwhelmed with handling the formatting (I don't use conditional formatting) while calculating the subform totals for each record, so much so that it eats up the system's resources.

I'm pretty sure that my problem doesn't involve an infinite loop, because in the non-fatal cases, the formatting can proceed without locking up the system, and it eventually finishes, given enough time.

Did you find a workaround for your formatting issue, or did you just leave off special formatting completely?

 

[willir]

eksortso

On the computer in question, I am using Access2000, and it is patched to the latest level.

I am pretty sure it is a bug, and the work-around was to delete th conditional formatting. The fact that I can duplicate the problem enforces the idea that it is a bug. the Sum total flickers on and on and on and ....

My point is that Access does a lot of stuff behind the scenes that would drive most amatures dizzy. Depending on the computer, this can hinder or cause unexpected results under unusual circumstances. (akin to trying to print when your TEMP directory has a zillion files... Can you spell crash at a zillion and one?)

There is also a timing issue... Sometimes the computer will fire off a command and contiue on to the next command with assumption that the outcome for the first command will occur within a timely period. But when it does not, strange things can happend. (Example: old network cards would take a while to warm up. So when you logged in the first time, the hardware level was not ready to bind to the protocol level when trying to login. You had to reboot your computer a second time by which time the NIC had warmed up, and the protocol layer successfully bound to the hardware levle. Ancient example, but suffices as an explanation.)

 

[CajunCenturion]

eksortso - What I would do is add the Form_KeyDown event and check for values 33 and 34 (Page Up/Down) and add a DoEvents to the handler to allow the system to catch up on its event handling.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
   If ((KeyCode = 33) or (KeyCode = 34)) Then
      DoEvents
   End If
End Sub

willir - I'm not sure you're situation is the same. What are the conditions under which you can duplicate the problem?


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[willir]

Cajun - Great solution for eksortso

In my situation, which I am not going to worry about, is a problem with conditional formatting.

On the main form, a field calculates the sum for child records in a subform. Worked fine.

=IIf(Not IsNull([PurchaseType]),nz(DSum("[curItemTotal]","OrderDetailTbl","[lngOrderID] = " & [lngOrderID]),0),0)

If there are no entries yet created for the OrderDetail, then the form should display 0.

Then I thought I would get fancy, and add conditional formatting (add colour to field background if the DSUM total is above zero). When I did this, and met the condition for the DSUM, the DSUM flicker on and off -- seems to be contineously setting the format for the DSUM field.

Once there are child records, the problem goes away.

I suggest this is a bug because...
- The value in the DSUM field should be interpreted as zero before and after the IIF condition is met.
- If I take away the conditional formatting, the display problem goes away.

I suspect the problem lies in the possibility that the
nz(DSum("[curItemTotal]","OrderDetailTbl","[lngOrderID] = " & [lngOrderID]),0)
may be re-calculated as null and then zero which resets the conditional formatting.

Wierd, but I am not going to worry about it.

Richard

 

[TheAceMan1]

How are ya eksortso . . . . .

I've had similar problems like this in the past. Espcially with the [blue]On Current Event[/blue], but only where large record counts are concerned. Try this:

[blue]Make the event procedure [purple]Static[/purple]. Like:

Private [purple]Static[/purple] Sub FormName?_Current()

[purple]This put the procedure in memory instead of on the stack![/purple][/blue]

Only problem is, any declared variables retain their values and may have to be initialized when the procedure is run.

Remember Our Veterans! Remember Sept 11th!

 

[CajunCenturion]

TheAceMan1 - That's an interesting solution, one that I had not thought of.

But that got me thinking (dangerous, I know) -- What's happens inside a Static Procedure when it's called a second or third time, or nth time, when previous invocation(s) of the procedure have not yet completed? If you're referencing controls inside of a Static Form_Current related to a continuous form, which copy of the control(s) would be referenced. If the sub were not static, then it's easy to see how each instance of the sub relates to the active controls at the time that instance was created. But if the Sub is static, I'm really not sure. Can you shed some light?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[TheAceMan1]

How are ya Cajun . . . .

Slight misquote. I should have said:

[blue]This puts all local variables & references in memory instead of on the stack![/blue]

Access of course maintains a pointer to the routine. It works the same as declaring static variables within the procedure, only their all static. The values are maintained as long as the form is open.

As for dangerous, no problem as long as the event procedure is not recursive. If it is recursive, instead of crashing the stack and getting an error message, Access itself will crash when memory is fully depleted (Access first tries swapping out to disk, then locks-up. I tested this on a machine with only 32MB ram).

This is the real question here. [purple]Is [blue]eksortso's[/blue] Current Event procedure recursive?[/purple] Crashing the stack certainly indicates this is possible.

A Microsoft reference for this appears below (goto chapter 7 after you get there). Scroll down to the header [blue]Minimizing Stack Usage[/blue], then a little further to the subheader [blue]Declare Procedures or Local Variables with the Static Keyword[/blue]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/bapp2000/html/ba2000intro.asp

I initially was going to suggest increasing the number of [purple]Processing Threads[/purple] Access uses (requires a registry tweak), but the effect is never really seen unless a large amount of changes are being made to the DB and not sure if this would take care of the problem. Within the same reference above, at the top, under the header [purple]Adjusting Windows Registry Settings to Improve Performance[/purple] is info on the Threads.

Remember Our Veterans! Remember Sept 11th!

 

[CajunCenturion]

I guess I didn't ask the question very well.

When an event is triggered, and instance of the event handling procedure is placed on the stack, just as is true for any procedure when it is called. You can get a stack overflow if you trigger the event at a faster rate than the time required for the event to execute. For example, for the purposes of this hypothetical discussion, assume that it takes 3 seconds for the event handler (non-recursive) to execute, but I trigger the event once every second. Even though the event handler is not recursive, I will eventually get a stack overflow.

My question was, having never used a Static event handler, Is this behavior the same if the event handler is declared Static, and if so, then I'm not sure anything has changed with respect to this problem. But if the behavior is different, then how does it now behave? That is, when the event is being triggered at a faster rate than the time required to handle the event, when the event handler is Static?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[TheAceMan1]

Cajun . . . . .

[blue][purple]instead of crashing the stack and getting an error message[/purple], Access itself will crash when memory is fully depleted (Access first tries swapping out to disk, then locks-up[/blue]

This was tested with [blue]Me.Requery[/blue] in a Do Loop. Without the static invocation would run about 6 seconds, with 37 minutes.

So . . . . Ya Think?

Remember Our Veterans! Remember Sept 11th!

 

[CajunCenturion]

Without the static, program ran for about 6 seconds and crashed. With the static, ran for 37 minutes before crashing. Am I interpreting your results correctly? If so, that is revealing, and I'm glad to know about it. Thanks TheAceMan1.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[TheAceMan1]

CajunCenturion . . .

Thanks Cajun, coming from you its an honor!

Just gotta be a little careful with objects that include large record counts in the routine. When it finally crashes its very nasty. Can only get out of it with a [purple]Three Finger Salute![/purple]

This is in Access 2k. Don't know yet about its successors. I would believe with memory counts as high as they are today, this should present no problem, unless we make an error in coding.

Boy! . . . . Am I starting to think my microsoft or what! hahaha . . haaa ;-)

Remember Our Veterans! Remember Sept 11th!

 

[CajunCenturion]

The key is to realize that it is not solving the problem, it is just making it much more unlikely to occur.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[eksortso]

TheAceMan1,

Even though my Form_Current() is not recursive, it also has no local variables. And when I tested its performance on my form, it made no difference whether it was declared Static or not. It's the calls to the routine that are hogging up stack space, and not local variables.

However, I'll remember that Static declaration in the future. It might come in handy.

 

[eksortso]

CajunCenturion,

I've had problems with DoEvents in the past, and I still am, sad to say.

Calling DoEvents in this case doesn't solve the stack space problem. In fact, performance was hampered further. (Handling the KeyDown event added to the problem, evidently.)

But I was able to gleen some insight into the problem. When I ran the code before, I just received the standard user pop-up message; after clicking OK, the system caught up with its events. But this time, the error occured in the middle of my code, while it was calling a (non-recursive) helper function. I looked at the call stack at that point. The helper function never appeared (maybe since I left its variables declared Static, as per TheAceMan1's suggestion), but I saw alternations of Form_Current and <Non-Basic Code>, with the Form_KeyDown every so often similarly sandwiched. Perhaps Access's event handling induced the sort of pseudo-infinite recursion that willir was talking about?

Actually, reviewing the problem points out more Access flukes. Since the delayed reformatting occurs as if the data is being read currently, it seems that Access puts the entire form's state (or at least part of it) on the stack with each event procedure call! Anyone have any experience bearing this out?

I'll try moving DoEvents out of KeyDown and into an event procedure that already exists. Then I'll let you all know the results.

I should add that this problem might not be as prevalent if I were to convert the MDB to an Access Project, an ADP. All of the data would be loaded, and after that, performance would be very quick. But reimplementation would take far too long, and with certain changes afoot, I might eventually move away from Access, at least for larger projects.

 

[eksortso]

Sorry, but on reflection, I've realized that there is no safe place I can move DoEvents. I can't put it in Form_Current(), because another call to Form_Current() could mangle the state of the system. I don't want to even risk something like that.

So, I'm back to square one. But I've given you some more info to work with. Any advice?