Query With No Data Returns Blank Form

[Steve Mulhall]

Hi all,

I have a form with a combo box that displays the currently
available queries in my database. All these queries are
parameter queries so when one is selected, the user has to
input some criteria to query on and then a report is
produced with the query results displayed.

All works fine, except, if the there is no data for the
query I have a msgbox that tells the user this and it is
triggered on the Report_NoData(Cancel As Integer) event
procedure. Only problem is, when the message box is
displayed to tell the user there is no data, first the
above procedure loops around again, so the message is
displayed again, and then the report is loaded but with no
data i.e. just displays all the column names.

Firstly I'd like the report not to be loaded at all if
there is no data. Secondly, why is the message displayed
twice?

I have the following code in my report:

Private Sub Report_NoData(Cancel As Integer)
Dim Msg, Style, Title, Response

Msg = "Message"
Style = vbOKOnly + vbExclamation
Title = "Title"

Response = MsgBox(Msg, Style, Title)

If Response = vbOK Then
End If

End Sub

When the user presses 'OK' the whole procedure loops and
the message is displayed again, and when they press OK for
the second time, the "empty" report is displayed. I guess
there may be the need to display the code that is called
from my form during this procedure but I am loathe to
include it at this stage bearing in mind the length of
this post already.

Steve

 

[Allen Browne]

You need to set the Cancel argument to True:

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
MsgBox "No data for report " & Me.Name
End Sub

If you used OpenReport, the code will receive error 2501 so it is notified
that the report did not open. Use error handling to ignore this error
number.

 

[Steve Mulhall]

Cheers Allen,

You're a star. That works perfectly.

Easy when you know how no doubt.

Steve