Link to home
Start Free TrialLog in
Avatar of jmcclosk
jmcclosk

asked on

Problems Closing a Report with Detail Formatting

I have a report for which I have built a filter form.  The filter form opens first, giving the end user seven drop down boxes with choices they can select to filter the impending report (i.e. Name, Date, Manager Name, etc).  The query associated with the report is programmed to filter based on the selections from the form and, if none of the selections are chosen, assume null for them and return all records.

I am trying to resolve the event of a filter set returning no records.  I tried to create a separate make-table query to create a temporary table before running the report with the same filtered record set.  Then, if the table was empty, don't run the report at all.  This didn't work because the query apparently is so complex I couldn't get it to work as anything other than a select query.

So, I turned to the report itself and I see there is an event option for "On No Data".  So, I tried to set it up to do a DoCmd.Close on this event.  The problem is that I have code in the On Format event of the detail area.  And, it seems to want to run this code before closing itself.  This returns a run-time error 2585.  I tried to pass variables from the On No Data section and wrap the On Format in an If statement saying don't process this if variable equals 1.  That doesn't work.

Can anyone share some ways to get a report to close if no records are present?  Is there a way to stop it from trying to process the On Format event of the Detail section if no records are returned?
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America 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
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
The method I show (which includes a code sample) are the correct way to handle a "No Data" error from an Access report. Essentially the report will throw the error back to the calling routine (the form, in this case) and that calling routine must handle the error.

the Error Handler structure I suggested will do exactly that.