A2003 SP2 (Report – No Data Question)

Topic

New Reply

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.

Reply | Quote

Replies

Gary,

In your event No data add this line:

label.caption = “There is no data for criteria ” & [parameter_name]

Reply | Quote

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)

Reply | Quote

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]));

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote