Search function using option frames (1 Viewer)

[nick5196]

Hi, i am using the code created by "John Big Booty" to search a list box. I would like to add some option frames which will further filter the list box.

I am using this code

'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

I have mimiced John's query and form coding but cannot seem to get the same result.
This is a copy of his DB.

I am naming the field with the same name as the crietria entered into the query. But the reult is either nothing, or all of the records in the listbox disapperaring.

Thanks a lot, just wondered if there was something missing

 

[John Big Booty]

First up, have you checked the code behind on the On Click event of the option group in the sample.

Secondly have you placed the criteria;

Like "*" & [forms]![FRM_FormName]![YourFrameName] & "*"

in the appropriate grid in the query, be aware that you will need to put this criteria in all lines of the grid to align with the other criteria, check the query in the sample.

 

[nick5196]

Yes i have used that code within the criteria and have used the on click event for the option frame.

Thats why im so puzzled!

Thanks

 

[John Big Booty]

Can you post a copy of your DB?

 

[nick5196]

sorry for the late reply, i was at school and did not have a copy with me.
The search function was operational however, after adding the option frames, it is now not.

Basically I wish to search for an "employer", or student first and last name, ID, and subject. I would like the "course" and "college day" to be an option frame selector.

I really appreciate your help...the form containing the search and listbox should open automatically (switchboard) and the query is "QRY_SearchAll".

Thanks again

 

[John Big Booty]

OK the problem is that an Option Group will generally return a numeric value however you are comparing it with a text string. Now this is further complicated by the fact that you are using a Table Level lookup

You might be able to compare your option group and your day text names with your option group by using the option group result with the WeekdayName() function

 

[nick5196]

Ahhh, i see. How would I implement this within my DB?

 

[John Big Booty]

I'd us the WeekdayName() function to create an expression (in your query) that you can then compare with your option group i.e. put the criteria for the option group under it; You can set the column width to zero as you won't need to show it in the listbox.