Report Filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a form which enables a user to select various reports (Listed Below)
these reports are based upon a column, and therefore the reports are filtered
on this criteria. The User also has the option to run ALL reports. When the
selects the 'ALL' option (Case 1 below) every report is run with its specific
criteria.

The problem I run into is that I also have a 'On No Data' event. If one of
the reports contains no data, the code ends. No other reports are printed.

How can I preview only the reports that have data, when the user selects the
ALL button, and only return the 'NO DATA' error message if NO reports meet
this criteria.

Any help would be much appreciated.

Thanks

Select Case Me!ReportToPrint

Case 1
DoCmd.OpenReport "rpt_NewBusiness", PrintMode, , strWhere
DoCmd.OpenReport "rpt_ProfitabilityReview", PrintMode, ,
strWhere
DoCmd.OpenReport "rpt_RePrice", PrintMode, , strWhere
DoCmd.OpenReport "rpt_AdditionalMarkets", PrintMode, ,
strWhere
DoCmd.OpenReport "rpt_AdditionalProducts", PrintMode, ,
strWhere
 
In each of these reports, place the following code:

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
End Sub

In the form's module's procedure that opens the reports, try:

Dim cntr As Long

' Other code.

Select Case Me!ReportToPrint

Case 1
DoCmd.OpenReport "rpt_NewBusiness", PrintMode, , strWhere
DoCmd.OpenReport "rpt_ProfitabilityReview", PrintMode, , strWhere
DoCmd.OpenReport "rpt_RePrice", PrintMode, , strWhere
DoCmd.OpenReport "rpt_AdditionalMarkets", PrintMode, , strWhere
DoCmd.OpenReport "rpt_AdditionalProducts", PrintMode, , strWhere
Case 2

' Rest of code.

If (cntr > 0) Then
MsgBox cntr & " reports had no data."
End If

Exit Sub

ErrHandler:

If (Err.Number = 2501)
Err.Clear
cntr = cntr + 1 ' No data.
Resume Next
ElseIf (Err.Number <> 2501) Then ' Report not cancelled.
MsgBox "Error in OpenRpts( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
End If

Err.Clear

End Sub

If you really don't want a message unless all five reports don't print, then
change:

If (cntr > 0) Then

to:

If (cntr = 5) Then

But keep in mind that this requires more maintenance, because if the number
of reports ever changes, the developer must remember to change this current
maximum number, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Thanks for the help. Maybe I did not explain this correctly.

I have the following 'On No Data' event for each report.

Private Sub Report_NoData(Cancel As Integer)
Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "Your filter criteria has not returned any data. Please check
your criteria and try again."

intStyle = vbOKOnly
strTitle = "No Data returned"

MsgBox strMsg, intStyle, strTitle
Cancel = True
End Sub

I have a report form with 6 option buttons (ALL, New Business, New Products,
New Markets, Profitability Review, Reprice)

If a user selects ALL then, all 5 reports should preview. Or at least only
those reports that have data.

The reports are driven by a column called 'Request Type'. The underlying
data is based on Clients. So if we only have one client in the DB, and
his/her request type is 'New Business' then only one report should print when
selecting the ALL option.

Problem I encounter is the program ends, when the first report has no data.

The following Case Statement (Case = 1) indicates that the user has selected
the ALL option. Since the DB has only 1 client and his request type is
'Profitability Review', I only want the 1 report to preview.

Select Case Me!ReportToPrint

Case 1
DoCmd.OpenReport "rpt_NewBusiness", PrintMode, , strWhere
DoCmd.OpenReport "rpt_ProfitabilityReview", PrintMode, ,
strWhere
DoCmd.OpenReport "rpt_RePrice", PrintMode, , strWhere
DoCmd.OpenReport "rpt_AdditionalMarkets", PrintMode, ,
strWhere
DoCmd.OpenReport "rpt_AdditionalProducts", PrintMode, ,
strWhere

Case 2
DoCmd.OpenReport "rpt_NewBusiness", PrintMode, , strWhere
Case 3
DoCmd.OpenReport "rpt_ProfitabilityReview", PrintMode, ,
strWhere
Case 4
DoCmd.OpenReport "rpt_RePrice", PrintMode, , strWhere
Case 5
DoCmd.OpenReport "rpt_AdditionalMarkets", PrintMode, ,
strWhere
Case 6
DoCmd.OpenReport "rpt_AdditionalProducts", PrintMode, ,
strWhere

End Select

Thanks Again !
 
So if we only have one client in the DB, and
his/her request type is 'New Business' then only one report should print when
selecting the ALL option.
Click to expand...

Does the code figure this out, or are you relying on the OnNoData( ) event
of each report to skip the unnecessary reports, which is the easy way to do
this? If it's code you need to have the select statement figure out which
reports should be printed and which should not when the "ALL" option is
selected, then please let me know.

If you're relying on the OnNoData( ) event of each report, then humor me for
a few minutes. Open each of these reports' code module, and comment out the
line for the message box, so that it looks like this:

'MsgBox strMsg, intStyle, strTitle

Then save and compile the code. Next, create a new button on your form,
name it PrintBtn, and paste the following code in the form's module (watch
out for word wrap):

Private Sub PrintBtn_Click()

On Error GoTo ErrHandler

Dim cntr As Long

Const CANC As Long = 2501
Const MAXCNT As Long = 5

Select Case Me!ReportToPrint

Case 1
DoCmd.OpenReport "rpt_NewBusiness", PrintMode, , strWhere
DoCmd.OpenReport "rpt_ProfitabilityReview", PrintMode, ,
strWhere
DoCmd.OpenReport "rpt_RePrice", PrintMode, , strWhere
DoCmd.OpenReport "rpt_AdditionalMarkets", PrintMode, ,
strWhere
DoCmd.OpenReport "rpt_AdditionalProducts", PrintMode, ,
strWhere

Case 2
DoCmd.OpenReport "rpt_NewBusiness", PrintMode, , strWhere
Case 3
DoCmd.OpenReport "rpt_ProfitabilityReview", PrintMode, ,
strWhere
Case 4
DoCmd.OpenReport "rpt_RePrice", PrintMode, , strWhere
Case 5
DoCmd.OpenReport "rpt_AdditionalMarkets", PrintMode, ,
strWhere
Case 6
DoCmd.OpenReport "rpt_AdditionalProducts", PrintMode, ,
strWhere
Case Else
MsgBox "There is a logic error in the select case " & vbCrLf & _
"statement in PrintBtn_Click( ).", _
vbCritical + vbOKOnly, "Logic Error!"
End Select

If (cntr = MAXCNT) Then
MsgBox "None of the reports had any data.", _
vbInformation + vbOKOnly, "No Reports Printed"
End If

Exit Sub

ErrHandler:

If (Err.Number = CANC) Then ' Report cancelled.
Err.Clear
cntr = cntr + 1
Resume Next
Else
MsgBox "Error in PrintBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If

Err.Clear

End Sub ' PrintBtn_Click( )

Save the compile the code. Open the form in Form View and select the "ALL"
option where you know that some of the reports won't print. Press the new
PrintBtn button. Do the correct reports print, even when the previous
reports had no data?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Back
Top