Dynamically search multiple fields 2 list boxes ?

[murray83]

hi all

came across the great piece of work https://access-programmers.co.uk/forums/showthread.php?t=188663but it only filters one list box

i have tried to make it work on 2 by just adding another list box and adding the name of that box to the code so for instance this is the vanilla code

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 search 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
        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

and then i added this line in here

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 search 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
    Me.SearchResults2.Requery ( my extra list box )

'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
        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

but it dosnet filter any ideas would be grand

cheers

 

[June7]

This code is in Access VBA? Don't use .Text, use .Value.

Is the first listbox showing data correctly?

What is the RowSource property of each listbox?

 

[murray83]

This code is in Access VBA? Don't use .Text, use .Value.

Is the first listbox showing data correctly?

What is the RowSource property of each listbox?

the .text works

the list box does show data correct yes but when i want to run it for a second list box nothing happens for the second one

 

[murray83]

OK i have figured it out but have one, final question i hope

attached is the database i'm working on, what i don't understand is the PC Login list box doesn't filter down if i type in Tomas for instance where as the others do.

why would that be

cheers all

 

[murray83]

ignore the previous just clicked been a massive tool i forgot to add in the criteria in the query d'oh

 

[murray83]

this code is great but when i search for a colleague whos name starts with "I" i need to type in capital unless it thros up the following error.

Run-Time error '2110,:

Microsoft Access can't move the focus to the control SearchResults

is due to the computer mistaking the lower case "i" for a 1 ??

if so how would i rectify

cheers

 

[June7]

That is weird and found solution in another thread on this forum via Google search. Change the Allow AutoCorrect property of the SearchFor textbox to No.

 

[murray83]

that was easy many thanks

thought i was going to have to code some exception