Run-time error 6 Overflow 2

[vba317]

I am getting this runtime error I don't know why. I have used the same program for other clients with no problem. Any help is appreciated.
Tom

The code that is highlighted in blue is where the error is.
When I queried strSQL I got:
SELECT PatName,AcctNu,dos,chgpostdate,priminsmne,priminsdesc,cptdisplay,cptdsc,mod1,diag1,grpdsc1,grpdsc2,chgamt FROM PROC_RptSrc_ChgDetail ORDER BY dos,PatName,cptcode;

When I queried irec it is 0
When I queried rstDat.RecordCount it is 46440
iRw = 6

Public Sub RS_ChgDetail(liCl As Long, liRPd As Long, strTitl As String, strSubTitl As String, li1stGrp As Long, li2ndGrp As Long)

' *********************************
' *** CHARGE DETAIL - FLAT FILE ***
' *********************************
Dim strSQL As String
Dim rstDat As Recordset
Dim iRec As Integer
Dim iRw As Integer

' Set Title, SubTitle, Tab names
With goXL.ActiveSheet
    .Cells(1, 1).Value = (GetUCI(liCl)) & " - " & (GetClntName(liCl))
    .Cells(2, 1).Value = (strTitl) & "  (" & (strSubTitl) & ")"
    .Cells(3, 1).Value = "For the Month of: " & (GetFullMonthName(liRPd))
    ' Column Titles
    .Cells(5, 11).Value = (GetSubName(li1stGrp))
    .Cells(5, 12).Value = (GetSubName(li2ndGrp))
End With
iRw = 6
' Get Charge Data
strSQL = "SELECT PatName,AcctNu,dos,chgpostdate,priminsmne,priminsdesc,cptdisplay,cptdsc,mod1,diag1" & _
                ",grpdsc1,grpdsc2,chgamt " & _
            "FROM PROC_RptSrc_ChgDetail " & _
            "ORDER BY dos,PatName,cptcode;"
            '"ORDER BY dos,grpdsc1,grpdsc2,PatName,priminsmne;" ' Changed sort order to DOS first for HMF and MSP not sorting correctly
            '"ORDER BY grpdsc1,grpdsc2,dos,PatName,priminsmne;" 'Orig code changed 4/8/2013 per request by Denissa
Set rstDat = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If Not rstDat.EOF Then
    With rstDat
        .MoveLast
        .MoveFirst
    End With
[Blue]    For iRec = 1 To rstDat.RecordCount [/Blue]
        With goXL.ActiveSheet
            .Cells(iRw, 1).Value = (rstDat![PatName])
            .Cells(iRw, 2).Value = (rstDat![AcctNu])
            .Cells(iRw, 3).Value = (rstDat![dos])
            .Cells(iRw, 4).Value = (rstDat![chgpostdate])
            .Cells(iRw, 5).Value = (rstDat![priminsmne])
            .Cells(iRw, 6).Value = (rstDat![priminsdesc])
            .Cells(iRw, 7).Value = (rstDat![cptdisplay])
            .Cells(iRw, 8).Value = (rstDat![cptdsc])
            .Cells(iRw, 9).Value = (rstDat![mod1])
            .Cells(iRw, 10).Value = (rstDat![diag1])
            .Cells(iRw, 11).Value = (rstDat![grpdsc1])
            .Cells(iRw, 12).Value = (rstDat![grpdsc2])
            .Cells(iRw, 13).Value = (rstDat![chgamt])
        End With
        iRw = iRw + 1
        rstDat.MoveNext
    Next iRec
End If
rstDat.Close
Set rstDat = Nothing
'Hide Detail if not selected
If (li2ndGrp = 1) Then
    goXL.Columns("L:L").Hidden = True
End If
If (li1stGrp = 1) Then
    goXL.Columns("K:K").Hidden = True
End If
' Delete Extra Rows
With goXL
    .Rows("" & (iRw) & ":50000").Select
    .Selection.Delete Shift:=xlUp
    .Cells(4, 1).Select
End With

End Sub

 

[TheAceMan1]

How are ya vba317 . . .

First thing Id try:

[blue][b]change:[/b]
   Dim rstDat As Recordset
   Set rstDat = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

[b]to:[/b]
   Dim db As DAO.Database 'added line. [green][b]DAO.Database is key here![/b][/green]
   Dim rstDat As [purple][b]DAO.[/b][/purple]Recordset [green]'added DAO.[/green]
   
   Set db = Currentdb [green]'added line[/green]
   Set rstDat = [purple][b]db[/b][/purple].OpenRecordset(strSQL, dbOpenSnapshot)[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

vba317 . . .

As far as I know you can't move backwards in a Snapshot

[blue]    With rstDat
        .MoveLast
        .[purple][b]MoveFirst[/b][/purple]
    End With[/blue]

So ...

[blue][b]Change:[/b]
   Set rstDat = db.OpenRecordset(strSQL, dbOpenSnapshot)
[b]To:[/b]
   Set rstDat = db.OpenRecordset(strSQL, [purple][b]dbOpenDynaset[/b][/purple])[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[strongm]

You have defined iRec as an Integer. Which means the maximum positive number it can hold is 32767

 

[TheAceMan1]

strongm . . .

Good Lookin! ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]