Test your query for records

By using Dmax

from: http://experts.about.com/q/Using-MS-Access-1440/Conditional-macros-number-records.htm

Here is an IF construct that will test your query for records, then enable you to action alternative queries according to the outcome of the initial condition

If Len(DMax("FieldName", "QueryTestName")) > 0 Then

DoCmd.OpenQuery "QueryConditionTrue", acViewNormal
Else
DoCmd.OpenQuery "QueryConditionFalse", acViewNormal
End

A little explanation:
QueryTestName - this is the query which decides which action needs to be taken..
FieldName - is any field in the above query, which we can use foor checking the presence of records

DMax() function - returns a single record containing the maximum (numeric or alpha) of a field.

Len() function - returns the length of a string - so if DMax() returns a value (ie. there are records), then the length will be > 0, otherwise, if no records, DMax() returns 'Null' and the Len() will be 0 - so this is a good set of functions to use for testing for records or no records.

by using recordset recordcount

from : http://www.tek-tips.com/viewthread.cfm?qid=1218083&page=1

Private Sub Form_Open(Cancel As Integer)
Dim strRecError

If Me.RecordSource <> "" Then
    If Me.Recordset.RecordCount = 0 Then
        strRecError = "No Records. "
    End If
Else
    strRecError = "No recordset. "
End If
If strRecError <> "" Then
    If MsgBox(strRecError & "Continue?", vbYesNo) = vbNo Then
        Cancel = True
    End If
End If
End Sub

Example 2

Me!Recordset.MoveFirst
DoCmd.SetWarnings False
If Forms!frmhelpdesk1ProformaCompletion.Recordset.RecordCount = 0 Then
MsgBox "No orders were found for " & Me!CustomerReq, vbOKCancel, "Try Again"
DoCmd.Close acForm, "frmhelpdesk1ProformaCompletion"
'GoTo exit_cmdTotals_Click
End If