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
jeran042
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:

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:
Code:
If DCount("*", sRecordSource) > 0 Then
to
Code:
If DCount("*", me.RecordSource) > 0 Then
With no luck

jeran042 is offline   Reply With Quote
 

Tags
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
General
Tables
Queries
Forms
Reports
Macros
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