Canceling a report filter without Run-time error

[Monterey_Manzer]

Hello Everyone,

So here's my problem. I have a form and report that are used by multiple coworkers who all track specific issues with specifically assigned customers. Each customer has, as part of their record, their assigned administrator and I've got some simple code that allows each administrator to filter the records present in either the form or the report to just their customers. It works beautifully and I am very happy with it, unless users decide they don't want to look at that form or report. If a user does not enter anything or presses the Cancel button on the InputBox, they get a run-time error '2501'. Can I simply have the result be that the form/report does not open?



The code for this feature in the form is shown below (the code is pretty much the same in the report except for a much longer SQL statement):

Private Sub Form_Open(Cancel As Integer)
    Dim strCA As String, strSQL As String, strWHERE As String
    On Error GoTo ErrHandler
    strSQL = "SELECT * FROM GandHTracker"
    
    strCA = InputBox("Enter the CA for the paricipants you wish to view" & vbCr & vbCr & _
            "VP, LM, KR, SH, or JS" & vbCr & "Or press * to view all participants", "Show All/Filter")
            
    If strCA = "" Then
        Cancel = True
    ElseIf strCA = "*" Then
        Me.RecordSource = strSQL
    Else
        strWHERE = " WHERE CA Like '" & strCA & "';"
        Debug.Print strSQL
        Debug.Print strWHERE
        Me.RecordSource = strSQL & strWHERE
    End If
    Exit Sub
ErrHandler:
    MsgBox Err.Number & ":  " & Err.Description
End Sub

Any thoughts/suggestions would be appreciated. Thanks!

[Bob Fitz]

I would assume that the form is being opened from some other code, like the On Click event of a button. If this is true, then the error message will be generated in the error handling code of that button. So you could deal with it there.

[Bob Fitz]

The attached db shows how to handle the error.

[Monterey_Manzer]

Thanks! I tweaked the code a little to provide a message box notifying the user that no valid selection was made. It works perfectly.