Link to home
Start Free TrialLog in
Avatar of Derek Brown
Derek BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

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

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.
One way, you need to loop through reports, find record source for a report, check for records, and run qualified report.
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

Avatar of Derek Brown

ASKER

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?
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?
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Thank you all!!!

I must have a serious problem somewhere!!!