467,868 Members | 1,315 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,868 developers. It's quick & easy.

if report query is empty, close report

The easy way is to put cancel = True in the On No Data event of the report. But why replicate code across all reports that way? My app selects reports from a listbox, so I would prefer to check the record count of the query, and if 0, never get to the DoCmd.OpenReport line. Here's the code (case 2 is where I would like to check if the report query has data, and my DCount attempt is not working):
Expand|Select|Wrap|Line Numbers
  1. ...
  3. Dim stDocRS As String
  5.     'store report name in variable
  6.     stDocName = Me.lstReportName.Column(1)
  7.     stDocRS = stDocName & "." & RecordSource
  9.     'check print options
  10.     Select Case Me.grpPrintOptions.Value
  12.         Case 2        'print preview
  13.             If DCount("*", stDocRS) = 0 Then
  14.                 MsgBox "No records match the current criteria"
  15.                 Exit Sub
  16.             Else
  17.                 DoCmd.OpenReport stDocName, acViewPreview
  18. ...
The DCount is producing a 3078 error, and the text reads "...Access cannot find the input table or query '[correct report name is shown here].[but here shows one of the underlying tables that feed the query?]'...". This is strange since the report's Record Source is the query.

Thanks for looking,

Mar 28 '09 #1
  • viewed: 15304
11 Replies
Expert 8TB
I think you may require a little trickery to get the desired results:
  1. Define the desired Report in some manner, in your case a List Box:
    Expand|Select|Wrap|Line Numbers
    1. strReportName = "Invoice"
  2. Open the Report in 'Hidden' Mode:
    Expand|Select|Wrap|Line Numbers
    1. DoCmd.OpenReport strReportName, acViewDesign, , , acHidden
  3. Retrieve the RecordSource for the Report:
    Expand|Select|Wrap|Line Numbers
    1. strRecordSource = Reports(strReportName).RecordSource
  4. Use DCount() to see if any Records exist in the RecordSource, then take the appropriate action:
    Expand|Select|Wrap|Line Numbers
    1. If DCount("*", strRecordSource) > 0 Then
    2.   DoCmd.OpenReport strReportName, acViewPreview
    3. Else
    4.   DoCmd.Close acReport, strReportName, acSaveNo
    5. End If
  5. All together now:
Expand|Select|Wrap|Line Numbers
  1. Dim strReportName As String
  2. Dim strRecordSource As String
  4. strReportName = "Invoice"
  6. DoCmd.OpenReport strReportName, acViewDesign, , , acHidden
  8. strRecordSource = Reports(strReportName).RecordSource
  10. If DCount("*", strRecordSource) > 0 Then
  11.   DoCmd.OpenReport strReportName, acViewPreview
  12. Else
  13.   DoCmd.Close acReport, strReportName, acSaveNo
  14. End If
Mar 29 '09 #2
fantastic. Thanks again ADezii.
Mar 29 '09 #3
Expert 8TB
You are quite welcome, tuxalot.
Mar 29 '09 #4
Expert Mod 16PB
Tux, can I try to convince you that, while this is a working method, it is not a good idea.

I hear what you're saying about repeating the code for every report, but consider the effect of testing the data source before running the query every time. This may be negligible for extremely quick reports, but I'm sure you will have reports that take much longer to run (if not currently, then surely you will in future if you stay in the field). What you are considering is effectively to run the queries twice for every time the report is run.

Is this really what you want as your standard approach when running reports? Is the alternative really so annoying? Only you can decide for you, but I would certainly advise setting Cancel to False instead.
Apr 4 '09 #5
Thanks NeoPa for the advice. I understand how this approach could potentially cause issues. As they say there are many ways to skin the proverbial cat. Developers new to Access like me truly rely on folks like you and the other experts on this forum to help steer us in the right direction.

Thanks to you and ADezii for your insight and expertise.

Apr 4 '09 #6
Expert Mod 16PB
I see you're also trying to post answers where you can Tux, so you're especially welcome :)
Apr 4 '09 #7
fantastic code, worked like a charm for me. thank you very much!
Apr 10 '14 #8
On No Data
Expand|Select|Wrap|Line Numbers
  1. Cancel = True
Apr 10 '14 #9
Expert Mod 16PB
An important issue to bear in mind, when Cancel is set to True in the OnNoData event procedure, is that the code that calls for the report to open returns an error. This needs to be handled if you don't want the user to see it crash every time.

To be clear, the two bits of code would look something like :
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Call DoCmd.OpenReport(ReportName:="ReportName", View:=acPreview)
  3. On Error GoTo 0
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_NoData(Cancel As Integer)
  2.     Dim strMsg As String
  4.     Cancel = True
  5.     strMsg = "There is no data available for the selected parameters"
  6.     Call MsgBox(Prompt:=strMsg, _
  7.                 Buttons:=vbExclamation Or vbOKOnly, _
  8.                 TITLE:=Me.Name)
  9. End Sub
Notice there is also the possibility of notifying the operator if you choose to. Otherwise, only line #4 is required within the procedure wrapper.
Apr 10 '14 #10
I did not know that! Thanks, NeoPa.
Apr 11 '14 #11
Expert Mod 16PB
Always pleased to help further understanding Mark :-)
Apr 12 '14 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by z.ghulam | last post: by
2 posts views Thread by Mike Abbott | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.