Access Report stops accumulating values (Office 2000)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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 .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) 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 ( 5 Tons © 35 Tons

(A) is the column header for the total weight of the railcar (80,000 lbs/2000), ( the weight of the six parts that were recovered off the railcar (10000 lbs/2000) and © is the remainder (scrap steel) (A-.

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.

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

Under investigation!

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

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.

Any help is greatly appreciated!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote