Prevent empty query from opening

[SeadooRider]

I have the following code which uses a form that has (two date boxes and two comboboxes) to allow the user to select criteria for a query result. It all works fine except when no records are found with that criteria. The query still opens with no records.
Question is, how can I insert a msgbox saying "no matching records found" prior to the query/and eventually a report, opening?

Private Sub cmdSearch_Click()
If IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And IsNull([cboIssue]) Then
MsgBox "No Search Criteria Selected", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
Else
If Not IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And IsNull([cboIssue]) Then
MsgBox "You must select a date range or issue type to continue", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
Else:
If IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And IsNull([cboIssue]) Then
MsgBox "You must select a VIP or issue type to continue", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
Else
If Not IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And IsNull([cboIssue]) Then
DoCmd.OpenQuery "qryWalkthrough", acViewNormal, acViewNormal
Else
If Not IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And Not IsNull([cboIssue]) Then
DoCmd.OpenQuery "qryWalkthrough", acViewNormal
Else
If IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And Not IsNull([cboIssue]) Then
DoCmd.OpenQuery "qryWalkthrough", acViewNormal
Else
If Not IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And Not IsNull([cboIssue]) Then
MsgBox "Please remove one search criteria", vbDefaultButton1 = vbOKOnly, "To many Critera fields chosen"
End If
End If
End If
End If
End If
End If
End If
 
End Sub
 

[zmbd]

You can use a Dcount() function against the query. YOu can F1 help on it or Google.

However, with the structure of your code, we'll have to jump thru hoops to get this to work properly.

I'm on my way out the door with kids to school or I'd take a stab...

Normally, I'd be using a DAO recordset and drastically different code to do what you're doing here.... stacked IF.THEN are not really the easiest/cleanest to work with.

[SeadooRider]

Thanks, I'm all for easier/better ways of doing things but guess I went with what I know.

[ADezii]

zmbd pretty much answered your question for you. You need to first check for the existence of any Return Records for each Query using the DCount() Function before you actually Open the Query:

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
Dim strCriteria As String
 
If IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And IsNull([cboIssue]) Then
  MsgBox "No Search Criteria Selected", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
    Exit Sub
ElseIf Not IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And IsNull([cboIssue]) Then
  MsgBox "You must select a date range or issue type to continue", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
    Exit Sub
ElseIf IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And IsNull([cboIssue]) Then
  MsgBox "You must select a VIP or issue type to continue", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
    Exit Sub
ElseIf Not IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And IsNull([cboIssue]) Then
  strCriteria = "Not IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And IsNull([cboIssue])"
    If DCount("*", "<Your Table>", strCriteria) <> 0 Then
      DoCmd.OpenQuery "qryWalkthrough", acViewNormal, acViewNormal
    End If
ElseIf Not IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And Not IsNull([cboIssue]) Then
  strCriteria = "Not IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And Not IsNull([cboIssue])"
    If DCount("*", "<Your Table>", strCriteria) <> 0 Then
      DoCmd.OpenQuery "qryWalkthrough", acViewNormal
    End If
ElseIf IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And Not IsNull([cboIssue]) Then
  strCriteria = "IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And Not IsNull([cboIssue])"
    If DCount("*", "<Your Table>", strCriteria) <> 0 Then
      DoCmd.OpenQuery "qryWalkthrough", acViewNormal
    End If
ElseIf Not IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And Not IsNull([cboIssue]) Then
  MsgBox "Please remove one search criteria", vbDefaultButton1 = vbOKOnly, "To many Critera fields chosen"
    Exit Sub
End If
End Sub
 

P.S. - The Exit Subs are for illustration purposes only.

[SeadooRider]

ADezii, It needed the query name instead of the table name, but other than that it works perfect.

Thanks again

[NeoPa]

There's a NoData event for reports. I expect that's all you need to handle this (unless I misread your intention).

Bear in mind, when Cancel is set in this event the code that calls the Report to Open fails (So this should also be handled for smooth operation).

[ADezii]

Question is, how can I insert a msgbox saying "no matching records found" prior to the query/and eventually a report, opening?

I totally missed the 'eventually a Report' part. NeoPa's Reply in Post# 6 appears to be the way to go in this light.