Handling a WHERE condition that returns no records ?

[Spenney]

I have a form that allows a user to set certain criteria before running a report. The code that runs the report is below and works satisfactorily.

Private Sub cmdOK_Click()

    Dim strDocName As String
    Dim strCriteria As String
    
    strDocName = "rptIndividual_Base_Data"
    
    'Build WHERE condition depending on the user's preference
    Select Case Me.fraCriteria
    Case 2 'Individual
        strCriteria = "[individual_id] = " & Me.cboIndividual.Column(0)
    Case 3 'Business Unit
        strCriteria = "[business_unit_id] = " & Me.cboBU.Column(0)
    Case 4 'Priority Group
        strCriteria = "[priority_group] = " & Me.cboPriority.Column(0)
    End Select
        
    DoCmd.OpenReport strDocName, acViewPreview, , strCriteria
    
End Sub

Though the criteria is selected from 'Limit to List' Combo boxes, they show all of the potential choices rather than just those with associated data. I'd like to keep it this way and handle the result rather than limit the row source of the Combo Box to known existing data.

My question is does anybody have any tips for handling a report that has no data that satisfies the user's criteria ?

 

[lupins46]

A report has a 'No Data' event.
You can use the No Data event procedure to display a message to the user and cancel production of the report.

 

[Spenney]

Just the job - thank you. Funny how the simple things are the ones you (well, me in this case) never notice.