use multiple textboxes to filter a listbox

[MarMo]

Hi ,

I'm trying to filter a listbox on a form with 2 or 3 textboxes as the
user types

Filtering with 1 textbox on the OnChange event works fine.
this is the code i'm using
'***********************************************************************
Sub FilterListSerial()
txtSearchString = Forms!FrmOrderSearch.TxtSearchSerial.Text

strSQL = "SELECT DISTINCT * FROM QryOrdersSearch "
strSQL = strSQL & "WHERE [Workorder] Like '" & txtSearchString
& "*' "
strSQL = strSQL & "ORDER BY PickupDate DESC"

Forms!FrmOrderSearch!LstSearchOrders.RowSource = strSQL
Forms!FrmOrderSearch!LstSearchOrders.Requery
'***********************************************************************

How can i filter the listbox when a user types values in 2 or more
textboxes.
Thanks for helping me.
MarMo

 

[Dale Fye]

Another option would be to go with something like:

'***********************************************************************
Sub FilterListSerial()

Dim frm as form
dim varCriteria as Variant

varCriteria = NULL
Set frm = Forms!FrmOrderSearch

strSQL = "SELECT DISTINCT * FROM QryOrdersSearch "

if len(frm.txt_Field1 & "") > 0 then
varCriteria = "[Field1] like '" & frm.txt_Field1 & "*' "
endif
if len(frm.txt_Field2 & "") > 0 then
varCriteria = (varCriteria + " AND ") _
& "[Field2] like '" & frm.txt_Field2 & "#' "
endif
if len(frm.txt_Field3 & "") > 0 then
varCriteria = (varCriteria + " AND ") _
& "[Field3] like '" & frm.txt_Field3 & "#' "
endif

strSQL = strSQL & (" WHERE " + varCriteria) _
& " ORDER BY PickupDate DESC"

frm.LstSearchOrders.RowSource = strSQL

'Don't need the following line
'Forms!FrmOrderSearch!LstSearchOrders.Requery
'***********************************************************************

 

[MarMo]

Hello Terry , hello Dale
Sorry for not being clear , i'll try to be more specific.

I have a form on which there are :
- a listbox (LstSearchOrders)
- 3 textboxes (txtSearchWO ; txtSearchCity ; txtSearchPC (zipcode)

The source of the listbox is a query called qrySearchOrders with
several fields including Workorder ; City ; PostalCode.
The form itself has no recordsource.
So the user can type a value in any textbox to filter the listbox ,
and it should filter according to all values in the textboxes , even
if they're empty.

I'll try the code already given tomorrow and let you know.

Thanks
MarMo

 

[MarMo]

Hello Terry , hello Dale
Sorry for not being clear , i'll try to be more specific.

I have a form on which there are :
- a listbox (LstSearchOrders)
- 3 textboxes (txtSearchWO ; txtSearchCity ; txtSearchPC (zipcode)

The source of the listbox is a query called qrySearchOrders with
several fields including Workorder ; City ; PostalCode.
The form itself has no recordsource.
So the user can type a value in any textbox to filter the listbox ,
and it should filter according to all values in the textboxes , even
if they're empty.

I'll try the code already given tomorrow and let you know.

Thanks
MarMo

Hi Terry , Dale,

I managed to filter the listbox with both textboxes
In a module i defined the variables as PUBLIC variables , so i can
access them anywhere in the application

' -------------- On Module level -----------------
Option Explicit
Public txtSearchWO As Variant
Public txtSearchCITY As Variant

Sub FilterListALL()
strSQL = "SELECT DISTINCT * FROM QryOrdersSearch "
'strSQL = "SELECT * FROM " & strSource
strSQL = strSQL & "WHERE [Workorder] Like '" & txtSearchWO &
"*' "
strSQL = strSQL & " AND [Town] Like '" & txtSearchCITY & "*' "
strSQL = strSQL & "ORDER BY PickupDate DESC"

Forms!FrmOrderSearch!LstSearchOrders.RowSource = strSQL
Forms!FrmOrderSearch!LstSearchOrders.Requery
End Sub

'-------------- On Form level ----------------

Option Compare Database
Option Explicit
Private Sub TxtCity_Change()
' use .Text on this textbox and use .Value on the other textboxes
'
txtSearchCITY = Me.TxtCity.Text
txtSearchWO = Me.TxtSearchSerial.Value
FilterListALL
End Sub
Private Sub TxtSearchSerial_Change()
' use .Text on this textbox and use .Value on the other textboxes
'
txtSearchWO = Me.TxtSearchSerial.Text
txtSearchCITY = Me.TxtCity.Value
FilterListALL ' goto sub on module level
End Sub

This works for me. Thanks for helping me out in finding a solution.
Many thanks again.