How to apply 2 or more text box filters to 1 list box on form

[Guest]

Access 2003

PLEASE HELP!

I would like to create a form for filtering and viewing data in a table. I
want the table data to appear in a list box on the form and I want to filter
this data by entering criteria in two or more text boxes and combo boxes on
the same form. I want the data to be automatically filtered when criteria is
entered into the text or combo box. I want the the data to be filtered by all
the criteria in each text or combo box. So far I have only been able to have
the data filtered by one text or combo box at a time. I can't get more than
one criteria to be applied to the data at the same time.

Please help

Regards

danielrt9

 

[Guest]

I've figured out how to do it by using a control button to apply the
criterias instead of having them automatically update as I type. I guess that
will have to do.

 

[Guest]

Daniel,

I don't know if this code helps you. I'll put two examples. The first refers
to a query that updates the listbox. In the after update event of each of the
textboxes type:

Private Sub textboxname_AfterUpdate()
With Listboxname
.Value = ""
.RowSourceType = "Table/Query"
.RowSource = "SELECT [queryname].[field] FROM [queryname]; "
End With
End Sub


Alternatively, I've put an address search that I did - please bear in mind I
only used Access for the first time a couple of weeks ago so I'm not so good
and there's probably a much better way of doing this- but then if it works ...

Private Sub add1_AfterUpdate()
Dim RSDEF As String
Dim IF1 As String
Dim IF2 As String
Dim IF3 As String
Dim IF4 As String
Dim IF5 As String
Dim IF6 As String
Dim CNT As Integer
CNT = 0
Dim and1 As String
Dim and2 As String
Dim and3 As String
Dim and4 As String
Dim and5 As String

If IsNull([Forms]![Address]![housename].Value) Then IF1 = "" Else IF1 =
"(([ADDRESS].[House name/number]) Like [Forms]![ADDRESS]![housename] & '*')"
If IsNull([Forms]![Address]![add1].Value) Then IF2 = "" Else IF2 =
"(([ADDRESS].[Address line 1]) Like [Forms]![ADDRESS]![add1] & '*')"
If IsNull([Forms]![Address]![add2].Value) Then IF3 = "" Else IF3 =
"(([ADDRESS].[Address line 2]) Like [Forms]![ADDRESS]![add2] & '*')"
If IsNull([Forms]![Address]![parish].Value) Then IF4 = "" Else IF4 =
"(([ADDRESS].[Parish]) Like [Forms]![ADDRESS]![parish] & '*')"
If IsNull([Forms]![Address]![postcode].Value) Then IF5 = "" Else IF5 =
"(([ADDRESS].[Postcode]) Like [Forms]![ADDRESS]![postcode] & '*'))"
'If IsNull([Forms]![Address]![Country].Value) Then IF6 = "" Else IF6 =
"(([ADDRESS].[Country]) Like [Forms]![ADDRESS]![Country])"

If IsNull([Forms]![Address]![housename].Value) Then and1 = "" Else and1 = "
and "
If IsNull([Forms]![Address]![add1].Value) Then and2 = "" Else and2 = " And "
If IsNull([Forms]![Address]![add2].Value) Then and3 = "" Else and3 = " And "
If IsNull([Forms]![Address]![parish].Value) Then and4 = "" Else and4 = " And "
If IsNull([Forms]![Address]![postcode].Value) Then and5 = "" Else and5 = "
And "
'If IsNull([Forms]![Address]![Country].Value) Then and6 = "" Else and6 = "
And "

If IF1 = "" Then CNT = CNT Else If CNT = 0 Then CNT = 1
If IF2 = "" Then CNT = CNT Else If CNT = 0 Then CNT = 2
If IF3 = "" Then CNT = CNT Else If CNT = 0 Then CNT = 3
If IF4 = "" Then CNT = CNT Else If CNT = 0 Then CNT = 4
If IF5 = "" Then CNT = CNT Else If CNT = 0 Then CNT = 5
'If IF6 = "" Then CNT = CNT Else If CNT > 0 Then CNT = 6

If CNT = 1 Then and1 = ""
If CNT = 2 Then and2 = ""
If CNT = 3 Then and3 = ""
If CNT = 4 Then and4 = ""
If CNT = 5 Then and5 = ""
'If CNT = 6 Then and6 = ""

'CONSTRUCT VARIABLE RSDEF
RSDEF = "SELECT [ADDRESS].[ADDRESS ID], [ADDRESS].[House name/number],
[ADDRESS].[Address line 1], [ADDRESS].[Address line 2], [ADDRESS].[Parish],
[ADDRESS].[Postcode], [ADDRESS].[Country] FROM ADDRESS WHERE (" & IF1 & and2
& IF2 & and3 & IF3 & and4 & IF4 & and5 & IF5 '& and6 & IF6

With addlist
.RowSourceType = "Table/query"
.RowSource = RSDEF
End With
End Sub

It basically constructs a long-winded SQL statement depending on which of
the text fields is populated and then fills the addlist listbox with the
results of a filter using ALL of the text boxes.

Hope this isn't too late

Cynic