Results 1 to 6 of 6
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Error 6: Stack Overflow

    Hi all,
    I am using a module for my Form_Current() and below is the code. Now this works just fine on all my forms except one (ZipCodeFrm) which I believe the issue is because of the amount of records (41,659) records. Is there something I need to change in this to get rid of that error? Error 6: Stack Overflow

    After clicking OK on error, all my records are there but I noticed that the record counter (TxtRecordNo) is blank

    Thanks
    Dave

    [CODEPrivate Sub Form_Current()
    On Error GoTo Form_Current_Error
    Call subCommon_Form_Current(Me.Form)
    CboZipCodeSearch.Requery
    Me.RecordLock = True
    Call RecordLock_Click
    Call ButtonControlOne
    Me.Form.Caption = "ZipCode"
    Form_Current_EXIT:
    Exit Sub
    Form_Current_Error:
    Select Case Err
    Case Else
    'Call fcnLogError(Err.Number, Err.Description, " in Form_Current of VBA Document Form_frmAttendees", , True)
    End Select
    Resume Form_Current_EXIT
    End Sub


    ][/CODE]

    [CODEOption Compare Database
    Option Explicit
    Public Sub subCommon_Form_Current(frm As Form)
    Dim rst As DAO.Recordset
    Dim nCount As Integer, nPosition As Integer
    nCount = frm.Recordset.RecordCount

    On Error GoTo Err_Handler

    If nCount = 0 Then
    MsgBox "No Records exist yet.", vbOKOnly, " R E S T R I C T I O N "
    Exit Sub
    End If

    Set rst = frm.RecordsetClone
    rst.MoveLast
    rst.MoveFirst
    nCount = rst.RecordCount
    nPosition = frm.CurrentRecord
    frm!TxtRecordNo = nPosition & " of " & nCount
    frm.CmdFirst.Enabled = True
    frm.CmdPrev.Enabled = True
    frm.CmdNext.Enabled = True
    frm.CmdLast.Enabled = True
    'disable as appropriate
    If nCount = 1 Then
    frm.CmdFirst.Enabled = False
    frm.CmdPrev.Enabled = False
    frm.CmdNext.Enabled = False
    frm.CmdLast.Enabled = False
    ElseIf nPosition = 1 Then
    frm.CmdFirst.Enabled = False
    frm.CmdPrev.Enabled = False
    ElseIf nPosition = nCount Then
    frm.CmdLast.Enabled = False
    frm.CmdNext.Enabled = False
    End If

    Exit_Handler:
    If frm.Dirty Then frm.Dirty = False
    Set rst = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err
    Case Else
    MsgBox "Error " & Err & ": " & Error$, vbExclamation, "Form_Current()"
    Resume Exit_Handler
    End Select
    End Sub

    ][/CODE]

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I can see you have tried to use the code blocks but without success - better to paste your code in then highlight and select code blocks. You should be able to edit your post to correct

    this is missing the closing square bracket after CODE
    [CODEOption

    your missing bracket is here
    ][/CODE]

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Sorry about that, here ya go!

    Code:
    Private Sub Form_Current() On Error GoTo Form_Current_Error Call subCommon_Form_Current(Me.Form) CboZipCodeSearch.Requery Me.RecordLock = True Call RecordLock_Click Call ButtonControlOne Me.Form.Caption = "ZipCode" Form_Current_EXIT: Exit Sub Form_Current_Error: Select Case Err Case Else 'Call fcnLogError(Err.Number, Err.Description, " in Form_Current of VBA Document Form_frmAttendees", , True) End Select Resume Form_Current_EXIT End Sub
    Code:
    Option Compare Database Option Explicit Public Sub subCommon_Form_Current(frm As Form) Dim rst As DAO.Recordset Dim nCount As Integer, nPosition As Integer nCount = frm.Recordset.RecordCount On Error GoTo Err_Handler If nCount = 0 Then MsgBox "No Records exist yet.", vbOKOnly, " R E S T R I C T I O N " Exit Sub End If Set rst = frm.RecordsetClone rst.MoveLast rst.MoveFirst nCount = rst.RecordCount nPosition = frm.CurrentRecord frm!TxtRecordNo = nPosition & " of " & nCount frm.CmdFirst.Enabled = True frm.CmdPrev.Enabled = True frm.CmdNext.Enabled = True frm.CmdLast.Enabled = True 'disable as appropriate If nCount = 1 Then frm.CmdFirst.Enabled = False frm.CmdPrev.Enabled = False frm.CmdNext.Enabled = False frm.CmdLast.Enabled = False ElseIf nPosition = 1 Then frm.CmdFirst.Enabled = False frm.CmdPrev.Enabled = False ElseIf nPosition = nCount Then frm.CmdLast.Enabled = False frm.CmdNext.Enabled = False End If Exit_Handler: If frm.Dirty Then frm.Dirty = False Set rst = Nothing Exit Sub Err_Handler: Select Case Err Case Else MsgBox "Error " & Err & ": " & Error$, vbExclamation, "Form_Current()" Resume Exit_Handler End Select End Sub

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    You're correct about the record count being the problem! The Stack Overflow error occurs when a variable/datatype is assigned a value that it is too small to handle.

    Integer variables can range from -32,768 to 32,767...and your RecordCount excedes this.

    Change your Dim statements from

    Dim nCount As Integer, nPosition As Integer

    to

    Dim nCount As Long, nPosition As Long

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks so much! Works perfectly!!!!

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK - what line do you get the error? step through the code if necessary.

    why have the code about no records? - if there aren't any, the current event won't fire

    Also don't see why you need to assign the recordsetclone to rst?

    Or why you need to movelast/first - that is only required when a recordset is newly populated to determine recordcount - and since this is the current event, it is already populated

    and not sure how a form can be dirty if this is the current event (exit handler)

    It seems to me the code is about setting the enabled property of your navigation buttons which should be as simple as



    Code:
    Option Compare Database
    Option Explicit
    
    Public Sub subCommon_Form_Current(frm As Form)
        On Error GoTo Err_Handler
       If frm.recordset.EOF then 'however if there are no records there is not a current event
            MsgBox "No Records exist yet.", vbOKOnly, "  R E S T R I C T I O N  "    
       Else
           frm.CmdFirst.Enabled = frm.currentrecord>1
            frm.CmdPrev.Enabled = frm.currentrecord>1
            frm.CmdNext.Enabled = frm.currentrecord<frm.recordset.recordcount
            frm.CmdLast.Enabled = frm.currentrecord<frm.recordset.recordcount
    
        End If
    
    Exit_Handler:    
        Exit Sub
    Err_Handler:    
        Select Case Err
            Case Else
                MsgBox "Error " & Err & ": " & Error$, vbExclamation, "Form_Current()"
                Resume Exit_Handler
        End Select
    End Sub
    if you are using linked tables then recordcount might return an incorrect value. a faster way to determine the the recordcount would be a simpler rst along these lines. Note the use of static - this is to prevent the recordset being run multiple times - although if your recordset is constantly changing you would need to check if that has changed as well


    Code:
    static rst as dao.database
    
    if rst is nothing then 'populate rst
        set rst=currentdb.openrecordset("SELECT Count(*) FROM (" & frm.recordsource & ")")
    end if 'do not set to nothing on exit
    then these two lines become
    frm.CmdNext.Enabled = frm.currentrecord<rst.fields(0)
    frm.CmdLast.Enabled = frm.currentrecord<rst.fields(0)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error: overflow.
    By Homegrownandy in forum Programming
    Replies: 6
    Last Post: 05-02-2019, 02:47 AM
  2. Overflow error
    By Thistle in forum Queries
    Replies: 6
    Last Post: 12-12-2015, 02:57 PM
  3. overflow error
    By emir in forum Access
    Replies: 5
    Last Post: 11-23-2015, 07:47 AM
  4. Error 28 Out of Stack Space
    By loulou in forum Programming
    Replies: 1
    Last Post: 03-30-2012, 10:59 AM
  5. Error 28: Out of Stack Space??
    By mugsmugs in forum Access
    Replies: 1
    Last Post: 02-22-2009, 09:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums