Dynamic multiple fields search - list box

[accesslearner]

Dynamically search multiple fields

I have got this code and used it to create a search filter.
It works but for the first item. How can i create a loop to work for all the items on the list box.


'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 IfMy list box contains values for company name contact name , state etc.

Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus
it filters for the first list item. could any one help.

 

[pbaldy]

Something went wrong with your formatting, and it's very difficult to read that code. This is one way to filter a report:

http://www.baldyweb.com/multiselect.htm

You may be able to adapt it to what you're doing. If not, repost or reformat that code so we can read it.

 

[accesslearner]

sorry about that

i will try to repost

Private Sub SearchFor_Change()
'Create a string (text) variable
Dim vSearchString As String
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
Dim stLinkCriteria As String
Dim sqlstr As String

Dim counter As Integer
Dim varElement As Variant

'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
Exit Sub
End If
'Set the focus on the first item in the list box
If Me.SearchResults.ItemsSelected.Count = 0 Then
MsgBox "select one item"
'Exit Sub
End If
Set ctl = Forms!Frm_SearchMulti!SearchResults ' this is the listbox
counter = 1
If ctl.ItemsSelected.Count = 0 Then
MsgBox "please select form the list"
Me.SearchResults.SetFocus
'Exit Sub
End If
For Each varElement In ctl.ItemsSelected
If counter > 1 Then
sqlstr = sqlstr & "OR"
End If
sqlstr = sqlstr & "[Item Number]=" & ctl.ItemData(varElement)
counter = counter + 1
Next varElement
'MsgBox sqlstr

stLinkCriteria = sqlstr
stDocName = "Frm_SearchMulti"

' 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

I used the following steps to create the search field

I have a query called Qry_SearchAll

in the criteria of the feild1 i am searching i have
Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"
to filter feilds 2, 3 i have copied this code in the "OR"

SrchText is a text box - visible property set to no

when i type into this text box the list box feilds are narrowed which is what i want.

But it only works for Feild no 1 I figured it is because of this code
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus

How to make the text box read 1,2,3 values of the list box.

I tried to loop through the list box but i dont think i am getting the logic can anyone please help.
I want to be able to filter for any of the feilds in the list box.

 

[accesslearner]

I have found the problem in

in the criteria of the feild1 i am searching i have
Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"
to filter feilds 2, 3 i have copied this code in the "OR"


i copied this code in a cascading sequence like

field 1 - in criteria
feild 2- in OR
feild 3 - in the next line below or and so on and it worked.

I still have to figure out how to use a list box.

i do not want to populate the list box since it is having values from the table.

i want to click on the first name in the list box and the cboname only should be displayed all other controls should be hidden and when i click on company the company combo should be displayed.. i have attached a jpg file .

i would appreciate if someone could help i seem to not know how to start my statement i have tried different options.

thanks

 

[pbaldy]

I would be more likely to do something like in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

That said, if you want to control the visibility with the listbox, you'd first make them all invisible and then (I assume you want multiselect):

For Each varElement In ctl.ItemsSelected
  Me(ctl.Column(1, varElement)).Visible = True
End If

which assumes the listbox has a column which has the actual name of the controls you want to make visible. You'd replace 1 with the appropriate column in your listbox. If you don't have that, you'd have to use the tag property or something. Somehow you have to be able to relate the choice in the listbox with the name of a combo.

 

[Rick_A]

I'm not sure what you're up to with this, but it sounds like you're gonna want some code on the click event of the list box. To get the value that was selected in the list use:

lstListBox.ItemData(lstListBox.ListIndex)

Assign this to a string and then do a select case, i.e.

Select Case strChoice
Case "First Name"
'Make control visible, others invisible
Case "Blah"
'So on and so forth
End Select

 

[accesslearner]

Private Sub listsearch_Click()
Dim strchoice As String
strchoice = listsearch.ItemData(listsearch.ListIndex)
Debug.Print (strchoice)

Select Case strchoice
Case [listsearch.Listindex] = 1
cbostate.Visible = False
cbotitle.Visible = False
cbocompany.Visible = False
'Make control visible, others invisible
'So on and so forth
End Select
End Sub

The index is reading correctly but how to refer to "first Name" etc

i am getting a run time error 2465

thanks for your time

 

[accesslearner]

Thanks again Rick

Well the bliss of being a learner you always feel like a door knob.


I got the result i need thanks once again.

Private Sub listsearch_Click()
Dim strchoice As String
strchoice = listsearch.ItemData(listsearch.ListIndex)
Debug.Print (strchoice)

Select Case strchoice
Case 1
cbostate.Visible = False
cbotitle.Visible = False
cbocompany.Visible = False
cboname.Visible = True
'Make control visible, others invisible
Case 4
cbostate.Visible = False
cbotitle.Visible = True
cboname.Visible = False
cbocompany.Visible = False