How to test a report for no records in On Open

  • Thread starter Thread starter Mick Ruthven
  • Start date Start date
M

Mick Ruthven

How can I test for no records in the On Open event of a report so I can
close it before it appears? I can't use the report's On No Data event
because that occurs after the On Format event and I have code in the On
format event that blows if there are no records. I now have a test in the On
Format event that tests for no records and closes the report there, but an
empty report appears. I want to test for no records in time to close the
report without any part of the report displayed.
 
Mick said:
How can I test for no records in the On Open event of a report so I can
close it before it appears? I can't use the report's On No Data event
because that occurs after the On Format event and I have code in the On
format event that blows if there are no records. I now have a test in the On
Format event that tests for no records and closes the report there, but an
empty report appears. I want to test for no records in time to close the
report without any part of the report displayed.
Click to expand...


Use the report's NoData event instead of the Open event.
Setting Cancel = True will prevent the report from
appearing. If you are opening the report from a form, the
canceled report will bring up error message 2501 so you will
probably want to trap that error and ignore it.
 
Have you tried an Error Handler in the Format event and told it to simply
ignore the error?
 
As I said, I've successfully bypassed the error-causing code on On Format if
there are no records and closed the report, but the report opens anyway with
no records. I want to prevent the no-record report from opening.

(Sorry I first sent this by email instead of posting to the forum.
Finger-slip.)
 
I found a way. I put this in the report's On Open event:

If Not Me.HasData Then
MsgBox "There are no records for this report."
Cancel = True
End If
 
Boy, I spoke too soon. In a report's On Open event, Me.HasData returns false
whether or not there are any records present.

Anybody??
 
Mick Ruthven said:
Boy, I spoke too soon. In a report's On Open event, Me.HasData returns false
whether or not there are any records present.

Anybody??
Click to expand...

In my testing the NoData event fires before any of the section events. You've
got something else wrong if that is not happening. This is the very purpose of
the NoData event. It would be pretty useless if other events fired first.
 
You never stated what code you are running and which format event. If it is
the On Format of the detail section, it will never be run if there are no
records returned in the report.

Please share more information about your situation.
 
OK, let's start over. This is an Access 2002 application, which runs Visual
Basic 6.3.

1. I don't agree that the On Format of the detail section will never be run
if there are no records returned in the report, if you mean by that that
there are no detail records. Code I've placed in the On Format of the detail
section runs with no records present from the report's source query.

2. The code I'm using in the On Format of the detail section uses a control
sourced by a yes/no field. That code gives an error when no records are
present. Fair enough. I've been able to protect that code, and close the
report if no data is present, by this:

If Me.HasData Then
- Code that blows up if nor records are present -
Else
MsgBox "No records."
Cancel = True
End If

This works fine except that it if no records are present, it opens the
report anyway with the headers, etc., and of course no data. I could leave
well enogh alone, but I want to prevent that empty report from appearing for
a cleaner handling of the no-record condition.

3. That's when I put the HasData test into the On Open event and closed the
report if no records were present:

If Not Me.HasData Then
MsgBox "There are no records for this report."
Cancel = True
End If

But I found that Me.HasData returns "false" whether or not any records are
present, sort of like it's too early for the data to show up for HasDate to
know it's there.

Is that a clearer statement?

Thanks,

Mick Ruthven
 
Ooops the code does run even if there are no records.
Your code in the On Format event of the detail section will cancel the
printing of the detail section, not the report.

There is a On No Data property event that you can use. With the following
code, the Detail Format MsgBox will not display. The Report_NoData MsgBox
will display and the report will close.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
MsgBox "On Format of Detail Section"
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no records for this report."
Cancel = True
End Sub


--
Duane Hookom
MS Access MVP
--

Mick Ruthven said:
OK, let's start over. This is an Access 2002 application, which runs
Visual
Basic 6.3.

1. I don't agree that the On Format of the detail section will never be
run
if there are no records returned in the report, if you mean by that that
there are no detail records. Code I've placed in the On Format of the
detail
section runs with no records present from the report's source query.

2. The code I'm using in the On Format of the detail section uses a
control
sourced by a yes/no field. That code gives an error when no records are
present. Fair enough. I've been able to protect that code, and close the
report if no data is present, by this:

If Me.HasData Then
- Code that blows up if nor records are present -
Else
MsgBox "No records."
Cancel = True
End If

This works fine except that it if no records are present, it opens the
report anyway with the headers, etc., and of course no data. I could
leave
well enogh alone, but I want to prevent that empty report from appearing
for
a cleaner handling of the no-record condition.

3. That's when I put the HasData test into the On Open event and closed
the
report if no records were present:

If Not Me.HasData Then
MsgBox "There are no records for this report."
Cancel = True
End If

But I found that Me.HasData returns "false" whether or not any records are
present, sort of like it's too early for the data to show up for HasDate
to
know it's there.

Is that a clearer statement?

Thanks,

Mick Ruthven
Click to expand...
 
Bingo! Thanks so much. It's funny how this development stuff works. I know
about the On No Data event, but you may note that in my original post I said
that using it wouldn't work because it fired after the On Format event where
the no-records problem was. Then I learned how to test for no records in the
On Format event but didn't think to combine that test - branch around the
problem code if no records - with closing the report in the On No Data
event. Using those two processes works just fine and is simpler than other
combinations I had tried.

Thanks again!

Duane Hookom said:
Ooops the code does run even if there are no records.
Your code in the On Format event of the detail section will cancel the
printing of the detail section, not the report.

There is a On No Data property event that you can use. With the following
code, the Detail Format MsgBox will not display. The Report_NoData MsgBox
will display and the report will close.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
MsgBox "On Format of Detail Section"
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no records for this report."
Cancel = True
End Sub
Click to expand...
 
Mick Ruthven said:
Bingo! Thanks so much. It's funny how this development stuff works. I know
about the On No Data event, but you may note that in my original post I said
that using it wouldn't work because it fired after the On Format event where
the no-records problem was. [snip]
Click to expand...

And I don't see that in my testing. I created a test report with four grouping
levels and placed code in the OnFormat of all four section headers, the page
header, the page event, the report header and the detail section and No Data
fired before ANY of them. The only event I could find that fired before NoData
was the Open event of the report.
 
Back
Top