Listbox Report search filter issue

[Sketchin]

I have no idea how to title this problem, and I hope I do a better job explaining it.

I have a form containing a listbox based on a query that shows a list of reports that are in the database. Included on this form is a search textbox that filters the results in the listbox as you type. My problem is that I also have an option group that allows you to filter reports based on 3 different criteria. It does this using a select case statement and changing the recordsource of the listbox to an SQL string, meaning that the listbox is no longer based on my query. Because of this, the search textbox no longer works after selecting anything in the option group because the textbox is looking for results from a query...not from the new SQL string.

To clarigy, if I open the form and only search for text, everything is fine. The minute I filter with the option group, the text search no longer works.

My question is whether or not there is anything I can do to fix this problem?

Code for option group:

Private Sub OptionReportFilter_Click()

    Dim sqlQry As String
    sqlQry = "SELECT tblPrograms_Reports.ProgramsReportID, tblPrograms_Reports.AssociatedProgram, tblPrograms_Reports.Description" & _
    ", tblPrograms_Reports.ReportName, tblPrograms_Reports.AcceptedProjects, tblPrograms_Reports.AllProjects " & _
    "FROM tblPrograms_Reports"
         
    Select Case Me.OptionReportFilter
   
        Case 1
            sqlQry = sqlQry & " WHERE AcceptedProjects = True"
        Case 2
            sqlQry = sqlQry & " WHERE AllProjects = True"
       
    End Select
    Me.SearchResults.RowSource = sqlQry
    '& "ORDER BY tblPrograms_Reports.AssociatedProgram"
    Me.SearchResults.Requery
        
End Sub

Code for search box:

Private Sub searchFor_Change()
'Create a string (text) variable
    Dim vSearchString As String
        
'Populate the string variable with the text entered in the Text Box SearchFor
    vSearchString = searchFor.Text

'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
    SrchText.Value = vSearchString

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
    Me.SearchResults.Requery


'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        'Set the focus on the first item in the list box
            Me.SearchResults = Me.SearchResults.ItemData(1)
            Me.SearchResults.SetFocus
        'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
            DoCmd.Requery
        'Returns the cursor to the the end of the text in Text Box SearchFor,
        'and restores trailing space lost when focus is shifted to the list box
            Me.searchFor = vSearchString
            Me.searchFor.SetFocus
            Me.searchFor.SelStart = Me.searchFor.SelLength
            
        Exit Sub
    End If

'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
    DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box SearchFor
    Me.searchFor.SetFocus

    If Not IsNull(Len(Me.searchFor)) Then
        Me.searchFor.SelStart = Len(Me.searchFor)
    End If

End Sub

btw...the code for the search box was courtesy of John Big Booty.

 

[bob fitz]

Can you show us the SQL of the listbox (or the query that is used to populate it)

 

[Sketchin]

Im sure I am not posting this string in the correct format, but please forgive me because I can never remember the best way to do this.

Thanks for taking a look.

SELECT TblPrograms_Reports.ProgramsReportID,
tblPrograms_Reports.AssociatedProgram, 
tblPrograms_Reports.Description, 
tblPrograms_Reports.ReportName, 
tblPrograms_Reports.AcceptedProjects, 
tblPrograms_Reports.AllProjects
FROM tblPrograms_Reports
WHERE (((tblPrograms_Reports.AssociatedProgram) Like "*" & 
[forms]![frmProjects_Reports_V2]![SrchText] & "*") AND ((tblPrograms_Reports.Active)=True)) OR 
(((tblPrograms_Reports.Description) Like "*" & 
[forms]![frmProjects_Reports_V2]![SrchText] & "*"));

 

[Sketchin]

Come to think of it.... if I add the following code to the where clause of my listbox filter, the search box may still work.

 Select Case Me.OptionReportFilter
   
        Case 1
            sqlQry = sqlQry & " WHERE AcceptedProjects = True"
        Case 2
            sqlQry = sqlQry & " WHERE AllProjects = True"

'Add this additional where clause to the end of the previous case statements.

WHERE (((tblPrograms_Reports.AssociatedProgram) Like "*" & 
[forms]![frmProjects_Reports_V2]![SrchText] & "*") AND ((tblPrograms_Reports.Active)=True)) OR 
(((tblPrograms_Reports.Description) Like "*" & 
[forms]![frmProjects_Reports_V2]![SrchText] & "*"));

Now....as to how you add this to the end of my current sqlQRY where clause...thats another problem.

 

[Sketchin]

So I've gotten to this point and get a runtime erorr 13, data type mismatch.

 Dim sqlQry As String
    Dim Srchtext As String
    
    Srchtext = Nz(Me.Srchtext.Value)
    
    
    sqlQry = "SELECT tblPrograms_Reports.ProgramsReportID, tblPrograms_Reports.AssociatedProgram, tblPrograms_Reports.Description" & _
", tblPrograms_Reports.ReportName, tblPrograms_Reports.AcceptedProjects, tblPrograms_Reports.AllProjects " & _
    "FROM tblPrograms_Reports"
           
    Select Case Me.OptionReportFilter
   
        Case 1
            sqlQry = sqlQry & " WHERE AcceptedProjects = True AND   " & _
                                [COLOR="Red"]"tblPrograms_Reports.AssociatedProgram Like " * " & SrchText & " * " " & _[/COLOR]
                                "AND tblPrograms_Reports.Active = True" & _
                                "[COLOR="red"]OR tblPrograms_Reports.Description Like " * " & SrchText & " * " "[/COLOR]

The error is in the criteria in red.

Any ideas?