Report totals don't match

[RayMilhon]

I have an Access 2010 Report when the report is previewed to screen all the numbers check out perfectly. When the report is printed the Report Totals are doubled what is on shown on the screen. The detail is fine. The totals are totaled using variables in VBA and displayed in the Report footer using various calculations Total Authorizations and Total Member months are the 2 totals being accumulated and then various calculations using these 2 variables are displayed (% of authorizations over member months, per thousand annualized calculations etc.) the Total Authorizations and Total member months are doubled from the screen report to the printed report. There is no VBA in the ON print Section of the report. Anybody have a clue?

[June7]

You are viewing on screen in PrintPreview?

No ideas. Want to provide db for analysis?

[RayMilhon]

Wish I could but most of the data is in SQL Server and HIPPA Prevents me from providing the data.

I printed the report directly to the printer then used PrintPreview to look at the report and then printed it. In comparing the 2 printed Reports All of the detail matches. The difference is only those 2 fields that are totaled in the On format event of the detail section. Here's the code behind the report.

Option Compare Database
Option Explicit
Private lngsRaUTHS As Long
Private lngSRAppAuths As Long
Private lngSRDenAuths As Long
Private lngCommercialAuths As Long
Private lngCommAppAuths As Long
Private lngCommDenAuths As Long
Private lngSrmbrship As Long
Private lngCommmbrship As Long
Private lngGTSRauths As Long
Private lngGTSrappauths As Long
Private lngGTSrdenauths As Long
Private lngGTcommappauths As Long
Private lngGTcommdenauths As Long
Private lngGTcommercialauths As Long
Private lngGTsrmbrship As Long
Private lngGTcommmbrship As Long
Private tpdays As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Select Case Me.PRODLINE
        Case Is = "SENIOR"
                Select Case Me.CODE
                    Case Is = 1
                        'to calculate the Senior Membership for this provider
                        lngSrmbrship = lngSrmbrship + Me.MBRS
                        'to calculate the Senior Membership for the group
                        lngGTsrmbrship = lngGTsrmbrship + Me.MBRS
                        lngSRAppAuths = lngSRAppAuths + Me.AUTHS
                        lngGTSRauths = lngGTSRauths + Me.AUTHS
                    Case Is = 3
                        lngSRDenAuths = lngSRDenAuths + Me.AUTHS
                        lngGTSrdenauths = lngGTSrdenauths + Me.AUTHS
                End Select
        Case Is = "COMMERCIAL", Is = "COMM-POS"
                lngCommercialAuths = lngCommercialAuths + Me.AUTHS
                Select Case Me.CODE
                    Case Is = 1
                        'To calculate the Commercial membership for the provider
                        lngCommmbrship = lngCommmbrship + Me.MBRS
                        'To calculate the Commercial membership for the group
                        lngGTcommmbrship = lngGTcommmbrship + Me.MBRS
                        'To calculate the Commercial Approved Authorizations for the provider
                        lngCommAppAuths = lngCommAppAuths + Me.AUTHS
                        'To calculate the commercial Approved authorizations for the group
                        lngGTcommappauths = lngGTcommappauths + Me.AUTHS
                    Case Is = 3
                        'To calcluate the Denied Authorizations for the provider
                        lngCommDenAuths = lngCommDenAuths + Me.AUTHS
                        'to Calculate the Denied Authorizations for the group
                        lngGTcommdenauths = lngGTcommdenauths + Me.AUTHS
                End Select
                'To calculate the Total Authorizations approved or deined for the group
                lngGTcommercialauths = lngGTcommercialauths + Me.AUTHS
    End Select
End Sub


Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
   'the following 6 lines of code set the text box in the report to the appropriate variable
    Me.txtsRaUTHS = lngSRAppAuths
    Me.TXTDENAUTHS = lngSRDenAuths
    Me.txtcommauths = lngCommAppAuths
    Me.TXTCOMMDENAUTHS = lngCommDenAuths
    Me.txtsrmbrship = lngSrmbrship
    Me.txtcommmbrship = lngCommmbrship
    'there are 2 divisions in the calculations for 2 of the text boxes in the report one of those tpdays will always have a number > 0 however
    'the mbrship could be 0 if that provider has no members for that product line.  for example a pediatrician will not have any assigned
    'seniors  the if statement prevents divide by 0 errors
    If lngSrmbrship = 0 Then
        Me.txtsrper1000 = 0
        Me.txtsrauthpermbr = 0
    Else
        Me.txtsrper1000 = ((lngSRAppAuths + lngSRDenAuths) / lngSrmbrship) * (365 / tpdays) * 1000
        Me.txtsrauthpermbr = ((lngSRAppAuths + lngSRDenAuths) / lngSrmbrship) * (365 / tpdays)
    End If
    'see comment above however this is for providers that may only have seniors and 0 commercial members
    If lngCommmbrship = 0 Then
        Me.txtcommper1000 = 0
        Me.txtcommauthpermbr = 0
    Else
        Me.txtcommper1000 = ((lngCommAppAuths + lngCommDenAuths) / lngCommmbrship) * (365 / tpdays) * 1000
        Me.txtcommauthpermbr = ((lngCommAppAuths + lngCommDenAuths) / lngCommmbrship) * (365 / tpdays)
    End If
    'combines the senior approved auths with commercial approved auths to get a total of approved auths
    Me.txttotauths = lngSRAppAuths + lngCommAppAuths
    'combines the senior denied auths with the commercial denied auths to get the total of denied auths
    Me.TXTTOTDENAUTHS = lngSRDenAuths + lngCommDenAuths
    'combines senior membership and commercial membership to get a total of membership
    Me.txttotmbrs = lngSrmbrship + lngCommmbrship
    'this should never be 0 however I took the precaution of including the if statement so the report will run however If the
    'total on the report shows as 0 I know I made a mistake on the parameters probably the date range and I need to recheck
    If Me.txttotmbrs = 0 Then
        Me.txttotauthsper1000 = 0
        Me.txttotauthspermbr = 0
    Else
        Me.txttotauthsper1000 = ((Me.txttotauths + Me.TXTTOTDENAUTHS) / Me.txttotmbrs) * (365 / tpdays) * 1000
        Me.txttotauthspermbr = ((Me.txttotauths + Me.TXTTOTDENAUTHS) / Me.txttotmbrs) * (365 / tpdays)
    End If
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    'Reset all of the provider variables back to 0 for each provider
    lngCommmbrship = 0
    lngCommAppAuths = 0
    lngCommDenAuths = 0
    lngSRAppAuths = 0
    lngSRDenAuths = 0
    lngSRAppAuths = 0
    lngSrmbrship = 0
    lngsRaUTHS = 0
    lngCommercialAuths = 0
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    'Sets the data in the Report Totals line
    Me.txtGTSrAuths = lngGTSRauths
    Me.txtGTsrmbrs = lngGTsrmbrship
    Me.txtsrdenauths = lngGTSrdenauths
    Me.txtgtsrauthsper1000 = (lngGTSRauths / lngGTsrmbrship) * (365 / tpdays) * 1000
    Me.txtgtsrauthspermember = (lngGTSRauths / lngGTsrmbrship) * (365 / tpdays)
    Me.txtGTcommauths = lngGTcommercialauths
    Me.TXTGTCOMMDENAUTHS = lngGTcommdenauths
    Me.txtGTCommmbrs = lngGTcommmbrship
    Me.txtgtcommauthsper1000 = (lngGTcommercialauths / lngGTcommmbrship) * (365 / tpdays) * 1000
    Me.txtgtcommauthspermbr = (lngGTcommercialauths / lngGTcommmbrship) * (365 / tpdays)
    Me.txtgtauths = lngGTSRauths + lngGTcommercialauths
    Me.txtgtmbrs = lngGTsrmbrship + lngGTcommmbrship
    Me.txtgtautsper1000 = (Me.txtgtauths / Me.txtgtmbrs) * (365 / tpdays) * 1000
    Me.txtGTtotauthspermbrs = (Me.txtgtauths / Me.txtgtmbrs) * (365 / tpdays)
    
    
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
        'sets the variable tpdays to the number of days between the start date parameter and the end date parameter.
        'Normally this is run for the preceding quarter of when the report is run but it could potentially be for a year or
        'half a year
        tpdays = DateDiff("d", Forms!form1!txtstart, Forms!form1!txtend) + 1
End Sub

[RayMilhon]

Sorry wasn't clear enough the variables with GT in them are the ones doubling up. the others work as expected.

[June7]

That's a lot of data calculation behind a report. Afraid I can't help without analyzing db. You could copy frontend and import relevant tables (without confidential info).

I can offer that I have some reports with graphs and a lot of code related to the graphs. I have found that when printing the report the Format procedure repeats more times than I would have expected in rendering the report. One report takes just over a minute to render with 7 graphs.

[RayMilhon]

Well, I have figured out a workaround If I print the report directly to the printer it prints as expected. I would've liked to keep giving them a PDF of the report but until I figure out why this is happening I will just have to give them the printed report. Thanks. Unfortunately I have a multitude of other projects to work on and I do have a workable solution so this project is done for the moment. If I get a chance to look at this again I'll open a new thread and take out the data and post the DB. In the meantime I'll mark this solved.

[June7]

What were you doing - opening report, saving as PDF, and then printing the open report to printer? The Format event runs when report opened in PrintPreview or direct to printer or SendObject/OutputTo methods. I am guessing when opened in PrintPreview, the Format events run then run again when the open report is printed.

You declare the variables in the module header so they are available to all procedures of the module. However, the non-GT variables are set to 0 in the Group Header Format event. These variable are not causing issue? The GT variables must still be in memory and holding values when the open report is printed. So what happens if you set the GT variables to 0, maybe in Report Header?

[RayMilhon]

That's a good point. in the Group Header I have to reset the group variables to 0 and clear them out which is why the group numbers are correct no matter what. Never thought about setting the report variables to 0. Thought the onformat event only ran when the report was being formatted. Never thought about it running a second time when formatting to print or send to PDF. I'll try that today and let you know.