×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Run-time error 6 Overflow
2

Run-time error 6 Overflow

Run-time error 6 Overflow

(OP)
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

CODE

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
    For iRec = 1 To rstDat.RecordCount 
        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 

RE: Run-time error 6 Overflow

How are ya vba317 . . .

First thing Id try:

CODE

change:
   Dim rstDat As Recordset
   Set rstDat = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

to:
   Dim db As DAO.Database 'added line. DAO.Database is key here!
   Dim rstDat As DAO.Recordset 'added DAO.
   
   Set db = Currentdb 'added line
   Set rstDat = db.OpenRecordset(strSQL, dbOpenSnapshot) 

See Ya! . . . . . .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading! thumbsup2

RE: Run-time error 6 Overflow

vba317 . . .

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

CODE

    With rstDat
        .MoveLast
        .MoveFirst
    End With 
So ...

CODE

Change:
   Set rstDat = db.OpenRecordset(strSQL, dbOpenSnapshot)
To:
   Set rstDat = db.OpenRecordset(strSQL, dbOpenDynaset) 

Your Thoughts? . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading! thumbsup2

RE: Run-time error 6 Overflow

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close