Count the record source of an open Report

[jeran042]

I have a function that will output a report to a specified folder, and what I am trying to do is count the number of records before the report is exported, so if there are 0, I do not want to oputput anything.

Seems easy enough, and I thought I was accounting for this in my code. The problem is that the count of the recordsource is counting the total number of records in the query (which is returning the correct number of 18), and not the count of records in the report.

Here is what I have for code:

Private Sub btnPDF_Click()
'PDF the Cases Report for the department Selected

'Error handling
On Error GoTo Error_Handler

Dim strFormName         As String
Dim myPath              As String
Dim iDepartment         As Integer
Dim sReportName         As String
Dim sDepartment         As String
Dim sRecordSource     As String

'Set references
myPath = "Y:\Budget process information\BUDGET DEPARTMENTS\3. CASES\"
iDepartment = Me.cboDEPARTMENT.Column(0)
sDepartment = Me.cboDEPARTMENT.Column(1)
strFormName = sDepartment & "_" & "Case Report" & Format(Date, "_mmddyy") & ".pdf"
sReportName = "RPT: CASES"


'Open the Cases Report, then count the number of cases
DoCmd.OpenReport sReportName, acViewReport, , "[COST_CENTER] = " & iDepartment
sRecordSource = Reports(sReportName).RecordSource

    
'Check if there any record to output
If DCount("*", sRecordSource) > 0 Then
    DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, myPath + strFormName, True
    DoCmd.Close acReport, sReportName, acSaveNo
    MsgBox " Your Report Has Successfully Run " & _
           vbCrLf & " You can find it at: " & myPath
Else
    DoCmd.Close acReport, sReportName, acSaveNo
    MsgBox "There are no Cases associated with: " & vbNewLine _
            & sDepartment, vbInformation, "NO CASES"
End If


Error_Handler_Exit:
    Exit Sub

Error_Handler:
    Select Case Err.Number
        Case 2501
            Err.Clear
            Resume Error_Handler_Exit
        Case Else
            MsgBox "Error No. " & Err.Number & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
            Err.Clear
            Resume Error_Handler_Exit
    End Select

End Sub

What am I missing?
I even changed:

If DCount("*", sRecordSource) > 0 Then

to

If DCount("*", me.RecordSource) > 0 Then

With no luck

 

[MarkK]

You are filtering the report when you open it...

DoCmd.OpenReport sReportName, acViewReport, , [COLOR="Blue"]"[COST_CENTER] = " & iDepartment[/COLOR]

...so you need to filter the DCount() too...

If DCount("*", sRecordSource, [COLOR="blue"]"[COST_CENTER] = " & iDepartment[/COLOR]) > 0 Then

hth
Mark

 

[jeran042]

Spot on, that worked perfectly.
Your the man!

 

[arnelgp]

You can also use simply:

If reports(sReportName).HasData then
' returns true output the report

Else 'False, no data
End if

 

[jeran042]

But my problem was that it was always going to show, because it would be reading the total number of records in the unfiltered query.

Adding the filter to the IF statement sorted that out.

However, I was unfamiliar with the .HasData Property before this post, and I'm sure that I will find a use for it int he future,

Thank you for your help,

 

[arnelgp]

No, no no. It will read the filtered report.
Hasdata means if it returns True your report is not blank ir had data.
If HasData is false, bkank report.
You yourself can test it in immediate window in vba.

 

[jeran042]

My apologies, I stand corrected,
I tried this and it in fact worked,

Private Sub Command23_Click()



Dim strFormName         As String
Dim myPath              As String
Dim iDepartment         As Long
Dim sReportName         As String
Dim sDepartment         As String
Dim sRecordSource       As String

'Set references
myPath = "Y:\Budget process information\BUDGET DEPARTMENTS\3. CASES\"
iDepartment = Me.cboDEPARTMENT.Column(0)
sDepartment = Me.cboDEPARTMENT.Column(1)
strFormName = sDepartment & "_" & "Case Report" & Format(Date, "_mmddyy") & ".pdf"
sReportName = "RPT: CASES"


'Open the Cases Report, then count the number of cases
DoCmd.OpenReport sReportName, acViewReport, , "[COST_CENTER] = " & iDepartment, acHidden


If Reports(sReportName).HasData Then
DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, myPath + strFormName, True
    DoCmd.Close acReport, sReportName, acSaveNo
    MsgBox " Your Report Has Successfully Run " & _
           vbCrLf & " You can find it at: " & myPath, vbInformation, "SUCCESS!"
Else
    DoCmd.Close acReport, sReportName, acSaveNo
    MsgBox "There are no Cases associated with: " & vbNewLine _
            & sDepartment, vbInformation, "NO CASES"
End If
            
End Sub

Very good suggestion,
This form is very helpful to its members,

Thank you again!!