Need to perform wildcard filter of form using textbox 3

[BxWill]

Using the following code on the click event of a command button on a form to filter a list box based on the AcctStatus field that is displayed in the list box.

What modification(s) is/are necessary to enable a filter on any field within the listbox?

Also, is it possible to have multiple textboxes and be able to perform a filter and subfilter on the contents of a listbox - sort of like a sort and subsort function within MS Excel?

Appreciate any additional thoughts regarding the filtering of a listbox using textboxes or another more preferred method.

Private Sub Command116_Click()

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()


strSQL = "SELECT tblData.Product, tblData.AcctStatus FROM tblData"


strWhere = "WHERE"


strOrder = "ORDER BY tblData.Product, tblData.AcctStatus;"

If Not IsNull(Me.Text114) Then

strWhere = strWhere & " (tblData.AcctStatus) Like '*" & Text114 & "*' AND"

End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

Me.List84.RowSource = strSQL & " " & strWhere & "" & strOrder


Me.Text117 = Me.List84.ListCount - 1
If (Me.List84.ListCount = 0) Then
Me.Text117 = 0
End If

 

[MazeWorX]

For starters just to clean up your code

Private Sub Command116_Click()

    Dim strSQL As String, strOrder As String, strWhere As String
    Dim dbNm As Database

    Set dbNm = CurrentDb()

    strSQL = "SELECT tblData.Product, tblData.AcctStatus FROM tblData"
    strOrder = " ORDER BY tblData.Product, tblData.AcctStatus;"

    If Not IsNull(Me.Text114) Then
        strWhere = " WHERE(((tblData.AcctStatus) Like & Text114 & """ * """))"
        strSQL = strSQL & strWhere & strOrder
    Else
        strSQL = strSQL & strOrder
    End If

    Me.List84.RowSource = strSQL
    Me.List84.Requery

    Me.Text117 = Me.List84.ListCount - 1
    If (Me.List84.ListCount = 0) Then
        Me.Text117 = 0
    End If
End Sub

untested

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[dhookom]

I don't know what you were doing with the dbNm and qryDef objects since they are not used in your code. This is how I would write it. If you want to add more conditions to where, just add another If Then block. Also, do yourself a favor and kick your development up a notch and change the names of your significant controls.

Private Sub Command116_Click()
  Dim strSQL As String, strOrder As String, strWhere As String
'[green]  Dim dbNm As Database[/green]
'[green]  Dim qryDef As QueryDefSet[/green] 
'[green]  dbNm = CurrentDb()[/green]
  strSQL = "SELECT Product, AcctStatus FROM tblData "
  strWhere = "WHERE 1=1 "
  strOrder = "ORDER BY Product, AcctStatus;"
  If Not IsNull(Me.Text114) Then
    strWhere = strWhere & " AND AcctStatus Like '*" & Text114 & "*' "
  End If
  Me.List84.RowSource = strSQL & strWhere & strOrder
  Me.Text117 = Me.List84.ListCount - 1
  If (Me.List84.ListCount = 0) Then
    Me.Text117 = 0    
  End If

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

This may or may not work the way you want.
This takes a single textbox and any listbox with any amount of fields. It then filters as you type the records that meet the criteria in any field. The beauty of this is that it is encapsulated into a class module so this is the only code you need. All you have to do is instantiate the class and pass the listbox and text box.

Public faytLst As FindAsYouTypeListBoxMultiField
Private Sub Form_Load()
  Set faytLst = New FindAsYouTypeListBoxMultiField
  faytLst.Initialize Me.lstSearch, Me.txtSearch
End Sub

the class does the rest.

http://www.4shared.com/file/168068619/18a0dbab/multiFieldFAYTList.html

 

[MazeWorX]

lol missed the dbnm statement

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work