if report query is empty, close report

[tuxalot]

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.OpenRepor t 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):

...
 
Dim stDocRS As String
 
    'store report name in variable
    stDocName = Me.lstReportName.Column(1)
    stDocRS = stDocName & "." & RecordSource
 
    'check print options
    Select Case Me.grpPrintOptions.Value
 
        Case 2        'print preview
            If DCount("*", stDocRS) = 0 Then
                MsgBox "No records match the current criteria"
                Exit Sub
            Else
                DoCmd.OpenReport stDocName, acViewPreview
...
 

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,

Tux

[ADezii]

@tuxalot
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:
   

   strReportName = "Invoice"

2. Open the Report in 'Hidden' Mode:
   

   DoCmd.OpenReport strReportName, acViewDesign, , , acHidden

3. Retrieve the RecordSource for the Report:
   

   strRecordSource = Reports(strReportName).RecordSource

4. Use DCount() to see if any Records exist in the RecordSource, then take the appropriate action:
   

   If DCount("*", strRecordSource) > 0 Then
     DoCmd.OpenReport strReportName, acViewPreview
   Else
     DoCmd.Close acReport, strReportName, acSaveNo
   End If

5. All together now:

Dim strReportName As String
Dim strRecordSource As String
 
strReportName = "Invoice"
 
DoCmd.OpenReport strReportName, acViewDesign, , , acHidden
 
strRecordSource = Reports(strReportName).RecordSource
 
If DCount("*", strRecordSource) > 0 Then
  DoCmd.OpenReport strReportName, acViewPreview
Else
  DoCmd.Close acReport, strReportName, acSaveNo
End If

[/list]

[tuxalot]

fantastic. Thanks again ADezii.

[ADezii]

@tuxalot
You are quite welcome, tuxalot.

[NeoPa]

@tuxalot
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.

[tuxalot]

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.

Tux

[NeoPa]

I see you're also trying to post answers where you can Tux, so you're especially welcome :)

[bikermo]

fantastic code, worked like a charm for me. thank you very much!

[mcupito]

On No Data

Cancel = True

[NeoPa]

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 :

On Error Resume Next
Call DoCmd.OpenReport(ReportName:="ReportName", View:=acPreview)
On Error GoTo 0

Private Sub Report_NoData(Cancel As Integer)
    Dim strMsg As String
 
    Cancel = True
    strMsg = "There is no data available for the selected parameters"
    Call MsgBox(Prompt:=strMsg, _
                Buttons:=vbExclamation Or vbOKOnly, _
                TITLE:=Me.Name)
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.