ListBox with search criteria?

[d9pierce1]

Hi all,


I have a unbound form with an unbound listbox on it that is populated from a union qry.
Below is the folling code i am trying to use to get it to search if I enter something in the above txt boxes.
It requeries when i enter like R in the firstname but I still get all records showing?
Its just sample data and i just cannot for the life of me figure out why this isnt populating the list box with the search
Criteria? Also on after update on all the filter txt boxes i have =FilterPerson() so this should trigger the filter click
This should work. I have it in one of my old db's and it works just fine.... Not sure what I am doing wrong.
Please do assist....Thanks
Dave

Option Compare Database
Option Explicit


Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form , and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form  section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acOptionGroup
            ctl = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
    FilterPerson
End Sub


Private Sub cmdFilter_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Dim s As String
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
    
End Sub


Private Sub Form_Load()
'not bound -- no data is loaded
   cmdReset_Click
End Sub


Private Function FilterPerson()
   'this is the main procedure for filtering
    Dim strWhere As String
    Dim s As String
    
    s = "SELECT PersonID, FirstName, LastName, Association, IsActive From qryPersonSearch;"
    
   If Not IsNull(Me.FilterFirstName) Then
      strWhere = strWhere & "([FirstName] LIKE ""*" & Me.FilterFirstName & "*"") And "
   End If


   If Not IsNull(Me.FilterLastName) Then
      strWhere = strWhere & "([LastName] LIKE ""*" & Me.FilterLastName & "*"") And "
   End If


   If Not IsNull(Me.FilterAssociation) Then
      strWhere = strWhere & "([Association] LIKE ""*" & Me.FilterAssociation & "*"") And "
   End If
   
    Debug.Print s


   
    Me.LstPersonSearch.RowSource = s
    Me.LstPersonSearch.Requery
    
End Function

I have tried this code also but no difference in results?

Private Function FilterPerson()
   'this is the main procedure for filtering
   
   Dim s As String, mWhere As String
	s = "SELECT PersonID, FirstName, LastName, Association, IsActive From qryPersonSearch;"
      
    
   If Not IsNull(Me.FilterFirstName) Then
      mWhere = (mWhere & IIf(Len(mWhere) > 0, " AND ", "")) & "FirstName LIKE '*" & Me.FilterFirstName & "*'"
   End If


   If Not IsNull(Me.FilterLastName) Then
      mWhere = (mWhere & IIf(Len(mWhere) > 0, " AND ", "")) & "LastName LIKE '*" & Me.FilterLastName & "*'"
   End If


   If Not IsNull(Me.FilterAssociation) Then
      mWhere = (mWhere & IIf(Len(mWhere) > 0, " AND ", "")) & "Association LIKE '*" & Me.FilterAssociation & "*'"
   End If
   
Debug.Print s


    Me.LstPersonSearch.RowSource = s
    Me.LstPersonSearch.Requery
    
End Function


...........................................................


Private Sub Form_Load()
'not bound -- no data is loaded
   cmdReset_Click
End Sub


............................................................


Private Sub cmdFilter_Click()
    
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
    
End Sub


.......................................................................


Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form , and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form  section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acOptionGroup
            ctl = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    'Me.grpCompanyFilter = 27
    
    'Remove the form's filter.
    Me.FilterOn = False
    FilterPerson
End Sub

[June7]

That's a lot of code to read through. I would rather have db and debug.

Speaking of debugging, have you? Set breakpoint and step through code.

[d9pierce1]

Tempdb.zip

Here is a temp db to work with
Thanks
Dave

[Welshgasman]

You have no WHERE ? in the last code shown

The debug.print would show that?

A filter statement does not use the WHERE, but SQL does?

[June7]

Need to concatenate s and strWhere variables but remove the semi-colon.

s = "SELECT PersonID, FirstName, LastName, Association, IsActive FROM qryPersonSearch "

s = s & strWhere

Debug.Print s

Need an AND operator between each criteria. For example of handling that, review http://allenbrowne.com/ser-62.html

[ssanfu]

You might review the design of the table table "tblPerson"; I would say it is not normalized because you have repeating fields (which is why you have to use a Union query for the List box Row Source.

Firstname	sFirstName
MiddleName	sMiddleName
LastName	sLastName
UseMaiden	sUseMaiden
UseMaidenLast	sUseMaidenLast
MaidenName	sMaidenName

And what about the fields "Created" and "Modified"? What part of the record is Modified? For the FirstName group (6 fields) or the sFirstname group (6 fields)?


At first I wasn't sure what you wanted to search - the form or the listbox. I guessed the list box (because the form is unbound ) Duh!!

So see if this is what you were trying to do..... ( the filter button has no code because the form is unbound - the code didn't do anything....)

[d9pierce1]

Hi ssanfu,
I do like those changes you made! Very nice indeed.
I have two questions if you dont mind,

1. If I were to add the checkbox "IsActive" to the filter, how would I specify that in the code? I cant find an example of that.
How would I wrght the code to use a check box in the filter section?

Such as like the following except a check box!

   'check FilterFirstName   'FilterIsActive to replace FilterFirstName
    Forms!form1.FilterFirstName.SetFocus
    If Len(Forms!form1.FilterFirstName.Text) & "" > 0 Then
      strWhere = strWhere & "([FirstName] LIKE ""*" & Forms!form1.FilterFirstName.Text & "*"") And "
    End If

2. If I were to have a search form with say two check boxes and I wanted one to be true, and one to be false on reset, is that possible in the Case portion of reset code?
I know I dont have two check boxes in this form but i do in another which i would like to know if possible to keep one True, and one False ?

Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form , and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acOptionGroup
            ctl = Null
        Case acCheckBox
            ctl.Value = False  'IsActive=True, IsPreferred=False (I would assume it would go like? ctl.IsActive.Value = True and ctl.IsPreferred.Value = False)
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
    FilterPerson
End Sub

[d9pierce1]

ssanfu,
This is the form for that person table and may explain the Maiden and such...
The Created and Modified are the time stamp file was created and if modified, the time stamp for that in before update

[ssanfu]

This is the form for that person table and may explain the Maiden and such...
The Created and Modified are the time stamp file was created and if modified, the time stamp for that in before update

I might have designed this a little different, but......if it works for you .

As far as the 2nd boolean field, I think I got it. Had to change a few things. Maybe a little more than a few.

I renamed the function in Module1 (added an "f" prefix )and added "Call" ( 2 places in the "Change event code)

Play with it ........ tear it apart... maybe it will help (or maybe not...)

In the list box, the boolean fields were displayed as 0/-1. It took a while, but I finally got 'No'/'Yes' to display *AND* was still able to get the filtering to work!!

[d9pierce1]

Hi ssanfu,
Thank you so much, I have tested it out and appriciate you help... These search things are a royal pain to me.
I only found one issue and it is where if you uncheck IsActive in table for record 2, then it wont filter the form to show the not active persons....?
I will play with it as the IsPreferred works just wonderful... Not sure why the IsActive wont show files that are non active?
But in any event, thank you so much
Dave

[ssanfu]

I missed that.

Stupid computer does what I tell it to do, not what I want it to do!

OK, try changing the following section of code in "Function fListBoxFilter()" to

   'check chkIsActive
    If Forms!form1.chkIsActive = True Then
      strWhere = strWhere & "([Active] = 'Yes') And "
    End If

   'check IsPreferred
    If Forms!form1.chkIsPreferred = True Then
      strWhere = strWhere & "([Preferred] = 'Yes') And "
    End If

    'chop off the last 5 characters
    lngLen = Len(strWhere) - 5
    If lngLen > 0 Then    '<<-- if  Len of strWHERE is > 0, then there must be a filter to apply
        strWhere = Left(strWhere, lngLen)
        strWhere = " WHERE " & strWhere  ' add "WHERE to the filter string (strWHERE)
    End If
    
'    Debug.Print Len(strWhere)
'    Debug.Print s
'    Debug.Print s & strWhere
    
    Forms!form1.LstPersonSearch.RowSource = s & strWhere
'    Me.LstPersonSearch.Requery
    
End Function

[d9pierce1]

Thank you ssanfu
That is just awsome!
I would have never figured that out on my own....
Again, Thank you and will mark as solved!
Dave

[d9pierce1]

TempTestNew.zip

Hi all,
I had this marked as solved but changed that as I ran into an issue with a cbo in the search function.
ssanfu was kind enough to create this for me and worked like a charm...
I am using it as a template for other search criteria and i cannot figure out the Cbo... Questionalble code is in red below
I am sure it is something with the way i have wwritten the criteria for that but have tried many mehods and nothing has success yet....
I didnt have a cbo in the origional so trying to figure out how to add the cbo filter code.
Please assist me if you would be so kind

Option Compare Database
Option Explicit


'row source for listbox
Public Const s As String = "SELECT PersonID, FirstName, LastName, Association, Active From qryPersonSearch"


Function fListBoxFilter()
   'this is the main procedure for filtering
    Dim strWhere As String
'    Dim s As String
    Dim lngLen As Long
   
   'check FilterFirstName
    Forms!form1.FilterFirstName.SetFocus
    If Len(Forms!form1.FilterFirstName.Text) & "" > 0 Then
      strWhere = strWhere & "([FirstName] LIKE ""*" & Forms!form1.FilterFirstName.Text & "*"") And "
    End If
  
   'check FilterLastName
    Forms!form1.FilterLastName.SetFocus
    If Len(Forms!form1.FilterLastName.Text) & "" > 0 Then
      strWhere = strWhere & "([LastName] LIKE ""*" & Forms!form1.FilterLastName.Text & "*"") And "
    End If


   'check cboFilterAssociation
    Forms!form1.cboFilterAssociation.SetFocus
    If Len(Forms!form1.cboFilterAssociation.Value) & "" > 0 Then
      strWhere = strWhere & "([Association] = Forms!form1.cboFilterAssociation.Value) And "
    End If


   'check chkIsActive
    If Forms!form1.chkIsActive = True Then
      strWhere = strWhere & "([Active] = 'Yes') And "
    End If


    'chop off the last 5 characters
    lngLen = Len(strWhere) - 5
    If lngLen > 0 Then    '<<-- if  Len of strWHERE is > 0, then there must be a filter to apply
        strWhere = Left(strWhere, lngLen)
        strWhere = " WHERE " & strWhere  ' add "WHERE to the filter string (strWHERE)
    End If
    
'    Debug.Print Len(strWhere)
'    Debug.Print s
'    Debug.Print s & strWhere
    
    Forms!form1.LstPersonSearch.RowSource = s & strWhere
'    Me.LstPersonSearch.Requery
    
End Function

[Welshgasman]

You do not say what the error/problem is ????

Likely mismatch?
If the criteria is text, then it needed to be surrounded by single quotes for the most situations, or triple quotes as you do for the other criteria?

I am sure you have had to do this before?

[d9pierce1]

Hi,
The problem is if I select from cbo it doesnt filter the list box, just empties it, sorry i didnt state that....
I really have not done this in this format. This function he wrote is totally new to me I will say.
I was able to get it correct in the format i used prior but not this one.
I have tried .value, "", "'", and so many ways. It complies but does not filter...
Have no real idea on how to write that line to make it filter...
Thanks