Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2007 VB Runtime Error 6

Posted on 2010-11-23
12
Medium Priority
?
793 Views
Last Modified: 2012-05-10
I have this code in a report. It worked perfectly last I used it about 6 months ago and now I'm getting a Run-time Error 6 (Overflow) and don't really know why. The report shows a list of the status of personnel packets from a department. The code is supposed to calculate a percentage of packets received as opposed to not received, and color-code a pie graph at the foot of the page, with a total between all departments at the report footer. The code now overflows at line 8 [NotRec = (...]  I have tried to change the variable types around to some extent, but I'm stull not sure why it worked a few months ago and now it doesn't. Any ideas. I have also included a view of what the report is supposed to look like. There were never any issues with a 100% status, either.

 
Option Compare Database
Option Explicit

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Dim chrt As Object
Dim strCat As String
Dim NotRec As Double
 NotRec = ((DCount("*", "PER FILTER E5", "KeyStatus='NotReceived'" & "AND UPC=" & "'" & [UPC] & "'")) / (DCount("*", "[PER FILTER E5]", "UPC=" & "'" & [UPC] & "'")))

Set chrt = Me.Graph11
    If NotRec = 1 Then
        'All red for NotReceived
        chrt.SeriesCollection(1).points(1).Interior.Color = 255
    Else
        chrt.SeriesCollection(1).points(1).Interior.Color = 32768
    End If

End Sub


Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim chrt As Object
Dim strCat As String

Set chrt = Me.Graph26
    If Me.Text32 = 1 Then
        'All red for NotReceived
        chrt.SeriesCollection(1).points(1).Interior.Color = 255
    Else
        chrt.SeriesCollection(1).points(1).Interior.Color = 32768
    End If

End Sub

Open in new window

PER-REPORT.pdf
0
Comment
Question by:USArmy1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
12 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 34197435
USArmy1,

The issue is most likely that the DCount statement is resulting in a zero (causing a floating point overflow when used in division).

Try changing:
DCount("*", "[PER FILTER E5]", "UPC=" & "'" & [UPC] & "'")

To:

iif(DCount("*", "[PER FILTER E5]", "UPC=" & "'" & [UPC] & "'") = 0, 1, DCount("*", "[PER FILTER E5]", "UPC=" & "'" & [UPC] & "'"))
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 34197474
Written a little more cleanly:

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Dim chrt As Object
Dim strCat As String
Dim NotRec As Double
dim lngCount as Long

LngCount = DCount("*", "[PER FILTER E5]", "UPC=" & "'" & [UPC] & "'")
if lngCount = 0 then lngCount = 1

 NotRec = ((DCount("*", "PER FILTER E5", "KeyStatus='NotReceived'" & "AND UPC=" & "'" & [UPC] & "'")) / lngCount))



Note that I am using 1 as a substitute for Zero.  You may need to alter that, but the point is that you can't divide by zero (BLUF)
0
 
LVL 1

Author Comment

by:USArmy1
ID: 34197611
Thanks mbizup! It's getting past the overflow now, but I'm getting a 1004 Error (Unable to get the SeriesCollection property of the chart class) at 15 (after the "Else").
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:USArmy1
ID: 34197637
Nevermind. I didn't see your second post. That seems to work! Thanks a million!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 34197707
Okay -

I'm trying to understand your data at this point.  What do you want to happen for cases where (DCount("*", "[PER FILTER E5]", "UPC=" & "'" & [UPC] & "'") = 0 ?


(ie: cases where there are NO records in in [PER FILTER E5] where UPC is the specified code)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 34197719
I'm a little slow today :)

Glad that worked out for you.
0
 
LVL 1

Author Comment

by:USArmy1
ID: 34197914
Hmmm. The report is functioning, but the graph is not producing the right numbers...

If I understand your question correctly (which I probably don't) a UPC should not show up that has no records. The primary key of the query is the person (SSN, actually) and each person is assigned to a UPC. The report is sorted with one UPC on each page...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 34198168
USArmy1,

Would you be able to post a sample copy of the database -- just the form, tables etc needed to reproduce this  -- so that I can get a better idea of what is happening?

Your data is clearly sensitive, so ensure that any sample you post is using junk data
0
 
LVL 61

Expert Comment

by:mbizup
ID: 34199090
<< If I understand your question correctly (which I probably don't) a UPC should not show up that has no records. >>

It sounds like you're understanding my point perfectly.  The issue from what I can tell is that the UPC on the report does not have any corresponding records in your [PER FILTER E5] table or query, and that caused the divide by zero (overflow) error.

General debugging tip - add a message box to let you know which UPC values are causing this problem:

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Dim chrt As Object
Dim strCat As String
Dim NotRec As Double
dim lngCount as Long

LngCount = DCount("*", "[PER FILTER E5]", "UPC=" & "'" & [UPC] & "'")
if lngCount = 0 then lngCount = 1
if lngcount = 0 then msgbox "UPC = " & [UPC]   '<--- add this

Open in new window


Does anything about that UPC or the data in your query or table seem unusual for that record?  (you are the judge of that)

If it the data looks okay, what do you want to have happen in your report?  "NotRec" will be zero for these cases.  

You have a special case in your code for NotRec = 1.

Do you need a special case for NotRec = 0 as well?

0
 
LVL 1

Author Comment

by:USArmy1
ID: 34199345
Thanks for the tip! I'll start doing that.

While creating the dummy database, I actually figured out what the second issue was. The code works fine, but the chart was querying the wrong dataset on the property sheet, so the colors were showing correctly per the code, but numbers were wrong. Good to go there. Now...the only issue is that the color coding isn't working. It works on the report footer (the overall percentage for all UPCs) but in the report, everything is red regardless.

...and now it seems the report footer is backwards. Odd...I think it was right in the original DB.
DUMMYperDB.accdb
0
 
LVL 61

Expert Comment

by:mbizup
ID: 34199418
Okay - I will check this out on my Access 2007 machine this evening (US East Coast)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 34202322
It didn't look like your code was running at all in the sample.

Try placing your code in the Group Footer Print event instead of the Format event.

This made both colors show up for me.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question