Cancel form load when underlying query recordset is null

[Guest]

I have a form that I want to open if the underlying query has a recordset
greater than 0, but if the query's recordset is null, I want a message box to
open alerting the user to the fact that there are no records to process
instead of the distractingly empty report that opens otherwise.

I have searched and found similar questions answered here, but the syntax of
there solution does not seem to work for me.

I am using Access 2000.

Thanks in advance for any assistance!

 

[Ken Snell \(MVP\)]

Use the Report's NoData event to test the recordset's recordcount, and
cancel the event if there are no records:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no data for the selected parameters.", _
vbInformation, "No Data"
Cancel = True
End Sub


Note that you'll need to trap for Error Number 2501 (You have canceled ....)
in the form that calls for the report to be opened so that the user won't
get a message about the error. Do you have an error handler in the code that
is opening the report? If yes, put this code in the error handler section:

If Err.Number <> 2501 Then MsgBox "Error has occurred"


If you're using On Error Resume Next in the code instead of an error
handler, put this line of code right after the DoCmd.OpenReport step:

If Err.Number <> 0 And Err.Number <> 2501 Then MsgBox "Error has occurred"

 

[Ken Snell \(MVP\)]

I just posted a reply under the assumption that you were opening a report
(because of your text "instead of the distractingly empty report that opens
otherwise"). But it appears that you want to open a form instead.

In the form that is being opened, put code in the form's Load event that
tests the record count, and if zero, close the form:

Private Form_Load()
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records", vbInformation, "No Records"
DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

 

[Guest]

I just posted a reply under the assumption that you were opening a report
(because of your text "instead of the distractingly empty report that opens
otherwise"). But it appears that you want to open a form instead.

And so I did! My mistake. But the code you suggested the second time around
works like a charm. Thank you so much, as always.