filter listbox

[eugzl]

Hi All.
I'm trying to create VBA code to filter ListBox via value of TextBox. On online I found sample that is very similar to what I need

Private Sub txtDate_Exit(Cancel As Integer)
    Dim sFilter As String, oRS As DAO.Recordset
  
    If IsNull(txtDate) Then DoCmd.ShowAllRecords: Exit Sub
  
    DoCmd.ApplyFilter , sFilter
    Set oRS = Me.RecordsetClone
  
    If oRS.RecordCount = 0 Then
        MsgBox "No record matches"
        DoCmd.ShowAllRecords
    End If
  
    Cancel = True
End Sub

But when I run form, type value in TextBox and then press Enter in line DoCmd.ApplyFilter , sFilter I got error message:

How to fix the problem?
Thanks

 

[MajP]

Your title says Listbox but your attempted code looks as if you are trying to filter a form. Which is it?
My best guess from what you show is that you want to put a date in a textbox and filter the form to that date; however, nothing in the code is even close to doing that. Maybe something like this, and probably in the after update not exit event.

 Dim sFilter As String

    If isdate(txtDate) then
       sFilter = "SomeField = #" & format(me.txtDate,"MM/DD/YYYY") & "#"
       me.filter = sFilter
       me.filterOn = true
   else
      me.filter = ""
      me.FilterOn = false
   end if
End Sub

 

[MajP]

If you want to filter a listbox based on a textbox, then you likely will have to manipulate the rowsource of the listbox. Maybe something like

dim strSql as string
strSql = "Select SomeDate from SomeTable "
 If isdate(txtDate) then
      strSql = strSql & " WHERE SomeDate = #" & format(me.txtDate,"MM/DD/YYYY") & "#"
 else
   msgbox "Not a valid date"
end if 
strSql = strSql & " ORDER BY SomeDate"
me.SomeListbox.rowsource = strSql

 

[eugzl]

Hi MajP.
I tried to modify your code for string value in AfterUpdate event

Private Sub txtFilter_AfterUpdate()
    Dim strSql As String
    strSql = "Select Request from qRequestFilter "
         strSql = strSql & " WHERE Request = " Like '" & Me.txtFilter & " * '"
    strSql = strSql & " ORDER BY Request"
    Me.lstRequest.RowSource = strSql
End Sub

in line
strSql = strSql & " WHERE Request = " Like '" & Me.txtFilter & " * '"
got error message

How to fix the error? Thanks

 

[moke123]

Add a debug.print strSql and see what it looks like.

 

[MajP]

maybe something like

Private Sub txtFilter_AfterUpdate()
    Dim strSql As String
   if not isnull(txtFilter) then
    strSql = "Select Request from qRequestFilter WHERE Request Like '" & Me.txtFilter & "* '"
    strSql = strSql & " ORDER BY Device"
    debug.print strSql
    Me.lstRequest.RowSource = strSql
  end if
End Sub

 

[eugzl]

The correct syntax for strSql:
strSql = "Select * from qRequestFilter WHERE Request Like ""*" & Me.txtRequest & "*"""
or
strSql = "Select * from qRequestFilter WHERE Request Like '*" & Me.txtRequest & "*'"

 

[MajP]

Latter.