Adding a Message Box

[tarcona]

I made a search form. I want to add a message box when there are no results. What would the code be to add a message when the search returned no results?

I know you have to put:
MsgBox "Your search returned no results. Please check the spelling and try again.", vbInformation, "Invalid Search Criterion"

But where would I put this? What would be the statement?
This is my code:

Private Sub btnSearchAccounts_Click()
    Dim strWhere As String   ' The Criteria String
    Dim lngLen As Long       ' Length of the criteria string to append to
 
' Adds LIKE Company field match to the string
    If Not IsNull(Me.txtSearchCompany) Then
        strWhere = strWhere & " ([Company] Like '" & Me.txtSearchCompany & "*') AND "
    End If
 
lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "Please try again.", vbInformation, "Invalid Search Criterion"
 
    Else
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere
 
        ' Apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
 
End Sub

 

[KenHigg]

My suggestion is to plug in a dcount() to see if there are any records before you applied the filter...

 

[tarcona]

I just looked at some DCount() information and I dont seem to quite get it. Could you help me out a little more...

 

[KenHigg]

You can put your criteria in the dcount() first. And if the result = 0 then you know to tell the user such -

 

[tarcona]

I am totally lost...still. Sorry.

 

[KenHigg]

Sorry Tarcona, I have to bug out, maybe someone else can jump in...

 

[boblarson]

Something like this - be sure to put in the applicable names of YOUR real items:

If DCount("YourFieldNameInTheTable", "YourTableName", "[Company] Like '" & Me.txtSearchCompany & "*') > 0 Then
   ...Run your code here
Else
   Msgbox "No Records Found"
   Exit Sub
End If

 

[tarcona]

Awesome. One more question...sorry.
I have multiple fields that I search from. In the example code I supplied, I only listed one field. I have 4, I believe. How do you DCount all of them at the same time?

 

[boblarson]

It would use the same final filter string:

"[Company] Like '" & Me.txtSearchCompany & "*' And [AnotherField]='" & Me.txtWhatever & "*' AND [SomeDateField]>#" & Me.DateFieldName & "#"

etc.

 

[tarcona]

I tried it and it didnt work. I am probably not doing it right.
I am still confused. . . .

 

[boblarson]

What are ALL of the actual names of your fields you are looking to filter by and what is the form name and control names of the filtering form?

 

[tarcona]

This is my actual code:

Private Sub btnSearchAccounts_Click()
 
    Dim strWhere As String   ' The Criteria String
    Dim lngLen As Long       ' Length of the criteria string to append to
 
    ' Adds LIKE Company field match to the string
    If Not IsNull(Me.txtSearchCompany) Then
        strWhere = strWhere & " ([Company] Like '" & Me.txtSearchCompany & "*') AND "
    End If
 
    ' Adds EXACT State field match to the string
    If Not IsNull(Me.txtSearchState) Then
        strWhere = strWhere & "([State] = """ & Me.txtSearchState & """) AND "
    End If
 
    ' Adds LIKE Zip field match to the string
    If Not IsNull(Me.txtSearchCity) Then
        strWhere = strWhere & "([City] LIKE ""*" & Me.txtSearchCity & "*"") AND "
    End If
 
    ' Adds EXACT Account ID match to the string
    If Not IsNull(Me.txtSearchAccountID) Then
        strWhere = strWhere & " ([Account ID] Like """ & Me.txtSearchAccountID & """) AND "
    End If
 
lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "Please try again.", vbInformation, "Invalid Search Criterion"
 
    Else
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere
 
        ' Apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
 
End Sub

So I am filtering by Company, State, City, and Account ID.

My form name is "AllRecordsSearch"

 

[boblarson]

Try adding a DCOUNT here (see red and replace the applicable parts in the code with the actual table and field name because I don't know what your form is base off of, I couldn't include them):

Private Sub btnSearchAccounts_Click()
 
    Dim strWhere As String   ' The Criteria String
    Dim lngLen As Long       ' Length of the criteria string to append to
 
    ' Adds LIKE Company field match to the string
    If Not IsNull(Me.txtSearchCompany) Then
        strWhere = strWhere & " ([Company] Like '" & Me.txtSearchCompany & "*') AND "
    End If
 
    ' Adds EXACT State field match to the string
    If Not IsNull(Me.txtSearchState) Then
        strWhere = strWhere & "([State] = """ & Me.txtSearchState & """) AND "
    End If
 
    ' Adds LIKE Zip field match to the string
    If Not IsNull(Me.txtSearchCity) Then
        strWhere = strWhere & "([City] LIKE ""*" & Me.txtSearchCity & "*"") AND "
    End If
 
    ' Adds EXACT Account ID match to the string
    If Not IsNull(Me.txtSearchAccountID) Then
        strWhere = strWhere & " ([Account ID] Like """ & Me.txtSearchAccountID & """) AND "
    End If
 
lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "Please try again.", vbInformation, "Invalid Search Criterion"
 
    Else
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere
[color=red]If DCount("YourFieldNameHere","YourTableNameHere", strWhere) > 0
        ' Apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
Else
      Msgbox "No Records Match", vbInformation, "No Records"
End If[/color]
    End If
 
End Sub

 

[tarcona]

Ok. I have no clue what the field name should be because I have those four fields that I am searching in....do I put all field names or what?

I am searching in my 'Accounts' table.

 

[tarcona]

Actually I think I got it to work here. Thanks boblarson for your help.