Re: Close a Report When Sub-Reports are Empty

hi Keith,

Keith Wilby wrote:
I have an unbound report which contains two subreports. I've used the method suggested by Al to count the records in the subs but I want the main report to close if both subs are empty. I've successfully trapped error 440 in various events but each time I use
DoCmd.Close acReport, Me.Name
I get an error saying that the action isn't possible whilst the report is formatting. Does anyone have a solution?
Use a bound report with a SQL statment like that

SELECT * FROM [yourTable] WHERE 0 = 1

As this will never return a record, the event on no data is called. In this event, you can count the recordsets of the subreport. If they are 0 then you set Cancel = True and the report will not be printed:

Private Sub Report_NoData(Cancel As Integer)

Dim RecCount As Long

RecCount = DCount(<subreport1>) + DCount(<subreport2>)

Cancel = (RecCount = 0)

End Sub



mfG
--> stefan <--
.

Relevant Pages

  • Re: general programming guideline question
    ... A form or report that has a code module is an example of a class object. ... subs, etc.) and does not represent an object. ... You could create all routines as functions and there are some developers who ... If needs change later on, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Close a Report When Sub-Reports are Empty
    ... I have an unbound report which contains two subreports. ... report to close if both subs are empty. ... I've successfully trapped error 440 ... Or use a timerevent when the formatting is done ?? ...
    (microsoft.public.access.reports)
  • Re: Close a Report When Sub-Reports are Empty
    ... I have an unbound report which contains two subreports. ... report to close if both subs are empty. ... I've successfully trapped error 440 ... I believe you can use the ReportActivate event for this purpose: ...
    (microsoft.public.access.reports)
  • RE: Report with subreport formatting
    ... There should be no need to hide these subreports (i.e. you should not have ... Make sure the subreports are not overlapping each other, ... I have a report with 3 subs, ...
    (microsoft.public.access.reports)
  • Question on program design - progress reporting
    ... wondering what is the best way to get a running report of activity so it ... and that object will have the responsibility to report on each subs progress ... oReport.CurrentAction = "Done Calculating values" ...
    (microsoft.public.vb.general.discussion)