Search function within listbox

[Onlylonely]

Hi,

I'm facing an issue to find the text in the listbox.
Could anyone help me on this?

Below coding is find the text in the table.

Private Sub txtfind_Change()

        
        Dim strSource As String
        strSource = "SELECT * " & _
         "FROM Guests " & _
         "Where FirstName Like '*" & Me.txtfind.Text & "*';"
         
        Me.List0.RowSource = strSource


End Sub

 

[pbaldy]

What is the issue?

 

[Onlylonely]

What is the issue?

Objective: I need to get a textbox to filter the Part Number i want in listbox.

Listbox only will show up "OPEN" item in the table.

For example:
When i typing "2" in the textbox. I wish to filter PartNumber 2 in the listbox.

 

[MajP]

Untested, but likely something like

Private Sub txtfind_AfterUpdate()
  'Never filter on change event unless you want to filter on each keystroke
   dim lst as access.listbox
   dim strSql as string
   dim strCriteria as string
   dim strOrder as string
   setLst = Me.YourListBoxName
   strSql = "Select PartNumber, Quantity, Rejection from SomeTable"
   
   If not trim(me.QuantityTextBoxName & " ") = "" then
      strSql = StrSql & " WHERE Quantity = " & me.QuantityTextBoxName & " AND Remark = 'Open'"      
   end if
   strOrder = " ORDER BY SomeField1, SomeField2"
   strSql = strSql & strOrder
   debug.print strSql ' double check the string looks good
   lst.rowsource = strSql 
End Sub

 

[Orthodox Dave]

I think you need to requery the list box after your code:

Private Sub txtfind_Change()

        
        Dim strSource As String
        strSource = "SELECT * " & _
         "FROM Guests " & _
         "Where FirstName Like '*" & Me.txtfind.Text & "*';"
         
        Me.List0.RowSource = strSource

[B][COLOR="Red"]Me.List0.Requery[/COLOR][/B]

End Sub

 

[MajP]

I think you need to requery the list box after your code

No. Setting the rowsource automatically requeries. Even this will requery

me.SomeControl.rowsource = me.somecontrol.rowsource

 

[arnelgp]

Private Sub txtfind_Change()

        
        Dim strSource As String
        strSource = "SELECT [Part Number], [Quantity], [Rejection]  " & _
         "FROM yourTable " & _
         "Where ([Part Number] & '') Like '*" & Me.txtfind & "*' And " & _
         "[Remark]='Open';"
         
        Me.List0.RowSource = strSource


End Sub

substitute Real tablename to yourtable.