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

 

[Terry Kreft]

You're post isn't very clear you really need to tell us what the other text
boxes are called and what the fields in the QryOrderSearch are that you want
to filter on.


A generalist answer which wouldn't be particularly optimised would be:-

Where the other text boxes are called Text2 and Text3 and where the other
fields are called Field2 and Field3 then

'***********************************************************************
Sub FilterListSerial()
Dim f as Form
Set f = Forms!FrmOrderSearch

strSQL = "SELECT DISTINCT * FROM QryOrdersSearch " _
& "WHERE [Workorder] Like '" & f.TxtSearchSerial& "*' " _
& "AND Field2 Like '" & f.Text2 & "*' " _
& "AND Field3 Like '" & f.Text3 & "*' " _
& "ORDER BY PickupDate DESC"

Set f = Nothing
Forms!FrmOrderSearch!LstSearchOrders.RowSource = strSQL
Forms!FrmOrderSearch!LstSearchOrders.Requery
'***********************************************************************

Notice the Text property is not being referenced, this is inportant as the
Text property is not available unless the control has the focus.

 

[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.