OnOpen even cancelled error is persisting after event has failed.

[Monterey_Manzer]

Hi All,

I am having a problem with some new filtering code for one of my forms. I am doing two layers of filtering, by In Progress records and then by administrator, and for some reason my standard capture for a user pressing the Cancel button in the Input Box (2nd filter) is causing a really weird error. The code runs for a little while and then I get a msgbox popping up telling me the OpenObject action was canceled. Now if that was the full extent of the issue this wouldn't be such a problem. However, this "OpenObject cancelled" error is now the only response when I click on the button to open the form. I would appreciate anybody's ideas for how I can trap this error and prevent it from wrecking my dBase operation.

Most of the code involved is in the Form On Open event. However, I will first post the button click event that triggers the opening of the form.

Button Click (I keep these pretty simple and put all the complex stuff, including error trapping in the On Open event):

Private Sub Change_Requestbtn_Click()
    DoCmd.OpenForm "Change Request Tracker", acNormal
End Sub

That's pretty straight forward, I can't see that being where the problem is. However, when your debugger lights up due to some coding problems that is always where it wants to go.

Alright, here is the code I wrote to allow users to filter the record source by whether a request in In Progress or not and then by which administrator it is assigned to. I suspect this is where my error is but I can't seem to pin it down.

Private Sub Form_Open(Cancel As Integer)
    Dim strCA As String, strSQL As String, strWHERE As String, strAND, Msg, Title, Style, Response
    On Error GoTo ErrHandler
    
    strSQL = "SELECT CRTracker.*" & _
    "FROM PartInfo INNER JOIN CRTracker ON PartInfo.[Smart Id] = CRTracker.[SMART ID]"

 ' Determining if the user wants to view historical or only active requests
        Msg = "View Only Requests In Progress?"    ' Define message.
         Style = vbYesNo + vbQuestion + vbDefaultButton2    ' Define buttons.
         Title = "Which Records to View?"    ' Define title.
         ' Display message.
         Response = MsgBox(Msg, Style, Title)
        If Response = vbYes Then    ' User chose Yes.
           strWHERE = " WHERE CRTracker.[Request In Progress?] Like 'Yes'"
Repeat:
                strCA = InputBox("Enter the CA for the paricipants you wish to view" & vbCr & vbCr & _
                        Chr(9) & "VP, LM, KR, SH, or JS" & vbCr  & "Or press * to view all participants", "Show All/Filter")
                        
               Select Case strCA
                Case "VP"
                    strAND = " AND PartInfo.CA Like 'VP';"

                Case "LM"
                    strAND = " AND PartInfo.CA Like 'LM';"
                    
                Case "KR"
                    strAND = " AND PartInfo.CA Like 'KR';"
                    
                Case "SH"
                    strAND = " AND PartInfo.CA Like 'SH';"
                    
                Case "JS"
                    strAND = " AND PartInfo.CA Like 'JS';"
                    
                Case "*"
                    strAND = ";"
                Case ""
                    GoTo MyExit
                Case Else
                    MsgBox "I'm sorry I must have misheard you.  Could  you please enter your selection again?", vbOKOnly, "My Mistake"
                    GoTo Repeat
                End Select

         Else    ' User chose No.
Repeat2:
                strCA = InputBox("Enter the CA for the paricipants you wish to view" & vbCr & vbCr & _
                        Chr(9) & "VP, LM, KR, SH, or JS" & vbCr  & "Or press * to view all participants", "Show All/Filter")
                        
               Select Case strCA
                Case "VP"
                    strWHERE = " WHERE PartInfo.CA Like 'VP';"
                    strAND = ""
                Case "LM"
                    strWHERE = " WHERE PartInfo.CA Like 'LM';"
                    strAND = ""
                Case "KR"
                    strWHERE = " WHERE PartInfo.CA Like 'KR';"
                    strAND = ""
                Case "SH"
                    strWHERE = " WHERE PartInfo.CA Like 'SH';"
                    strAND = ""
                Case "JS"
                    strWHERE = " WHERE PartInfo.CA Like 'JS';"
                    strAND = ""
                Case "*"
                    strWHERE = ";"
                    strAND = ""
                Case ""
                    GoTo MyExit
                Case Else
                    MsgBox "I'm sorry I must have misheard you.  Could  you please enter your selection again?", vbOKOnly, "My Mistake"
                    GoTo Repeat2
                End Select
         End If
MyExit:
    Cancel = True
    ' Setting Form record source based on above selections
    Me.RecordSource = strSQL & strWHERE & strAND
    Exit Sub
ErrHandler:
    If Err.Number = 2501 Then
        MsgBox "No Selection Made - Form Open canceled", , "Form Open Canceled"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
End Sub

Ssnafu, you might find something familiar about that MsgBox code. Thanks again for the help earlier!

So....there it is. If anyone has any ideas what could be screwing me up I would greatly appreciate it.

Thanks!

[Monterey_Manzer]

Oddly enough, after shutting down, compacting and repair, now clicking the button allows me to go through both filtering steps but the code ends with an "Error 2501: OpenObject cancelled" error.

[Missinglinq]

I suspect that the problem, here, is that the Form_Open event is too early to be trying to do this sort of thing! Try moving your code to the Form_Load event, instead, and see what happens. Remember to delete it from the Form_Open!

Linq ;0)>