zero record return in select query (Access2000 or 97)

[ylev]

I have a form based on select query. I want to have a MsgBox pop up instead of form opens when my select query return no record.
Thanks <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

[Francois]

In the on open event of the form you don't want to open, enter the following code :
<pre>Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
MsgBox "There is no data"
Cancel = True
End If
End Sub</pre>

[ylev]

Thanks for your answer, but I opened this form from another form using VBA DoCmd method, which does not work with Cancel in the dialog box. Any idea?

[Francois]

In the code where you use the the docmd method to open the form, you have probably on error code. Something like
<pre>Exit_Command279_Click:
Exit Sub
Err_Command279_Click:
MsgBox Err.Description
Resume Exit_Command279_Click
End Sub</pre>

Change it to
<pre>Exit_Command279_Click:
Exit Sub

Err_Command279_Click:
If Err.Number = 2501 Then
Exit Sub
End If
MsgBox Err.Description
Resume Exit_Command279_Click

End Sub</pre>

[ylev]

Thanks a lot!!!!!!!!!!!!!