Running a list of reports from one procedure

I am trying to run a list of reports. Some reports have no data so should not open or print. How do I only open and print each report that has data.

I have used Has Data in each report to not open and show "No Data" message if selected indevidually but I cannot use this if I want the application to just ignor a report when printing automatically from a list(14 Reports)

I tried to count the records in the underlying query but I must have something wrong:

Architraves is the underlying query of one of the reports.

Private Sub Command17_Click()
If (IsNull([Architraves]![AccountCode])) Then
    MsgBox "No Reports data available"
Else
    DoCmd.OpenReport "Architraves"
End If
End Sub
Derek BrownMDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Try with:

Private Sub Command17_Click()

    If DCount("*", "[Architraves]") = 0 Then
        MsgBox "No Reports data available"
    Else
        DoCmd.OpenReport "Architraves"
    End If

End Sub

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You have two basic choices:

a. Check for the data outside of the report and don't open it.

b. in the report, check for data, but use some type of flag to tell the report if it should put up a message on no data, or be "silent" and just close.

  If your executing the reports from a form, the flag might be a hidden control on the form (say a check box with a true/false).   You might also pass a flag into the report using the openargs property of the report.

Jim.
0
Hamed NasrRetired IT ProfessionalCommented:
One way, you need to loop through reports, find record source for a report, check for records, and run qualified report.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Hamed NasrRetired IT ProfessionalCommented:
This is a try:
Tried for 2 reports a_2 and a_empty. a_empty has no record source, so it returns no records.
The reports are run sequentially. They can be automated from a table of reports.


The code is run from a button click event procedure in a form.
You may modify to do more processing on the respective qualified report
Option Compare Database

Private Sub Command0_Click()
    Dim reportName As String
    reportName = "a_2" ' one report
    subOpenReport (reportName) 'call sub to print report
    reportName = "a_empty" 'other report
    subOpenReport (reportName)
    
End Sub
Private Sub subOpenReport(rn As String)
On Error GoTo EH
    Dim strSource As String 'report source
    DoCmd.OpenReport rn, acViewPreview
    strSource = Reports(rn).RecordSource
    If strSource = "" Then
        DoCmd.Close acReport, rn, acSaveNo
        Exit Sub
    End If
    If DCount("*", strSource) = 0 Then
        DoCmd.Close acReport, rn, acSaveNo
    End If
    
Exit Sub
EH:
    
End Sub

Open in new window

0
Derek BrownMDAuthor Commented:
I am intrigued as to why we need to open report rather than just checking record source and why check strSource = 0 and strSource =""

Have I misunderstood?
0
Derek BrownMDAuthor Commented:
Hi Gustav

I tried this and database closes down. (maybe unrelated but can't see any other problem:


    If DCount("*", "[Architraves]") > 0 Then
        DoCmd.OpenReport "Architraves"
    End If

Is it OK to use > in this procedure?
0
Gustav BrockCIOCommented:
That should be OK.
On the other hand, the other variety of the code can in no way close the application, so you might be facing other - more serious - issues.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
If you know the record source then you don’t need to open record first.

To get the record source of a closed report requires more elaborate work, probably dealing with system tables.
0
Derek BrownMDAuthor Commented:
Thank you all!!!

I must have a serious problem somewhere!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.