Printing/Viewing two different report conditionally

[Tupacmoche]

Hi Report Masters,

I have a print button that works just fine when selected. It uses a case statement to select one of many reports that are appropriate. But, in some instances, I also want to print a second report that meets specific criteria like this person gives a gift for a million dollars. What is the best way to implement this? The form that collects the data has a check box flag that asks if the gift is a million or more that would indicate that the second report should be printed. Any ideas?

 

[MarkK]

Why do you need a check-box flag during data input to classify the value of the gift? Isn't the value of the gift part of the data?
???
Mark

 

[pbaldy]

If the form already has the checkbox, I'd just add code that tests the checkbox and prints the second report. That could be separate from or within the case statements, as appropriate.

 

[Tupacmoche]

While the value of the gift is part of the data there are many such flags and this is just one of them. So, while, I can just say something like GiftAmt >= 1,000,000, I just use the flag. Here is the case statement that selects the report to use for the current row:

Case "Pledge"
DoCmd.OpenReport "Pledge_Transmittal_Rpt", acViewPreview, , "DonorAdvanceID ='" & Me.DonorAdvanceID & "'"

Case "Pledge Payment"
DoCmd.OpenReport "Gift_Transmittal_Rpt", acViewPreview, , "DonorAdvanceID ='" & Me.DonorAdvanceID & "'"

How do, I add the second report when it meets the 1M dollar gift amount? Could the second report be made into a second page of the reports run above but will only print when the 1M flag is on?

 

[pbaldy]

If it prints no matter what the Case is, I'd do it separately. If it's only printed for certain cases, I'd add to the appropriate cases. The only way I can think of to add it to the existing report would be as a subreport, and you'd toggle its visibility based on the checkbox or amount. That would mean it would have to be added to all the reports though.

 

[Tupacmoche]

Hi All,

I took a completely different approach that worked. I added a page break and then a subreport. Then, I added logic to the on Format section to suppress the report if it was not one of our super donors. Works nice. But, a new issue has come out of it. Logically, the page header and footer will print along with this subreport on the next page so, I added this logic to suppress it.

If Me.SPH_MEDTrustee.Value = 1 Then

Forms!Report_Pledge_Transmittal_Rpt.Section(acPageHeader).Visible = False
Forms!Report_Pledge_Transmittal_Rpt.Section(acPageFooter).Visible = False
Else
End If

But, it is not suppressing either the header or footer can anyone see why? Additionally, I would not want it to be suppressed on the first page so is it possible to add additional logic like and "page number > 1" so that once it is working the headers and footers of the first page remain.:banghead:

 

[pbaldy]

That sounds like what I suggested, but I'd set the visibility of the subreport control, not the section. Maybe the page break too if it still shows a blank second page.

 

[Tupacmoche]

I did not set the visibility of the section but of the page break and subreport which was in fact your suggestion. But, I also changed the viability of the sections, page header and footer since they don't belong on the sub report. I used the code, I mentioned before but its not working. Do you see anything wrong with it? The strange thing is that the same condition SPH_MEDTrustee = 1 does suppress the sub report but when used as below it does not hide the section. Any suggestions? Also, I don't want to suppress it on the first page of the report and I add something like "and page > 1"?

If Me.SPH_MEDTrustee.Value = 1 Then
Forms!Report_Pledge_Transmittal_Rpt.Section(acPageHeader).Visible = False
Forms!Report_Pledge_Transmittal_Rpt.Section(acPageFooter).Visible = False
Else
End If

 

[pbaldy]

I'd try the format event of those sections. You can test for the page being >0.

 

[Tupacmoche]

Yes, that's were the code is in the on Format event of both the header and footer. See code but not working

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)

If Me.SPH_MEDTrustee.Value = 1 Then

Forms!Report_Pledge_Transmittal_Rpt.Section(acPageHeader).Visible = False
Forms!Report_Pledge_Transmittal_Rpt.Section(acPageFooter).Visible = False
Else

End If

 

[pbaldy]

Can you attach the db here?

 

[Tupacmoche]

I can but you won't have access to data in the tables since they are all link sql. Will that work?

 

[pbaldy]

I was hoping to be able to test the report, so that would need at least a local table with some junk data.