Results 1 to 13 of 13

Thread: nested too deep

  1. #1
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38

    Unanswered: nested too deep

    I got this msg 'too many transactions already nested' today after trying to add >50 scans to the db. I processed all the scans while inserting the index fields: last name, title, first name ... .

    When i got thru only ~50% of the scans were in the db. So managed to find where the process had stopped & started there. First scan i tried to process produced the msg "... nested too deep ..." or something close to that. So i closed the db & restarted, then processed the rest of the scans. My question is what am i doing in access 2007 that is stacking (?) processes (?). Wonder if u would look @ the pgm to see where i am stacking processes. Here is the program:

    Code:
      Private Sub oldget_scans_Click()
    On Error GoTo Err_search_for_project_Click
    Dim strdb As String, intI As Integer, strTem As String, strTmp As String, intTem As Integer, strTemp As String
    Dim I As Integer, J As Integer, K As Integer, strLastnameold As String, strAcceptancedateold As String
    Dim dbsDb As dao.Database
    'Dim dbsDb As Database
    Dim rstProj As dao.Recordset
    'Dim rstProj As Recordset
    Dim altMembers As Recordset
    Dim rsPictures As Recordset
    Dim rsPictures2 As Recordset      ''''''' ADDED
    
    Set dbsDb = CurrentDb
    'Set rstProj = dbsDb.OpenRecordset("Tbl_files", dbOpenTable, dbDenyRead)
    Set rstProj = dbsDb.OpenRecordset("Tbl_files query")
    Set altMembers = dbsDb.OpenRecordset("efccmembers", dbOpenDynaset)
    ' Call ReadTxtFilemine
    altMembers.MoveLast     ' add to the members
    strTem = altMembers![LastName]
     '       strTmp = rstProj![FileName]
      '      rstProj.MoveNext
       '     strTemp = rstProj![FileName]
    Recover:
    rstProj.MoveFirst       ' should be a new list
    strTemp = InputBox(" Are we recovering from an error? if yes enter the end of the filename ie 554p01. else null", "get scans")
        If strTemp = "" Then GoTo Caryon
        While Not rstProj.EOF
            strTmp = rstProj![FileName]
            If InStr(strTmp, strTemp) > 0 Then GoTo Caryon
            rstProj.MoveNext
        Wend
        If (MsgBox("Sorry that filename not found:" & strTemp, vbOKCancel)) = vbCancel Then GoTo Quit
        GoTo Recover
    Caryon:
        strLastnameold = " "        ' start with blanks
        strAcceptancedateold = " "  ' start with blanks
    
    While Not rstProj.EOF
                strTmp = rstProj![FileName]
                I = Getnumber(strTmp, ".tif")
    
    Carryon1:
                With altMembers
                            .AddNew
                            
                            Set rsPictures = altMembers.Fields("front").Value
                            rsPictures.AddNew
            
                            rsPictures.Fields("filedata").LoadFromFile rstProj![FileName]      ''''''' MODIFIED (removed equal sign)
    
                            rsPictures.Update
    
    Tryagn:
                            strdb = StrConv(InputBox("Last name pse", , strLastnameold), vbProperCase)
      '                      strdb = InputBox("Last name pse")
                            If (strdb = "") Then
                                MsgBox (" We are stopping")
                                GoTo Quit
                                End If
                            strLastnameold = strdb      ' keep this for next time arround
                            .Fields("lastname") = strdb
                            strTemp = .Fields("lastname")
                            strdb = StrConv(InputBox("Title Mr/Mrs/Miss/Ms ... pse"), vbProperCase)
                            If (strdb = "") Then
    Tryagnmsg:
                                MsgBox (" Lets try that complete name again")
                                GoTo Tryagn
                                End If
                            .Fields("title") = strdb
                            strdb = StrConv(InputBox("First names pse"), vbProperCase)
                            If (strdb = "") Then GoTo Tryagnmsg
                            .Fields("firstname") = strdb
                            On Error GoTo Tryagnmsg        ' in case of illegal date
                            strdb = StrConv(InputBox("Acceptance date pse", , strAcceptancedateold), vbProperCase)
                            If (strdb = "") Then GoTo Tryagnmsg
                            .Fields("datehired") = strdb
                            strAcceptancedateold = strdb    ' keep this for next time arround
                            On Error GoTo Err_search_for_project_Click
    Aloop:
                            rstProj.MoveNext
                            strTemp = rstProj![FileName]
                            J = Getnumber(strTemp, ".tif")
                            If J = I + 1 Then GoTo Carryon
                            If (MsgBox("Do not think this is the correct filename sequence" & vbCr _
                            & strTmp & vbCr _
                            & strTemp, vbOKCancel + vbDefaultButton2, "get scan")) = vbCancel Then GoTo Quit
                            If MsgBox(" ARE U SURE WE SHOULD CARRY ON????", vbOKCancel) = vbCancel Then GoTo Quit
                            
                            
    Carryon:
            ''''''' NEXT FOUR LINES ADDED
                            Set rsPictures2 = altMembers.Fields("back").Value
                            rsPictures2.AddNew
                            rsPictures2.Fields("filedata").LoadFromFile rstProj![FileName]       ''''''' MODIFIED (removed equal sign)
                            rsPictures2.Update
    
                           rstProj.MoveNext    ' Should be pairs of tif files
                            .Update     'Update parent
                End With
    
        Wend        ' loop
        
    ''''''' NEXT FIVE LINES ADDED TO CLOSE OUT RECORDSET AND DATABASE OBJECTS
    Exit_search_for_project_Click:
    Quit:
    rsPictures2.Close
    rsPictures.Close
    Quit1:
    altMembers.Close
    rstProj.Close
    dbsDb.Close
    
    MsgBox ("we are thru")
        Exit Sub
    Err_search_for_project_Click:
        MsgBox (" Lastname: " & altMembers![LastName] & "   Filename: " & strTmp)
        MsgBox (Err.Description & "   Code: " & Err.Number)
        If (Err.Number = 91) Or (Err.Number = 3420) Then GoTo Quit1 ' should be: obj variable or with block var no set
        Resume Exit_search_for_project_Click
    
    End Sub
    Last edited by jtmedin; 06-26-11 at 01:59. Reason: Correct code

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You don't have a line label "Nomo:" anywhere, although you reference it 3 times in your code "GoTo Nomo." If you create that label, and maybe some code, the program might work.

    Also, your top iIf statement
    (If Not (.NoMatch) Then)
    does not have a matching End If statement. That might also be the problem.


    Sam

    Yes, it does have an End If. I didn't see it.

  3. #3
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    Yes i have that label nomo. I removed a lot of comments & might have deleted that also. But the code compiles. So i know the code is ok except for logic.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yuck, not seen such a programming style in ages. the labels were more of a kludge workaround for error reporting, not for normal code. sorry just felt I had to say that. I almost feel the need to have a shower after reading it

    I suspect the problem may have something to do with the label handling. Im suspicious of tellall: and the jump to it. my reading of that code is that you jump to tellall and then hit another endif which will blow the stack. bear in mind the error message may well mean that you have hit an unexpected endif / loop rather than set too many.

    you are not making things easy for yourself with the spacing and indentation.

    I did look briefly at trying to knock that code into more the modern VB style as opposed to spaghetti code with jumps/goto's evrywhere but realised I have neither the time or inclination. I think you need to redesign the whole thing
    use a form to capture the data
    attach some code behind the various for elements

    on indentation for each code block eg if's, if's + else's, do loops, while and so on indent by a few characters, when you hit the end of that block with an endif or wend, whatever back out

    instead of iterating through the whole recordset instead use the power of SQL to find the records for you
    eg where you iterate through the table to find the surname.
    you could apply the lastname as a filter
    or use it in a where clause
    or use it in a dlookup.

    consider indexing the columns you are going to search on for performance reasons
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    Would u believe i posted the wrong code. Ur observations are probably correct, i may have hit a stack problem. I will post the correct code later after thinking about the stack problem. BTW i have been in digital programing since 1957 so that my explain my obtuseness :-). It was late @ nite when i posted the code so should wait until im fresh.

  6. #6
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    Ok i have posted the correct code for my problem. I belive u are correct about stack problems. Would u look @ the code & offer suggestions OR perhaps u have a list of nonos about stack problems. TIA

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    goto has been out of fashion for decades.

    imagine:
    do while 1=1
    goto imagine
    loop

    i have not run that code and have no idea what (if any) error might arise - but that uncertainty is a really good reason not to write code like that.

    goto is your nono!

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by jtmedin View Post
    Ok i have posted the correct code for my problem. I belive u are correct about stack problems. Would u look @ the code & offer suggestions OR perhaps u have a list of nonos about stack problems. TIA
    In all honesty that code is a disaster in the making based on the modern design stylistics.

    I look at it and just say to myself WTF. Im not having a pop, just I stopped that sort of malarkey in the 80's. I don't really want to spend the time involved in trying to decipher your code.

    part of it is down to style
    your code is tricky to read
    ferinstance
    Code:
    If J = I + 1 Then GoTo Carryon
    what is J, what is I, granted you can read the code and work out what J & I are, but I'd suggest instead of J use something meaningful (granted J may mean something to you, its your code it damn well ought too, but someone else looking at this code is gibberish, bear in mind that someone else looking at the code may be you in a years time)
    eg
    CurrentTiffFileNo instead of J
    on a quick scan of the code I've still not deciphered what I is.

    quite frankly I suspect the style of your code inhibits other helping you. I suspect that developers of a certain age probably can understand your code, but the younger turks will look in horror and not understand or not want to.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    Ok thanks, been using goto's probably longer than u have been alive. Its tough trying to change.

  10. #10
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    Ok i have spent some time with this code & unable to solve it with if else endif ... . So how about giving me some help in getting rid of goto's.

    Recover:
    rstProj.MoveFirst ' should be a new list
    strTemp = InputBox(" Are we recovering from an error? if yes enter the end of the filename ie 554p01. else null", "get scans")
    If strTemp = "" Then GoTo Caryon
    While Not rstProj.EOF
    strTmp = rstProj![FileName]
    If InStr(strTmp, strTemp) > 0 Then GoTo Caryon
    rstProj.MoveNext
    Wend
    If (MsgBox("Sorry that filename not found:" & strTemp, vbOKCancel)) = vbCancel Then GoTo Quit
    GoTo Recover
    Caryon:
    Some comments about the logic: rstproj has a list of filenames & sometimes to recover from errors we startout by moving to the filename where we quit otherwise we start @ the start. Recover & Caryon are in the code. Quit is where we close the sets & exit. What perplexed me most was the falling thru of the 'while not rstproj.eof'. TIA

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could try (not tested):
    Code:
    Recover:
    
        rstProj.MoveFirst ' should be a new list
        strtemp = InputBox(" Are we recovering from an error? if yes enter the end of the filename ie 554p01. else null", "get scans")
        If strtemp <> "" Then
            Do Until rstProj.EOF
                strTmp = rstProj![FileName]
                If InStr(strTmp, strtemp) > 0 Then Exit Do
                rstProj.MoveNext
            Loop
        Else
            If (MsgBox("Sorry that filename not found:" & strtemp, vbOKCancel)) = vbCancel Then Exit Sub
        End If
    
    Caryon:
    Have a nice day!

  12. #12
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    Ah thats what i forgot the 'exit do' & 'exit sub' Thanks

    After some review i believe dropping out or the do loop will send the operator to caryon, when it should go to the "sorry that filename ..." msgbox. Also if the operator does not cancel the msgbox then i would like to give them another chance with a new filename, that was the original 'goto recover'. Perhaps that msgbox could just 'exit sub'. Also if one 'exit sub' does that close all the sets. Looks like a couple of logic errors.

    I should get some time to day to work out the problems. Thanks for the input :-).
    Last edited by jtmedin; 06-30-11 at 12:33.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •