Update List box on Combo Box change

[geotrouvetout67]

I have a table (tblReports) containing basically two fields [PurchaseCategory] and [ReportName]. Each report is associated with a purchase category

In a form I have a List Box (lstReports) that is displaying all the reports available and corresponding purchase categories which display on Form_Load()

I also have a Combo Box called cboPurchaseCat. When I select the purchase category form that box, I want the List Box to update with showing only the reports for the selected category. Instead it returns blanks and I can't figure out why.

Here's the code:

Private Sub FilterReportsList()

Dim strReports As String

strReports = "SELECT [ReportName], [PurchaseCategory] FROM tblReports ORDER BY [ReportName]"

If Not IsNull(Me.cboPurchaseCat) Then
    strReports = strReports & " WHERE PurchaseCategory = " & Me.cboPurchaseCat
End If

Me.lstReports.RowSource = strReports


End Sub

Private Sub Form_Load()
FilterReportsList
End Sub

Private Sub cboPurchaseCat_Change()
FilterReportsList
End Sub

[June7]

WHERE clause goes before ORDER BY.

strReports = "SELECT [ReportName], [PurchaseCategory] FROM tblReports " & IIf(IsNull(Me.cboPurchaseCat), "", " WHERE PurchaseCategory = '" & Me.cboPurchaseCat & "'") & " ORDER BY [ReportName]"

This assumes PurchaseCategory is a text type field.

[geotrouvetout67]

Thank you so much, indeed that fixed it.