• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 37
  • Last Modified:

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
0
Derek Brown
Asked:
Derek Brown
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
 
hnasrCommented:
One way, you need to loop through reports, find record source for a report, check for records, and run qualified report.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now