How to fix "out of stack space" error?

Question

I have code which takes a table, and rearranges the table to form a new table. It worked with a small amount of data, but now that I tried to run the same code with over 1,000 records, it is getting Error 28 which is "Out of stack space". I will not copy all of my code here because it would be way too much and I think unnecessary, unless you think otherwise. I think it is a problem with my recursion of the sub. I need this because a DONOR_CONTACT_ID can only have 4 recipients, if it has more, then it must create a new record with the same DONOR_CONTACT_ID and populate the recipients.

Here is the sub routine which is getting the error:

Sub NextDonor()

With rstOutput
.FindNext "[DONOR_CONTACT_ID] = " & strDonor2
'Find the next record in T_OUTPUT with that DONOR_CONTACT_ID

            If .NoMatch Then
                'If there are no more records with that DONOR_CONTACT_ID, add a new one
                .AddNew
                !DONOR_CONTACT_ID = strDonor1
                !RECIPIENT_1 = strRecip1
                !ORDER_NUMBER = strOrderNum1
                .Update
            Else
            'A second DONOR_CONTACT_ID in T_OUTPUT exists. Check to see if all fields are filled.
                If !DONOR_CONTACT_ID = strDonor2 Then
                    If IsNull(!RECIPIENT_2) And Not (IsNull(!RECIPIENT_1)) Then
                    'RECIPIENT_2 is empty, so populate it
                        .Edit
                        !RECIPIENT_2 = strRecip1
                        .Update

                    ElseIf IsNull(!RECIPIENT_3) And Not (IsNull(!RECIPIENT_2)) Then
                    'RECIPIENT_3 is empty, so populate it
                        .Edit
                        !RECIPIENT_3 = strRecip1
                        .Update
                    ElseIf IsNull(!RECIPIENT_4) And Not (IsNull(!RECIPIENT_3)) Then
                    'RECIPIENT_4 is empty, so populate it
                        .Edit
                        !RECIPIENT_4 = strRecip1
                        .Update

                    ElseIf Not IsNull(!RECIPIENT_4) Then
                    'RECIPIENT_4 is filled, so run this function again
                        Call NextDonor
                    End If

                End If
            End If
End With
End Sub

The error is in the line where it says "Call NextDonor", probably because of the recursion. If you need me to clarify what my code is trying to do, or if you want me to copy other parts of my code, just let me know.

Answer 1

Actually your recursive code and 1st answer both skip past the recipient if the 4th slot is full, you iterate with another Find and you lose the current recipient! This also eliminates the recursion. instead:

 If .NoMatch or (not isnull(!recipient_4)Then
            'If there are no more records with that DONOR_CONTACT_ID, add a new one
            '    or current record is full
            .AddNew
            !DONOR_CONTACT_ID = strDonor1
            !RECIPIENT_1 = strRecip1
            !ORDER_NUMBER = strOrderNum1
            .Update
        Else

Answer 2

Try this to avoid recursion ...

Sub NextDonor(byref Again as Boolean)
With rstOutput
DoItAgain :
.FindNext "[DONOR_CONTACT_ID] = " & strDonor2

  If ....
    ....
  ElseIf Not IsNull(!RECIPIENT_4) Then
    'RECIPIENT_4 is filled, so run this function again
    Goto DoItAgain
  End If
End Sub