Results 1 to 7 of 7
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    A2003 SP2 (Report - No Data Question)

    Users have a report based on a query. The query contains a parameter query such that the when the report opens, the parameter query triggers, the users input data and the report opens with the appropriate data.

    The users then said that if the report opens blank (i.e, no data), they want a note to appear stating that there is no data. To do this, in the On No Data event of the report, code unhides a label stating no data is present.

    Here is the question. Users now want the no data message to indicate what was input in the parameter query. For example, Users input XYZ into the parameter query when the report opens and there is no data. The message would then return XYZ has no data.

    Is there a way to pass the parameter value to the report if the parameter returns no data?

    If not, I figure I could check the data for results using a Dcount first and if no data open a different report.
    Regards,

    Gary
    (It's been a while!)

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A2003 SP2 (Report - No Data Question)

    Gary,

    In your event No data add this line:

    label.caption = "There is no data for criteria " & [parameter_name]
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: A2003 SP2 (Report - No Data Question)

    Thanks, but no luck here. I get run time error 2465 Access cant find the field referenced in my expression. (In this case the reference to the parameter in the parameter query driving the report)
    Regards,

    Gary
    (It's been a while!)

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A2003 SP2 (Report - No Data Question)

    Place the [Parameter_name] into your report query
    Give it a name and then use that.

    Here is an example from the Northwind Traders database for the order form

    order qry

    SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, [What ID] AS ParmName
    FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
    WHERE (((Orders.OrderID)=[What ID]));
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: A2003 SP2 (Report - No Data Question)

    Don,

    Thanks for that. Sounds and looks logical but I'll be darned if I can get it to work. I must be having a more senior moment. Thanks for the help anyway.
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A2003 SP2 (Report - No Data Question)

    In such situations I usually open a report in code from a form, and replace the parameter of the query with a reference to a control (text box or combo box) on the form

    [Forms]![NameOfForm]![NameOfControl]

    You can then use the On No Data event of the report to display a message box and cancel the report:

    Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There are no data for " & Forms!NameOfForm!NameOfControl, vbInformation
    Cancel = True
    End Sub

    Canceling the report will cause error 2501 in the code that opened the report. This can be caught by an error handler:

    Private Sub cmdReport_Click()
    On Error GoTo ErrHandler
    DoCmd.OpenReport "rptSomething", acViewPreview
    Exit Sub

    ErrHandler:
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: A2003 SP2 (Report - No Data Question)

    Thanks Hans.

    I did come up with the following work around to get this to work.

    1. Removed the parameter from the query driving the report
    2. On the form that launches the report, used an input box to store the value of the parameter to filter by
    3. Used the Docmd, open report... and applied the filter from the input box
    4. Created a text box on the report that is typically invisible
    5. On the On No Data event of the report, make the text box visible and then set the text box value to the filter value passed to the report from the DoCmd OpenReport command.

    Other then some minor formatting on the filter value, this gets me to where I need to be.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •