Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

Thread Tools Rate Thread Display Modes
Prev Previous Post   Next Post Next
Old 04-27-2018, 06:10 AM   #1
Newly Registered User
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Count the record source of an open Report

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
    DoCmd.Close acReport, sReportName, acSaveNo
    MsgBox "There are no Cases associated with: " & vbNewLine _
            & sDepartment, vbInformation, "NO CASES"
End If

    Exit Sub

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

End Sub
What am I missing?
I even changed:
If DCount("*", sRecordSource) > 0 Then
If DCount("*", me.RecordSource) > 0 Then
With no luck

jeran042 is offline   Reply With Quote

dcount , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Open a report using a form's recordsetclone as the report data source Magster Modules & VBA 32 04-16-2016 03:40 AM
report record source and control source help scoob8254 Reports 5 11-14-2010 10:08 AM
Open Report and set the Record Source. spcepickle Forms 6 04-29-2008 12:34 PM
Can't modify/add records because of record source with COUNT function!!!! hortense76 Forms 13 06-27-2006 03:58 PM
select record source on open jguscs General 3 06-26-2003 06:28 AM

All times are GMT -8. The time now is 06:38 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World