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

 
Reply
 
Thread Tools Rate Thread Display Modes
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
Old 04-27-2018, 06:33 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,289 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
You are filtering the report when you open it...
Code:
DoCmd.OpenReport sReportName, acViewReport, , "[COST_CENTER] = " & iDepartment
...so you need to filter the DCount() too...
Code:
If DCount("*", sRecordSource, "[COST_CENTER] = " & iDepartment) > 0 Then
hth
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
jeran042 (04-27-2018)
Old 04-27-2018, 06:42 AM   #3
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Count the record source of an open Report

Spot on, that worked perfectly.
Your the man!


jeran042 is offline   Reply With Quote
Old 04-27-2018, 07:31 AM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,900
Thanks: 63
Thanked 2,509 Times in 2,409 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Count the record source of an open Report

You can also use simply:

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

Else 'False, no data
End if
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Gasman (04-27-2018)
Old 04-27-2018, 08:04 AM   #5
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Count the record source of an open Report

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,
jeran042 is offline   Reply With Quote
Old 04-27-2018, 08:11 AM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,900
Thanks: 63
Thanked 2,509 Times in 2,409 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Count the record source of an open Report

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
jeran042 (04-27-2018)
Old 04-27-2018, 09:08 AM   #7
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Count the record source of an open Report

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

Code:
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!!


jeran042 is offline   Reply With Quote
Reply

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 02:16 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