Display message when there are no query results!

[LJtechnical]

I Have a form with a button which opens another form based on a query. Sometimes the query will yield no information which is fine, but i would like for the form to close instantly and display a message box explaining why. I'm sure this is incredibly easy but i cant quite get my head round it. I am trying to use the IsNull command/on open event on one of the fields in the second form. But the fields are not displayed when the query produces no results.

Any one else tried doing this before.

 

[kevinclark]

In DAO you can use the RecordCount to return the number of records but in ADO you can not. If using ADO maybe use a counter and test its value

ADO Example:

Dim intRec as Integer

Do while not rsRecordset.EOF
'Do your stuff
intRec = intRec + 1
next rsRecordset
Loop

If intRec < 1 then
'No records exist
UserForm1.visible = false
msgbox &quot;No records Available&quot;
End If



DAO Example:
rsrecordset.MoveLast
rsRecordset.Movefirst
If rsRecordset.RecordCount > 0 then
'Do your stuff

Else
'No records exist
UserForm1.visible = false
msgbox &quot;No records Available&quot;

End if


Hope it helps

 

[hermanlaksko]

Before opening the form you know what the form will show i.e. if the forms recordset will have records or no records.

On your open button try this:
dim MyRe as dao.recordset, MyDb As Dao.Database
set mydb=currentdb
set myre=mydb.openrecordset(&quot;Select * From YrTbl Where YrFld=&quot; & me!YrTest)
if myre.recordcount>0 then
docmd.openform, &quot;YrForm&quot;
else
msgbox &quot;No records found&quot;
end if
Should do the trick

 

[LJtechnical]

Thanks to both of you, couldn't get the code working but you pointed me in the right direction. Here is the code I used if interested.
__________________________________________________________
Private Sub Form_Load()
Dim rs As Object

If Forms!Mapping.RecordsetClone.RecordCount = 0 Then
MsgBox &quot;The query returned no results please try again.&quot;
DoCmd.Close acForm, &quot;Mapping&quot;
End If

End Sub