Checking for empty RecordSource

[Guest]

I have a user input form for generating a report. The selections the user
makes get passed via global variables to the report. The report's open
event, among other things, is where the RecordSource query is built for the
report. Certain combinations of user input can produce an empty set of
records. How do I check for this condition so that I can gracefully warn the
user of the condition and close the report?

The following construct (in the report's open event) is not valid:
If Me!RecordSource.EOF and Me!RecordSource.BOF then ...

 

[Allen Browne]

Just cancel the form's NoData event.

Access only fires that event if the report has no data.

 

[Guest]

Allen,

I didn't know this report event existed! This looks like exactly what I
need and seems simple enough, but I apparently need further help here. My
code for this event is:

Private Sub Report_NoData (Cancel as Integer)
Msgbox ...
Cancel = True
End Sub

The user message appears, but then I get "Run time error 2501. The
OpenReport action was canceled." Why am I getting this error message and how
do I avoid it?

ctdak

 

[Douglas J. Steele]

In the event that's calling the form, put error trapping that ignores error
2501:

Private Sub MyControl_MyEvent()
On Error GoTo MyControl_MyEvent_Err



MyControl_MyEvent_End:
Exit Sub

MyControl_MyEvent_Err:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume MyControl_MyEvent_End
End Select

End Sub

 

[Guest]

Thanks for the reply. I'm not terribly experienced with VBA and certainly
have limited knowledge of error trapping code ... but ...

Surely error trapping can be done more globally than this. I have a lot of
similar events where this same error trapping routine would need to go in my
application code. There must be some way to place such code in only one
place (like a global procedure) and then just call it from the various events.

Out of curiosity, I also tried creating an On Error event procedure for the
form calling the report and/or the report itself, but these didn't fire at
all, so I don't know what triggers a form or report On Error event.

ctdak

 

[Allen Browne]

The form's Error event fires on engine-level errors (e.g. trying to save a
record that does not meet validation rules or uniqueness.) Failure to open
an form is not an engine-level issue.

You must include error handling in every routine (with the possible
exception of routines that are called from procedures that have error
handling, though that only makes it harder to debug.)

You can call a generic error handler that treats particular errors (such as
2501) in special ways. For an example of doing that, see:
Error Handling in VBA
at:
http://allenbrowne.com/ser-23a.html

 

[Guest]

Wow, thanks a bunch for this info! Your web site link is going to be
excellent for me. I wanted to start putting error trapping in my application
code and to create an error log table just like you have described here. I
will have to check out your web site more for code examples of other things.

ctdak

 

[Allen Browne]

Excellent.

If you are about to an add error handler to a whole bunch of code, you might
like to check out mztools.com. They have an Add-in for VBA that gives you an
extra toolbar, and one of the toolbar buttons adds error handling to any
function with a single click. You can configure what it adds, so you can
have it add the call to the generic error handling and logging function.

That could save you lots of typing.

 

[Guest]

How does using the toolbar of mztools differ from copy/paste operations?
Does it add an error handling routine to the beginning of all existing
procedures in a module with a single click, or ... ?
ctdak

 

[Alex Dybenko]

How does using the toolbar of mztools differ from copy/paste operations?
Does it add an error handling routine to the beginning of all existing
procedures in a module with a single click, or ... ?
ctdak

yes!

 

[John Spencer]

mzTools can add an error handling routine to each procedure. It can also
add a canned comments header to each procedure. It only does (as far as I
know) one procedure at a time.

It does a lot of other things also.

 

[Guest]

A previous response confirmed that it adds code to all procedures in a module
with a single click, which is what I was hoping for. If not, then what you
say here is no more beneficial than doing a copy and paste one procedure at a
time.
ctdak

 

[RD]

Well, that's not true. Manually, you would have to C&P the "On Error GoTo ..."
at the top and then C&P your error handler at the bottom, etc. With MZ Tools
you can right click within an existing procedure, choose "Add Error Handler" and
that's done for you. You can even go into Options and configure exactly how you
want your error handler to look. It's really rather handy even if it won't
automatically add error handlers to every procedure in a module.

MZ Tools has a bunch of other nifty tools that make it worth looking into.

 

[Allen Browne]

John and RD are correct: it's a click per procedure.

But it's considerably easier than copying and pasting the the top and bottom
of each procedure.

 

[Alex Dybenko]

one more advantage - that you can automatically incorporate module and proc
name into error handler, this is especially useful when you have error
logging in your application.

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com