How do I check for records opening a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a search form with multiple selection criteria. The user can
select none, any, or all of these criteria to use as a filter. Once they
click the print button it closes the search form then opens the report with
DoCmd.OpenReport applying the filter based on their input. Everything works
great unless they pick a combination of criteria that does not have a
corresponding record. The report opens but I get #ERROR in all of my
calculated fields with everything else blank.

I would like to check for data and give the user an error message if nothing
is found. Any guidance you can provide would be great.

Thanks,
Rich
 
RichNCSU said:
I have created a search form with multiple selection criteria. The user can
select none, any, or all of these criteria to use as a filter. Once they
click the print button it closes the search form then opens the report with
DoCmd.OpenReport applying the filter based on their input. Everything works
great unless they pick a combination of criteria that does not have a
corresponding record. The report opens but I get #ERROR in all of my
calculated fields with everything else blank.

I would like to check for data and give the user an error message if nothing
is found. Any guidance you can provide would be great.
Click to expand...


The typical approach to this is to use the report's NoData
event to cancel the report.

Then in the form procedure that opens the report, add error
handling to trap error 2501 and display a message box.
 
Marsh,

Thanks for the response. I have been playing with that option but am having
a problem. The NoData event gives the message but will not close the form
due to error 2585 "This action can't be carried out while processing a form
or report event. If I trap and ignore that error will it work or do I have
to some more magic. I'll give that a try.

Thanks,
Rich
 
Since you didn't post the code you used, I have no idea how
you got that error.

The code in the report's NoData event should simply be:
Cancel = True

The code in the form's button's Click event would be along
these lines:
Dim . . .
On Error GoTo ErrHandler
. . .
DoCmd.OpenReport . . .
. . .
ExitHere:
Exit Sub
ErrHandler:
Select Case Err.Number
Case 2501
Resume ExitHere
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume ExitHere
End Select
End Sub
 
Marsh,

That did the trick! I was using the DoCmd.Close rather than the Cancel=True.

Thanks,
Rich

Marshall Barton said:
Since you didn't post the code you used, I have no idea how
you got that error.

The code in the report's NoData event should simply be:
Cancel = True

The code in the form's button's Click event would be along
these lines:
Dim . . .
On Error GoTo ErrHandler
. . .
DoCmd.OpenReport . . .
. . .
ExitHere:
Exit Sub
ErrHandler:
Select Case Err.Number
Case 2501
Resume ExitHere
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume ExitHere
End Select
End Sub
--
Marsh
MVP [MS Access]

Thanks for the response. I have been playing with that option but am having
a problem. The NoData event gives the message but will not close the form
due to error 2585 "This action can't be carried out while processing a form
or report event. If I trap and ignore that error will it work or do I have
to some more magic. I'll give that a try.
Click to expand...
Click to expand...
 
Back
Top