asked on

Help with error handling for search with no returned records and for null values

I have the following code block that takes the input from a form (frmSearchMediaTitles) to conduct a search based on the information gathered. There are two values passed from the form the search type (vMediaTitleSearchType) and the search criteria (vSearchTerm)

I need to trap for the following errors. If either or both vMediaTitleSearchType and vSearchTerm are null then I want a message box to pop and explain the error and give the user the option to exit or go back to the form and correct the error.

I will also need to trap for an error if nothing is found that matches the search criteria. I believe this will involve setting up a DAO recordset. The code below only chows one CASE statement, when completed there will be 6 case statements that will need this type of error handling.

Private Sub btnSearch_Click()

Dim vMediaTitleSearchType As String
Dim vSearchTerm As String
Dim vSQL As String

vMediaTitleSearchType = Me.MediaTitleSearchType

Select Case Me.MediaTitleSearchType

Case "Title"

vSearchTerm = Me.SearchTerm
vSQL = "Title LIKE '*" & vSearchTerm & "*'"
DoCmd.Close 'Close form frmSearchMediaTitles
DoCmd.OpenForm "frmTitles", , , vSQL

End Select

ASKER CERTIFIED SOLUTION

Daniel Pineault

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fabrice Lambert

Hi,

@D Pineault:
By respect to SRP, I suggest to move the validation code in a separate function.
Also, in this case where there arn't many input, it is better to build a string explaining all reasons to the user:

Private Function ValidateInput() As Boolean
    ValidateInput = True

    Dim msg As String
    If IsNull(Me.MediaTitleSearchType) Then
        msg = msg & "Please specify a Search Type" & vbCrLf
    End If

    If IsNull(Me.SearchTerm)  Then
        msg = msg & "" & vbCrLf
    End If

    If (msg <> vbNullString) Then
        ValidateInput = False
       msg = msg & vbCrLf & "Please, try again."
       MsgBox msg, vbInformation + vbOKOnly, "Missing Search criteria"
    End If
End Function

Private Sub btnSearch_Click()
     Dim vMediaTitleSearchType As String
     Dim vSearchTerm As String
     Dim vSQL As String

    if(ValidateInput) Then  
        vMediaTitleSearchType = Me.MediaTitleSearchType
        Select Case Me.MediaTitleSearchType
        Case "Title"
             vSearchTerm = Me.SearchTerm
             vSQL = "Title LIKE '*" & vSearchTerm & "*'"                    
             DoCmd.Close         'Close form frmSearchMediaTitles
             DoCmd.OpenForm "frmTitles", , , vSQL
         End Select
    End If
End Sub

Open in new window

Side note:
Cept for error handlers, avoid those horribles GoTo.

Fred Fisher

ASKER

Thanks for the assistance. With some minor alterations I now have this error code working. Thanks for the assistance!