Double Calculations Problem

[acepayne]

Hi there,

I have created a report, where the detail section is hidden. I have written a bunch of code for the Detail_Format event, where some calculations are made based on the records returned from an existing query.

In the report footer, I have some text boxes that get filled with the resulting calcualtions.

Now, everything is just groovy when I preview the report, but when I go ahead and print it, everything gets doubled!

Can someone explain exactly what happens when you first preview a report, and then print the same report?

Oh, printing the report directly works fine. It's only after previewing first, followed by a print, that the doubling occurs.

Cheers!!

 

[KDg]

Hi,

I would like to know if there is a better solution to this but mine was to create blnIsCalcd as boolean, turn it to true on the first run thru and test for it, stepping over if it's true,

HTH

Drew

 

[Rich@ITTC]

Hi Acepayne

I think Drew's solution looks pretty neat. As he and you have discovered Access formats the detail section of a report (at least) once to preview it and (at least) once again when you send it to print.

There is something on this in the Help File (for Access 97):
You can use the FormatCount property to determine the number of times the OnFormat property has been evaluated for the current section on a report.

Setting

You can use this property only in a macro or an Visual Basic event procedure specified by a section's OnFormat property setting.
The FormatCount property setting has a Long data type value.
This property isn't available in report Design view and is read-only in other views.

Remarks

Microsoft Access increments the FormatCount property each time the OnFormat property setting is evaluated for the current section. As the next section is formatted, Microsoft Access resets the FormatCount property to 1.
Under some circumstances, Microsoft Access formats a section more than once. For example, you might design a report in which the KeepTogether property for the detail section is set to Yes. When Microsoft Access reaches the bottom of a page, it formats the current detail section once to see if it will fit. If it doesn't fit, Microsoft Access moves to the next page and formats the detail section again. In this case, the setting for the FormatCount property for the detail section is 2 because it was formatted twice before it was printed.

You can use the FormatCount property to ensure that an operation that affects formatting gets executed only once for a section. In the following example, the DLookup function is evaluated only when the FormatCount property is set to 1:

------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const conBold = 700
Const conNormal = 400
If FormatCount = 1 Then
If DLookup("CompanyName", _
"Customers", "CustomerID = Reports!" _
& "[Customer Labels]!CustomerID") _
Like "B*" Then
CompanyNameLine.FontWeight = conBold
Else
CompanyNameLine.FontWeight = conNormal
End If
End If
End Sub
--------------------------

HTH

Rich Gorvin

 

[acepayne]

Thanks for the awesome info guys. Sadly, my problem still exists, however.

Here is some more detail on what I have done:

Report header contains just a label, with the title for the report.

In the detail section, all of the calculations take place. To simplify, let's say each record contains 4 Yes/No fields. In the detail_format procedure I simply test each Yes/No field. If it is "Yes" then I add one to a module-level variable. Finally, at the end, I multiply these totals by some fixed numbers and add up the "grand total" of all of them.

I tried adding if FormatCount = 1
I also tried the PrintCount property, but the totals still get doubled when printed!
I did try the boolean variable method too with no luck. Actually, I set a break point at the top of the on_format procedure (for the detail section) and it goes through there the right number of times (once for each record). Upon printing, the breakpoint is not revisited.

The totals are placed into textboxes that are located in the report footer for the report. I have tried doing this in both the on_format procedure for the report footer and the detail section.

I sure appreciate your help with this! Just knowing a little more about what goes on when Access formats/prints the reports is great.

 

[Rich@ITTC]

Hi acepayne

Can't you do this calculation within the query behind the report? You can have a calculated field within a query based on the data available within each record. This is what I do, for example, with invoices that have discounts or percentage taxes to pay - the subtotals, net totals etc are calculated within the query for the reports etc.

HTH

Rich Gorvin

 

[acepayne]

Hi Rich,

I was unable to use a combination of queries I had created, as there was a conflict in using a particular table.

I have managed to get the report working now, though, by creating a temporary table, and putting the results from the various queries (they all either count or sum some records) in there. Once I had this temp table, the report was simple enough!

I'm thinking there is probably an easier way to do this, but I must move on to some other reports

Thanks again for your help!!

 

[norm]

acepayne,

I've been running into the same problem you are but think I've found a solution thanks in part to your discussion.

Decided to put debug statements for FormatCount and PrintCount in each Format event to see what's going on. It led me to believe that if i do all my calculations while PrintCount was still zero and FormatCount is One, I should be able to avoid double calculations. Neither one by itself was working.

So, here's some code for visual aid with the debug prints....

I'm still not completely sure of this so if you have any comments, they are welcomed.

Thanks for the help.
norm


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Debug.Print "GroupHeader0 FormatCount: " & FormatCount
Debug.Print "GroupHeader0 PrintCount: " & PrintCount

If txtAccessoriesPrice > 0 Then
lblAccessories.Visible = True
txtAccessoriesDescription.Visible = True
End If

If FormatCount = 1 And PrintCount = 0 Then
m_curSolPrice = m_curSolPrice + txtAccessoriesPrice
Debug.Print "m_curSolPrice + txtAccessories = " & m_curSolPrice
End If

End Sub


'GroupFooter3 = MachID
Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer)
Debug.Print "GroupFooter3 FormatCount: " & FormatCount
Debug.Print "GroupFooter3 PrintCount: " & PrintCount

If FormatCount = 1 And PrintCount = 0 Then
m_curSolPrice = m_curSolPrice + txtMachinesPrice
Debug.Print "m_curSolPrice + txtMachinesPrice = " & m_curSolPrice
End If

End Sub


'REPORT USING FormatCount = 1 and PrintCount = 0
GroupHeader0 FormatCount: 1
GroupHeader0 PrintCount: 0
m_curSolPrice + txtAccessories = 1000
GroupFooter3 FormatCount: 1
GroupFooter3 PrintCount: 0
m_curSolPrice + txtMachinesPrice = 2833000
GroupHeader0 FormatCount: 1
GroupHeader0 PrintCount: 1
GroupFooter3 FormatCount: 1
GroupFooter3 PrintCount: 1


'ADDITIONAL PRINTS:
GroupHeader0 FormatCount: 1
GroupHeader0 PrintCount: 1
GroupFooter3 FormatCount: 1
GroupFooter3 PrintCount: 1

 

[Rich]

Have you tried using the OnPrint event instead of the OnFormat ?

 

[norm]

Rich,

Yeah. I tried the print event first. It worked for the most part until i started literally printing multiple copies. Here's what i used.

--------------------------------------
Private Sub GroupFooter3_Print(Cancel As Integer, PrintCount As Integer)

Debug.Print "GroupHeader3 PrintCount: " & PrintCount
If PrintCount = 1 Then m_curSolPrice = m_curSolPrice + txtMachinesPrice

End Sub

Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)

Debug.Print "GroupHeader0 PrintCount: " & PrintCount
If PrintCount = 1 Then m_curSolPrice = m_curSolPrice + txtAccessoriesPrice

End Sub
--------------------------------------


I've read quite a bit on the subject but have to admit it's still confusing.

The above code gave debug.prints like this:

GroupHeader0 PrintCount: 1
GroupHeader3 PrintCount: 1
GroupHeader0 PrintCount: 1
GroupHeader3 PrintCount: 1
GroupHeader0 PrintCount: 1
GroupHeader3 PrintCount: 1

when i expected subsequent prints to be 2.

So, preview looked ok, first print to paper was doubled, second print to pdf was doubled also. Each test of the printcount in the print event returned one so it kept adding.

What's your take on this?

 

[Rich]

I'm not sure I fully understand what your actually calculating, but you just seem to be adding two fields, which doesn't need vba at all. Try to give us some more details

 

[norm]

After reading your last post, i sat here and thought twice....

Do i need the var? I'm pretty sure i do but i'll try to attach (think we can here) a pdf of it for you to take look at.

After evaluating production needs for each 'Machine' group, the report produces a price for the number of machines needed. That price has to be tallied as the report runs.

Unless there is some way to run a pretty complicated query to determine a sum at the end of the report I don't see any other way to do it besides by using a module level variable.

If i were to add using controls, all calculations would be correct as long as there was only one machine group being analyzed. When there are many, how do you sum them? I don't know of a way to sum many of the same control besides summing the underlying field.

In this report, the major numbers to look for to help explain are:
Accessories Price = 1000
Price of Machines Required (first type)= 2,832, 000
Price of Machines Required (second type) = 675, 000
Solution Est Price = 3,508,000

I don't see another way to reach 3,508,000 but i'm always happy to hear other ideas. I work alone!

take care,
norm

 

[norm]

Pdf (zip) attached in reference to my last reply....