Out of stack space???

[dynamictiger]

I have a multi subform, subform on a main form. If i am searching through records it dumps me out of access after giving me an out of stack space error.

What does this mean?

How do you control it?

How do I stop it, or handle it in code?

 

[Travis]

Visual Basic for Applications Reference

Out of stack space (Error 28)


The stack is a working area of memory that grows and shrinks dynamically with the demands of your executing program. This error has the following causes and solutions:

You have too many active Function, Sub, or Property procedure calls.
Check that procedures aren't nested too deeply. This is especially true with recursive procedures, that is, procedures that call themselves. Make sure recursive procedures terminate properly. Use the Calls dialog box to view which procedures are active (on the stack).

Your local variables require more local variable space than is available.
Try declaring some variables at the module level instead. You can also declare all variables in the procedure static by preceding the Property, Sub, or Function keyword with Static. Or you can use the Static statement to declare individual Static variables within procedures.

You have too many fixed-length strings.
Fixed-length strings in a procedure are more quickly accessed, but use more stack space than variable-length strings, because the string data itself is placed on the stack. Try redefining some of your fixed-length strings as variable-length strings. When you declare variable-length strings in a procedure, only the string descriptor (not the data itself) is placed on the stack. You can also define the string at module level where it requires no stack space. Variables declared at module level are Public by default, so the string is visible to all procedures in the module.

You have too many nested DoEvents function calls.
Use the Calls dialog box to view which procedures are active on the stack.

Your code triggered an event cascade.
An event cascade is caused by triggering an event that calls an event procedure that's already on the stack. An event cascade is similar to an unterminated recursive procedure call, but it's less obvious, since the call is made by Visual Basic rather than by an explicit call in your code. Use the Calls dialog box to view which procedures are active (on the stack).

To display the Calls dialog box, select the Calls button to the right of the Procedure box in the Debug window or choose the Calls command. For additional information, select the item in question and press F1.


--------------------------------------------------------------------------------
Send feedback to MSDN.Look here for MSDN Online resources.

 

[dynamictiger]

Travis your answer provides more questions. I checked the call stack and it only lists the current procedure.

This particular form has been predeveloped, and I have only just returned to it, as I am nearly ready to wrap the whole project together. The out of stack space does not occur all the time, is not consistent, and is only happening inside this procedure and one like it

Dim rs As Object

'Pickup the clientID from the combo box
Dim strA As String

strA = Nz(Me.cboAddress.Column(3), 0)

Set rs = Me.Recordset.Clone

rs.FindFirst "[ClientID] = " & strA

'force the name record to the appropriate record
'this does not necessarily return the correct site address
'rather just the owner of the site

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

The call stack error is occuring on the Me.Bookmark=rs.Bookmark in both procedures, in both cases the only item in the call stack is the current procedure.

 

[Travis]

try DIM'ing the RS as a Recordset instead of an Object.

 

[dynamictiger]

That code is wizard generated, as it was simplest.

I changed the object to a DAO recordset.

The error still occurs. However, it only occurs on records with pictures. Either loading the record with the picture or unloading the record with the picture.

I am using linked pictures, not OLE.

 

[Travis]

What Code/Method are you using to load the pictures on the Form?

It is possible that you are running out of Memory.

 

[dynamictiger]

This is the method I am using to load the pictures. This code runs in the current event of the sub-subform fsubClientPoolPic.

Private Sub Form_Current()
'Change the image to suit

If Not IsNull(Me.PoolPhotoPath) Then

Me.imgPool.Picture = Me.PoolPhotoPath

Else

Me.imgPool.Picture = ""

End If

End Sub

To add confusion I am currently at version 14 of this application. I just tested version 8. Version 8 does not cause this error.

The picture subform is linked to two unbound fields on the master subform. According to the values of these two unbound fields dictates which picture we are veiwing.

 

[Travis]

How long has it been since you "Compacted/Repaired" the MDB?

 

[dynamictiger]

Sorry Travis I missed your last post. The database compacts and repairs on close. I imported all objects into a new database, compacted and repaired and the error is still present.

I am starting to have an idea what the problem is, although at this time I am uncertain as to how to fix it.

I am using very little code in my application and have developed an alternative approach to requerying the source objects for the subforms.

Initially I was using a source object requery method, however, this loaded and reloaded the sub sub forms many times. I thought of a quicker way with less code, using multiple dual links based on unbound text boxes.

The result was faster load times and less reloads.

However, I have sacrificed some control to the underlying Access.exe.

Stepping through the code the problem appears to be the picture subform loads three or four times during the search of the recordset. Why? You tell me and we'll both know.

It appears if I can load this an alternative way or control it through a module set to true or false I may be able to gain control of this. My attempts so far have totally fouled the functions of the form, however, I am certain with a bit of plodding I can get there.

I guess what I am doing is bleeding edge stuff, as I have not been able to find any documentation anywhere that discusses this technique at all. There is some reference to a similar technique in the Developers Handbook, but I have taken it to an extreme.

On the positive side, what started out as 10 A4 sheets of code became 1 A4 sheet so, in theory anyway, less bugs. All I have to do now is figure out how to control the load behaviour of the current events.

What I can see is that the forms build from the bottom up. That is the lowest subforms (there are several) are requeried several times in the search of the recordset. Why????

I tried controlling this with a function called load and setting it according to the recordset position. However, this seemed to have totally prevented the master subform aligning itself even when requeried. Why???

Logically you would think the Master subform would load and then the sub subforms this appears to not be the case at all. Why???

I am looking forward to learning how to control this odd behaviour. I am starting to wonder if it is something to do with service pack 2.

 

[Travis]

Loading order is Sub Sub Form, Sub Form, Main Form. This has always been the way Access works. You may need to add code that suppresses the loading of the Picture until the End.

 

[dynamictiger]

Travis what is really confusing me is that the sub form loading behaviour is being trigerred by the recordset search behaviour. I can not see a reason why this should trigger any action on the subforms at all.

 

[dynamictiger]

Travis, even after cutting the code down - I still had too much code. This linking system I am using is truly amazing in its lack of code.

The solution is / was to drop the search of the recordset. It appears this was causing a requery action on the subform that simply was not required.

The final solution was acheived using ADO and calling this as a seperate search to set the main form. The code that works is:

Private Sub SearchAddress(strA As String)
'The wizard generated code gives an error
'It seems this is caused by the recordset clone method
'trying ADO instead with a straight search
Dim rst As ADODB.Recordset
Dim strCriteria As String

Set rst = New ADODB.Recordset

Set rst.ActiveConnection = CurrentProject.Connection

rst.CursorType = adOpenKeyset

rst.LockType = adLockOptimistic

rst.Open "qryMainClient", Options:=adCmdTableDirect

strCriteria = "ID = " & strA

rst.Find strCriteria

rst.Close

Set rst = Nothing

End Sub

This code forces the main form to the correct record, without requerying the form. I then set the links and that is that. No requerying and the Out of stack space error vanishes.

I am now applying this to the rest of the form and so far it works great.

Thanks for the suggestions and input