Message box help for search form.

[ron513]

Hello,

I require some assistance with adding a message box to the below listed code to notify the user there was no match for the value entered in the text box, “txtSearch”.

The code is executed from a search form, “frmSearch” which is opened from a button on the form, “frmMain”. The form “frmSearch” consists of an option group for the user to select the field to do the search on, in this case a Bid Tracker Number or Title, a text box “txtSearch” with corresponding label to enter the value to search for, and three command buttons “Search”, “Cancel”, “Close”.

The code is primarily from postings on this site and few lines of my own.

I am also open to any suggestions on improving the code. I do not have much experience so please reply with that in mind.
Thanks for the Help.

Ron

Private Sub cmdSearch_Click()

'Requery the form, frmMain based on
'the selectioned items
Dim mstrSQL As String

On Error GoTo HandleErr

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
DoCmd.OpenForm "frmSearch"
Me![txtSearch].SetFocus
Exit Sub
Else

' Construct SQL for Main's Recordsource
Select Case optSearch
Case 1
'Bid tracker Number
If Not IsNumeric(txtSearch) Then
MsgBox "Please enter a numeric value.", vbOKOnly, "Error"
Exit Sub
Else
mstrSQL = "SELECT * FROM tblMain Where " _
& " BidTracker Like '*" & DoubleQuote(Me![txtSearch]) & "*'"
End If
Case 2
'Title
mstrSQL = "SELECT * FROM tblMain WHERE " _
& " Title Like '*" & DoubleQuote(Me![txtSearch]) & "*'"
Case Else
End Select
DoCmd.OpenForm "frmMain"
Forms!frmmain.RecordSource = mstrSQL

End If

DoCmd.Close acForm, "frmSearch"

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error"
End Select
Resume ExitHere
Resume

End Sub

Private Function DoubleQuote(strIn As String) As String
Dim i As Integer
Dim strtemp As String
For i = 1 To Len(strIn)
If Mid(strIn, i, 1) = "'" Then
strtemp = strtemp & "''"
Else
strtemp = strtemp & Mid(strIn, i, 1)
End If
Next i
DoubleQuote = strtemp
End Function

Private Sub cmdSearchClose_Click()
On Error GoTo Err_cmdSearchClose_Click

DoCmd.Close

Exit_cmdSearchClose_Click:
Exit Sub

Err_cmdSearchClose_Click:
MsgBox Err.Description
Resume Exit_cmdSearchClose_Click

End Sub

Private Sub cmdCancel_Click()
txtSearch.Value = ""
txtSearch.SetFocus
End Sub

Private Sub optBidTrackerNumber_GotFocus()
lblSearch.Caption = "Enter Bid Tracker Number."
End Sub

Private Sub optTitle_GotFocus()
lblSearch.Caption = "Enter Proposal Title."
End Sub