filter listbox (1 Viewer)

eugzl

Member
Local time
Today, 09:59
Joined
Oct 26, 2021
Messages
125
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
Code:
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:
1635209034103.png

How to fix the problem?
Thanks
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:59
Joined
May 21, 2018
Messages
8,463
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.
Code:
 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
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:59
Joined
May 21, 2018
Messages
8,463
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
Code:
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

Member
Local time
Today, 09:59
Joined
Oct 26, 2021
Messages
125
Hi MajP.
I tried to modify your code for string value in AfterUpdate event
Code:
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
1635291032945.png

How to fix the error? Thanks
 
Last edited:

moke123

AWF VIP
Local time
Today, 09:59
Joined
Jan 11, 2013
Messages
3,849
Add a debug.print strSql and see what it looks like.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:59
Joined
May 21, 2018
Messages
8,463
maybe something like
Code:
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

Member
Local time
Today, 09:59
Joined
Oct 26, 2021
Messages
125
The correct syntax for strSql:
strSql = "Select * from qRequestFilter WHERE Request Like ""*" & Me.txtRequest & "*"""
or
strSql = "Select * from qRequestFilter WHERE Request Like '*" & Me.txtRequest & "*'"
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:59
Joined
May 21, 2018
Messages
8,463

Users who are viewing this thread

Top Bottom