Filter on Multiple Fields

[Guest]

I have a form that displays data from its bound table. In the footer of the
form I have two comboboxes whose contents mirror two comboboxes on the main
form. Using:

Me.Filter = "[Field1] = '" & cmbobox1 & "'"
Me.FilterOn = True

in the AfterUpdate Event of Combobox1 I can filter the records accordingly.
If I use the same code (except calling "Field2" and using "cmbobox2" in the
AfterUpdate Event of Combobox2) I can perform a second different filter.

What I would like to be able to do is to filter on Field1 and then using
this as the new dataset, Filter on Field2 to further refine the Filtering,
(and so on if required).

I can do it with a From on a parameter query but this requires the operator
to remember the enteries required for Field1 and Field2, whereas this Filter
method allows them to select from the comboboxes.

Appreciate any suggestions,
Ian.

 

[Van T. Dinh]

I guess you need to combine the first Filter into the second Filter like:

Me.Filter = "([Field1] = '" & cmbobox1 & "') And " & _
"([Field2] = '" & cmbobox2 & "')"

(assuming both Field1 and Field2 are Text Fields)

 

[Guest]

Thanks for your suggestion, it worked perfectly, once I'd got all the
brackets and quotation marks in the right place.

With that success another question has now come to me, is it possible to
remove the filters applied and view the Form data complete as when the Form
is first opened. In other words, having filtered can I then remove those
filters by selecting an option in my comboboxes that displays all records
again. I tried using "" in the table lookup field but feel it needs some kind
of wildcard entry and I'm not sure how to do that.

Cheers,
Ian.

I guess you need to combine the first Filter into the second Filter like:

Me.Filter = "([Field1] = '" & cmbobox1 & "') And " & _
"([Field2] = '" & cmbobox2 & "')"

(assuming both Field1 and Field2 are Text Fields)

--
HTH
Van T. Dinh
MVP (Access)

I have a form that displays data from its bound table. In the footer of the
form I have two comboboxes whose contents mirror two comboboxes on the
main
form. Using:

Me.Filter = "[Field1] = '" & cmbobox1 & "'"
Me.FilterOn = True

in the AfterUpdate Event of Combobox1 I can filter the records
accordingly.
If I use the same code (except calling "Field2" and using "cmbobox2" in
the
AfterUpdate Event of Combobox2) I can perform a second different filter.

What I would like to be able to do is to filter on Field1 and then using
this as the new dataset, Filter on Field2 to further refine the Filtering,
(and so on if required).

I can do it with a From on a parameter query but this requires the
operator
to remember the enteries required for Field1 and Field2, whereas this
Filter
method allows them to select from the comboboxes.

Appreciate any suggestions,
Ian.

 

[Pieter Wijnen]

something like
If Me.cmbobox1="<all>" And me.cmbobox2="<all>" Then
Me.Filter=VBA.vbnullstring
ElseIf Me.cmboBox2="<All>" Then
Me.Filter = "[Field1]='" & Me.cmboBox1 & "'"
Else
Me.Filter = "([Field1] = '" & cmbobox1 & "') And " & _
"([Field2] = '" & cmbobox2 & "')"
End If
me.filteron=VBA.Len(Me.Filter) > 0

HTH

Pieter

PS U can use a UNION query to Add the "<All>" To the Comboboxes

Thanks for your suggestion, it worked perfectly, once I'd got all the
brackets and quotation marks in the right place.

With that success another question has now come to me, is it possible to
remove the filters applied and view the Form data complete as when the
Form
is first opened. In other words, having filtered can I then remove those
filters by selecting an option in my comboboxes that displays all records
again. I tried using "" in the table lookup field but feel it needs some
kind
of wildcard entry and I'm not sure how to do that.

Cheers,
Ian.

I guess you need to combine the first Filter into the second Filter like:

Me.Filter = "([Field1] = '" & cmbobox1 & "') And " & _
"([Field2] = '" & cmbobox2 & "')"

(assuming both Field1 and Field2 are Text Fields)

--
HTH
Van T. Dinh
MVP (Access)

I have a form that displays data from its bound table. In the footer of
the
form I have two comboboxes whose contents mirror two comboboxes on the
main
form. Using:

Me.Filter = "[Field1] = '" & cmbobox1 & "'"
Me.FilterOn = True

in the AfterUpdate Event of Combobox1 I can filter the records
accordingly.
If I use the same code (except calling "Field2" and using "cmbobox2" in
the
AfterUpdate Event of Combobox2) I can perform a second different
filter.

What I would like to be able to do is to filter on Field1 and then
using
this as the new dataset, Filter on Field2 to further refine the
Filtering,
(and so on if required).

I can do it with a From on a parameter query but this requires the
operator
to remember the enteries required for Field1 and Field2, whereas this
Filter
method allows them to select from the comboboxes.

Appreciate any suggestions,
Ian.

 

[Guest]

Hi Pieter,

Thanks for the code, works perfectly. Presumably adding extra comboboxes to
filter further just requires this code to be tweaked to reflect the new cmbo
and text field.

Thanks again,
Ian.

something like
If Me.cmbobox1="<all>" And me.cmbobox2="<all>" Then
Me.Filter=VBA.vbnullstring
ElseIf Me.cmboBox2="<All>" Then
Me.Filter = "[Field1]='" & Me.cmboBox1 & "'"
Else
Me.Filter = "([Field1] = '" & cmbobox1 & "') And " & _
"([Field2] = '" & cmbobox2 & "')"
End If
me.filteron=VBA.Len(Me.Filter) > 0

HTH

Pieter

PS U can use a UNION query to Add the "<All>" To the Comboboxes

Thanks for your suggestion, it worked perfectly, once I'd got all the
brackets and quotation marks in the right place.

With that success another question has now come to me, is it possible to
remove the filters applied and view the Form data complete as when the
Form
is first opened. In other words, having filtered can I then remove those
filters by selecting an option in my comboboxes that displays all records
again. I tried using "" in the table lookup field but feel it needs some
kind
of wildcard entry and I'm not sure how to do that.

Cheers,
Ian.

I guess you need to combine the first Filter into the second Filter like:

Me.Filter = "([Field1] = '" & cmbobox1 & "') And " & _
"([Field2] = '" & cmbobox2 & "')"

(assuming both Field1 and Field2 are Text Fields)

--
HTH
Van T. Dinh
MVP (Access)



I have a form that displays data from its bound table. In the footer of
the
form I have two comboboxes whose contents mirror two comboboxes on the
main
form. Using:

Me.Filter = "[Field1] = '" & cmbobox1 & "'"
Me.FilterOn = True

in the AfterUpdate Event of Combobox1 I can filter the records
accordingly.
If I use the same code (except calling "Field2" and using "cmbobox2" in
the
AfterUpdate Event of Combobox2) I can perform a second different
filter.

What I would like to be able to do is to filter on Field1 and then
using
this as the new dataset, Filter on Field2 to further refine the
Filtering,
(and so on if required).

I can do it with a From on a parameter query but this requires the
operator
to remember the enteries required for Field1 and Field2, whereas this
Filter
method allows them to select from the comboboxes.

Appreciate any suggestions,
Ian.