Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Report stops accumulating values (Office 2000)

    I run the following event in an Access report:

    Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As Integer)
    ......lngCarCount = lngCarCount + 1

    ......If Val(txtLightWeightTons) = 0# Then
    ............lngZeroLightWeightCount = lngZeroLightWeightCount + 1
    ......End If

    ......dblLightWeightGT = dblLightWeightGT + txtLightWeightTons
    ......dblRecovTonsGT = dblRecovTonsGT + txtPartsTons
    ......dblScrapTonsGT = dblScrapTonsGT + txtScrapTons
    End Sub

    From the date range I am using, the report creates seven pages. Interestingly, the above group footer event appears to stop running after page 1 (even though all 7 pages are visible), resulting in incorrect GrandTotals on page 7.

    When I set up a watch and step through the group footer events for the first page, it returns to the report when it hits page 2. When I click the arrow to display page 2, and return to the code after that, I can continue watching the event (and vars) and it accumulates correctly. When I continue this method, I can eventually get the correct totals/averages for the report.

    Anybody know what causes this behaviour (to stop running this event after page 1) and how I can solve this problem?

    Much appreciated!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access Report stops accumulating values (Office 2000)

    I have also had problems in the past when trying to do Grand Totalling in code for a report. When the program enters the GroupFooter event (it may come through here more than once for the same condition) this is when you should be testing PrintCount = 1 (for the 1st time) and doing your totalling then.

    However I have also found that this does not seem to work either under some conditions whcih I could not fathom.

    Why are you manually totalling for a Grand Total, why not use =Sum in the grand total variables instead?

    HTH
    Pat

    NB. I would like an explanation of when the PrintCount =1,2, etc, and under what conditions that happens.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access Report stops accumulating values (Office 2000)

    Are your incorrect totals appearing when you print the report, or just when you preview it? A general rule of thumb is to try to do all your math in your query, then merely use functions such as Sum and Count in your report.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Report stops accumulating values (Office 2000)

    What is GroupFooter1 the footer for? Are lngCarCount and lngZeroLightWeightCount module level variables? If not, they're going to be reset when the group value changes.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Report stops accumulating values (Office 2000)

    Hi Pat,

    You asked: Why are you manually totalling for a Grand Total, why not use =Sum in the grand total variables instead?

    I guess the truth had to come out some day! This is my very first report! I guess it shows <img src=/S/blush.gif border=0 alt=blush width=15 height=15>.So far, I only have been using queries for my own purposes (to sanitize the data), but now that it is clean, requests for reports come in. I am a success (and the guy who ask for this report is weird) <img src=/S/duck.gif border=0 alt=duck width=23 height=23> Just kiddin'.

    To answer your question: Originally, to get started I used the report wizard and selected summary report.
    Guess what. The totals were wrong. So, I went on a quest for correct totals and I discovered report events.

    The report looks like this:
    Car # KENM123456 (6 recs) Totals: (A) 40 Tons ([img]/forums/images/smilies/cool.gif[/img] 5 Tons 35 Tons

    (A) is the column header for the total weight of the railcar (80,000 lbs/2000), ([img]/forums/images/smilies/cool.gif[/img] the weight of the six parts that were recovered off the railcar (10000 lbs/2000) and is the remainder (scrap steel) (A-[img]/forums/images/smilies/cool.gif[/img].

    The above line is a group total for a single car and there are 18 or 19 such group totals on each page - no details showing - although the # of recs shows the # of hidden detail lines (recovered parts).

    In the underlying query, Access matches the weight of the car (80000 lbs) with each detail line. The car record and the part records connect on fldCarID. The result was that the =SUM of the tons displayed was ((6 x 80000)/2000) ie 240 tons. Definitely wrong.
    So, instead I used the =Max([fldLightWeight])/2000, which selects the weight only once - ie 40 tons.
    Each group is now correct, but the GrandTotal is still wrong because it too was based on the =SUM function. So, I hit on the idea to not use the =SUM in the GrandTotal, but to accumulate the values of the group totals while each was being printed (event).

    I used Public vars, because each time the event finishes, the local vars will go out of scope.

    Actually, it works perfectly, except for the fact that it doesn't accumulate past Page 1. Perhaps it does, but the results of Page 1 are the ones showing on the last page (7).

    Interestingly, when I run the report for a shorter period of time (a week instead of a month), the data only requires 1.5 pages, and the GT is just fine. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    You said: NB. I would like an explanation of when the PrintCount =1,2, etc, and under what conditions that happens.

    Under investigation!

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Report stops accumulating values (Office 2000)

    Hi Mark,

    See my response to Pat.
    Are your incorrect totals appearing when you print the report, or just when you preview it? Both.

    You said: ..to try to do all your math in your query,...
    That is what I tried. The data for the report is retrieved by a named query. Below the GT row, we also want a row showing the Average weight, average recovery weight and average scrap weight for a bunch of unique cars.

    What is was doing is to provide the count of every part record. I want a single count for a single car, hence a single count for each car group total. That's why I used the lngCarCount accumulator.

    We are populating the db with data some years old and not all pieces are in yet. There are still some cars that don't have a lightweight (the weight of an empty railcar) in the record. By including these zero LW records, the average per car (on the last page) would get skewed, so I decided to also count the cars with zero LW and substract them from the total car count for purposes of calculating the overall average per car.

    But maybe, just maybe, there is a much better way of doing this. But my Access books show me in great detail how to place controls on the report (which I already know how to do) but there is not a single mention on how to make correct totals from complex queries. <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    Any help is greatly appreciated! <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Report stops accumulating values (Office 2000)

    Hi Charlotte,

    See also my reponses to Pat and Mark. (Sorry for being lengthy, but how can one explain complex issues in two words?)

    GroupFooter1 is the footer for a single railcar record and several recovered parts records.

    You asked: Are lngCarCount and lngZeroLightWeightCount module level variables?

    Yes.

    Public dblLightWeightTonsGT As Double
    Public dblRecovTonsGT As Double
    Public dblScrapTonsGT As Double
    Public lngCarCount As Long
    Public lngZeroLightWeightCount As Long

    They are initialized with this event (which I think and hope occurs only once):

    Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
    dblLightWeightGT = 0
    dblRecovTonsGT = 0
    dblScrapTonsGT = 0
    lngCarCount = 0
    lngZeroLightWeightCount = 0
    End Sub

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Report stops accumulating values (Office 2000)

    Hi Pat,

    NB. I would like an explanation of when the PrintCount =1,2, etc, and under what conditions that happens

    A section's printcount apparently changes when Access executes the Print event for that section. If it prints the section more than once (when a section spans more than one page) it increments the PrintCount property to 2.

    So, any accumulating should be done when PrintCount is 1, otherwise you get duplicated values.

    (see Access Dev Handbook - page 686)

Posting Permissions

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