If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report Filter
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 |
#2
|
|||
|
|||
Report Filter
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. "ACase" wrote: 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 |
#3
|
|||
|
|||
Report Filter
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 ! "'69 Camaro" wrote: 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. "ACase" wrote: 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 |
#4
|
|||
|
|||
Report Filter
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. 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. "ACase" wrote: 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 ! "'69 Camaro" wrote: 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. "ACase" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Form Filter / ability to open different report layouts | Brook | Using Forms | 0 | August 29th, 2005 02:07 PM |
Form/Query/Report problems | LT Larry | Using Forms | 18 | May 17th, 2005 07:25 PM |
Filter Report By Date | Edgar Chado via AccessMonster.com | Using Forms | 2 | May 4th, 2005 05:40 AM |
Printing Report on Recordset Using "Filter by Form" - Error Messag | Stepney_Clint | Setting Up & Running Reports | 0 | January 7th, 2005 08:17 PM |
Label | SRIT | General Discussion | 2 | June 22nd, 2004 09:42 PM |