Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problems Closing a Report with Detail Formatting

Posted on 2014-04-01
4
Medium Priority
?
356 Views
Last Modified: 2014-05-02
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?
0
Comment
Question by:jmcclosk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1600 total points
ID: 39971902
You should trap that 2585 error on the Filter form. I assume you have a button that actually opens the report, so do this:

Function MyButton_Click()
  On Error GoTo ErrHandler

  [code here to open the report]

Exit:
  Exit Function

ErrHandler:
  Select case Err.Number
    Case 2585
      ' do nothing
    Case Else
       Msgbox Err.Description
  End Select
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40026979
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 85
ID: 40026980
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question