Message box to display "No results found"

[funwithaccess]

Hi,

I am not sure where to start or go about creating message box that would display "No results found" if my queries return no results. My search of the forums hasn't been fruitful.

I have created one form that has a text box and a button that works as a search function to run through the tables and displays the applicable queries. Currently, if no results are returned nothing happens. Preferably I would like a message box to display stating that there were "No results found". I would assume that the code would be associated with the "Find" button. Here is the VBA code as it stands, again, I haven't even attempted to add code for a message box.

Private Sub Find_Click()
On Error GoTo Find_Click_Err

If DCount("Heading", "Service Desk Manual Query") > 0 Then
DoCmd.OpenQuery "Service Desk Manual Query", acViewNormal, acReadOnly
End If

If DCount("Alias", "SD Documentation Query") > 0 Then
DoCmd.OpenQuery "SD Documentation Query", acViewNormal, acReadOnly
End If

If DCount("AssetNumber", "Xerox Assets Query") > 0 Then
DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly
End If

If DCount("[IP Address]", "Xerox IP Query") > 0 Then
DoCmd.OpenQuery "Xerox IP Query", acViewNormal, acReadOnly
End If

If DCount("SerialNumber", "Xerox Serial Query") > 0 Then
DoCmd.OpenQuery "Xerox Serial Query", acViewNormal, acReadOnly
End If

Find_Click_Exit:
Exit Sub

Find_Click_Err:
MsgBox Error$
Resume Find_Click_Exit

Thank you in advance!!!!!!!

 

[Roku]

Hi

How does the TextBox relate to your Click handler? Is it relevant to the problem?

I assume you want every query to open when its test condition is true? I ask because you could use ElseIf on each DCount test to select the first which is true, with a final Else with the MsgBox is it gets that far.

If you do want all tests to be run, then perhaps a counter would help. Declare an integer and set its value to zero at the start of your subroutine. For each DCount test that succeeds, add 1 to the counter.
When you get to the end, if the counter is still zero, then none of the tests were passed, so you display your 'No Results' message.

HTH

 

[funwithaccess]

Hi Roku,

I mention the text box because that is where the user inputs the search data. Then the "Find" button is clicked to run the VBA code to return the appropriate queries.

Please excuse my ignorance regarding VBA, my knowledge on the subject is quite basic. How would I structure the code to include the Else for the MsgBox? And is the counter required? If it is, how would I set that up?

Thank you so much for your patience!

Nate

 

[EternalMyrtle]

Did you try:

If DCount("Heading", "Service Desk Manual Query") > 0 Then
DoCmd.OpenQuery "Service Desk Manual Query", acViewNormal, acReadOnly
Else: MsgBox "No Records Found."
End If

Or you could do another
If DCount("Heading", "Service Desk Manual Query") = 0 Then
MsgBox "No Records Found."
End If

 

[Roku]

Hi

With the counter and message box, your code would look like this:

Private Sub Find_Click()
On Error GoTo Find_Click_Err
Dim intCount As Integer
intCount = 0
If DCount("Heading", "Service Desk Manual Query") > 0 Then
  intCount = intCount + 1
  DoCmd.OpenQuery "Service Desk Manual Query", acViewNormal, acReadOnly
End If
If DCount("Alias", "SD Documentation Query") > 0 Then
  intCount = intCount + 1
  DoCmd.OpenQuery "SD Documentation Query", acViewNormal, acReadOnly
End If
If DCount("AssetNumber", "Xerox Assets Query") > 0 Then
  intCount = intCount + 1
  DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly
End If
If DCount("[IP Address]", "Xerox IP Query") > 0 Then
  intCount = intCount + 1
  DoCmd.OpenQuery "Xerox IP Query", acViewNormal, acReadOnly
End If
If DCount("SerialNumber", "Xerox Serial Query") > 0 Then
  intCount = intCount + 1
  DoCmd.OpenQuery "Xerox Serial Query", acViewNormal, acReadOnly
End If
If intCount = 0 Then MsgBox "No results found", vbExclamation + vbOKOnly, "Search Results"
Find_Click_Exit:
Exit Sub
Find_Click_Err:
MsgBox Error$
Resume Find_Click_Exit
End Sub

This method allows every DCount to be checked with its corresponding OpenQuery to be run if values are present in its query.

If you only wanted just the first of the OpenQuery actions (with DCount>0) to be run, you would use

Private Sub Find_Click()
On Error GoTo Find_Click_Err
If DCount("Heading", "Service Desk Manual Query") > 0 Then
  DoCmd.OpenQuery "Service Desk Manual Query", acViewNormal, acReadOnly
ElseIf DCount("Alias", "SD Documentation Query") > 0 Then
  DoCmd.OpenQuery "SD Documentation Query", acViewNormal, acReadOnly
ElseIf DCount("AssetNumber", "Xerox Assets Query") > 0 Then
  DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly
ElseIf DCount("[IP Address]", "Xerox IP Query") > 0 Then
  DoCmd.OpenQuery "Xerox IP Query", acViewNormal, acReadOnly
ElseIf DCount("SerialNumber", "Xerox Serial Query") > 0 Then
  DoCmd.OpenQuery "Xerox Serial Query", acViewNormal, acReadOnly
Else
  MsgBox "No results found", vbExclamation + vbOKOnly, "Search Results"
End If
Find_Click_Exit:
Exit Sub
Find_Click_Err:
MsgBox Error$
Resume Find_Click_Exit
End Sub

I asked about the TextBox because I can't see a link between it and your code. What I expect is some value to be entered in the TextBox and the click event to create a query based on the value.
I notice the reply from EternalMyrtle, which gives you another choice.

 

[bignate]

If the query results open in a new form then do this.

In the Load event of the form you wanted the results to open in put this code:

If Me.Recordset.RecordCount = 0 Then
DoCmd.Close
DoCmd.Close acQuery, "QueryNamehere"
MsgBox "No records were found matching the search criteria"
End If

 

[EternalMyrtle]

Nice, Roku! Mine gives another choice but a very rudimentary one

 

[funwithaccess]

Wow! I come back from lunch, get some other work done and end up with quite a few responses! Thank you everyone for your assistance!

Roku,

The first set of code in your most recent post is exactly what I needed! I now see what you meant by utilizing the counter. It works perfectly for what I am trying to do! Thank you!!