Adding a msgbox to a search form - easy peasy except for me :)

[ahuvas]

I have a code for a free text search form which will return records in another window when the search button is clicked.

[COLOR=DarkSlateBlue]CODE
Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
        'Filter frmRhinitis based on search criteria
        Form_frmRhinitis.RecordSource = "select * from tblBaseline where " & GCriteria
        Form_frmRhinitis.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
        MsgBox "Results have been filtered."
     
        
        End If
    
End Sub

[/COLOR]

However, even if no records match the criteria the window will return a "Results have been filtered" but return a blank form. How do I include code that will return a "Match cannot be found" MsgBox if the search string isnt found?


Thanks

 

[RuralGuy]

You could try this <<Air Code>>

Private Sub cmdSearch_Click()

Dim GCriteria As String

If Len(cboSearchField & "") = 0 Then
   MsgBox "You must select a field to search."
   Me.cboSearchField.SetFocus
   Exit Sub
End If
        
If Len(txtSearchString & "") = 0 Then
   MsgBox "You must enter a search string."
   Me.txtSearchString.SetFocus
   Exit Sub
End If     

'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
'Filter frmRhinitis based on search criteria
Form_frmRhinitis.RecordSource = "SELECT * FROM tblBaseline " & _
    "WHERE " & GCriteria

If Me.RecordSet.RecordCount > 0 Then
   Form_frmRhinitis.Caption = "Customers (" & cboSearchField.Value & _
   " contains '*" & txtSearchString & "*')"
   MsgBox "Results have been filtered."
Else
   MsgBox "Match cannot be found."
End If
    
End Sub

I would strongly recommend you put Option Explicit at the top of your code page. You could also use some error handling.

 

[ahuvas]

Thank you for your help - I have now solved the problem!

 

[RuralGuy]

Thanks for posting back and glad I could help.