Multi-Variable Search List Box

[SLS_GIS]

I have a simple database that contains Job data. Each Job has an individual Job Number and various other fields such as Client, Name, State, County, District, etc. As of right now, I have an unbound search that returns results in a List Box. For example, if I type "Smith", it searches every field, as I type, and returns everything that contains "Smith". It does this as I type, so it narrows my results the more I type. I like this ability, but I want to narrow my results more by other fields. I want all results containing "Smith", but are only in a certain "County" or may be in a certain "State" and "County" or so on. How do I do this? I got the code I'm using now online and it works great for one keyword or phrase, but I need to go further. Thanks.

 

[CJ_London]

not sure if it meets your 'vision' but you can modify the code based on a user entering a comma to split the user string

dim arr() as string
arr()=split(mytxtbox.text,",")
then modify your code to search on each field for each element

or just have a separate text/listbox for county

 

[SLS_GIS]

Here is the code I'm using. I'm still new at code, so I don't have a total grasp of what it all means. Some of my cells contain commas, so would your suggestion work in that case? Thanks.

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

Private Sub Text8_Change()

End Sub

 

[CJ_London]

sorry, struggling to read your code - please repost using the code tags to preserve indentation

 

[SLS_GIS]

Hopefully this makes it more clear. Thanks again.



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

Private Sub Text8_Change()

End Sub

 

[CJ_London]

a bit different, but suspect not - all depends on how your rowsource for your Me.SearchResults is constructed

my suggestion was based on building the rowsource in vba

i.e. build a string

SearchResults.rowsource="SELECT * FROM myTable WHERE keyfield=" & SearchFor.Text

no need for hidden fields, much less code and by the way you do not need .value, it is the default returned

I suspect your current setup is something like

SELECT * FROM myTable WHERE keyfield=[srchtext]

or a query which references the form srchtext control as a parameter.