Need my msgbox that warns user of missing criteria to stop the rest of the code from running

[qqp]

Hi!

I have a filter form to run a report, there are 2 reports that can be printed. Summary and Details. Right now on the summary button, I have put a code to be sure selections have been made in both list boxes (used for the report filter) and it will pop a message telling the user they need to choose something, at this point, I want the code to stop running because it will produce an error without the criteria. I have tried docmd.cancelevent, and cancel=true. But it keeps running until the error pops when it tries to open the report with no criteria. Thank you in advance for any advice you can give me!! PEACE.

Private Sub Summary_Click()
On Error GoTo Err_Handler
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    'Purpose:  Open the report filtered to the items selected in the list box.
    Dim varItem As Variant      'Selected items status
    Dim varItem2 As Variant      'Selected items mfg
    Dim strWhere As String      'String to use as WhereCondition status
    Dim strWhere2 As String      'String to use as WhereCondition mfg
    Dim lngLen As Long          'Length of string status
    Dim lngLen2 As Long          'Length of string mfg
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.first report
    Dim strDoc2 As String        'Name of report to open.second report
    Dim strcriteria As String   'Combine strings
    
    
    strDelim = """"            'Delimiter appropriate to field type.
    strDoc = "BidReportbyMfrSummary"
    strDoc2 = "secondBidReportbyMfrSummary"

    'Loop through the ItemsSelected in the list box.
    With Me.BidStatus
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'Build up the description from the text in the visible column. See note 2.
                'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
    
        With Me.MFG
        For Each varItem2 In .ItemsSelected
            If Not IsNull(varItem2) Then
                'Build up the filter from the bound column.
                strWhere2 = strWhere2 & strDelim & .ItemData(varItem2) & strDelim & ","
            End If
        Next
    End With
    
    
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "[bidstatus] IN (" & Left$(strWhere, lngLen) & ")"
        'lngLen = Len(strDescrip) - 2
'        If lngLen > 0 Then
'            strDescrip = "Status: " & Left$(strDescrip, lngLen)
'        End If
Debug.Print "1st String " & strWhere

    End If
    
    lngLen2 = Len(strWhere2) - 1
    If lngLen > 0 Then
        strWhere2 = "[mfg] IN (" & Left$(strWhere2, lngLen2) & ")"
        End If

Debug.Print "MFG " & strWhere2

  
      strcriteria = strWhere & "and " & strWhere2

Debug.Print "Strcriteria " & strcriteria

If Me.BidStatus.ItemsSelected.Count = 0 Then


Msg = "Please make a selection in the Bid Status field"
Style = vbOKOnly
Me.BidStatus.SetFocus

Title = "Missing Criteria"   

Response = Msgbox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then    ' User chose Yes.
    MyString = "Yes"    ' Perform some action.
End If

End if


DoCmd.CancelEvent


    DoCmd.Openreport strDoc, acViewPreview, WhereCondition:=strcriteria
    DoCmd.Openreport strDoc2, acViewPreview, WhereCondition:=strcriteria


Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        Msgbox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
    End If
    Resume Exit_Handler
End Sub

 

[Andrzejek]

Have you consider having this at the top of your code:

If Me.BidStatus.ItemsSelected.Count = 0 Then
    MsgBox "You need to select something in Bid Status."
    Exit Sub
End If
...

And you can do the same for the other listbox before you run the rest of your code..

BTW - your Msg, Style, Title, Help, Ctxt, Response, MyString are all Variants.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Are you sure [red]that's[/red] what you want to do here...?

...[blue]
    lngLen2[/blue] = Len(strWhere2) - 1
    If [red]lngLen[/red] > 0 Then
        strWhere2 = "[mfg] IN (" & Left$(strWhere2, [blue]lngLen2[/blue]) & ")"
    End If
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson