Multiple Filters on a Form

[rocky09]

Hi All,

I have been trying to create a User Form, which has multiple Filters. But, no luck for me. Can any one guide me.

Situation:

I have Split Form, which user can add, update records.
Table has following Fields, ID, Customer Name, Phone NO, Email, Purchase Enquiry Details, Sales Person, Status of the Enquiry (Open, Closed, In process)

I have placed two Unbound Combo boxes in the Form Header for the Filter.

1. Customer Name 2. Status of the Enquiry

So, Ideally, If user select, One Customer and Status of the Enquiry, it has to filter and display the only filtered results. But, I don't have any idea, how to achieve this.:banghead:

Can any one please guide me.

Thank you in advance.

 

[llkhoutx]

Use the FilterOn, Filter properties for you form. Create you Filter property based upon which, either or both you filters are populated and set FilterOn = True. Note on the form_load event, set FilterOn appropriately.

 

[leanpilar]

you can also bound the form to a query that has filter based on the combo value and requery on combo change event

 

[rocky09]

Use the FilterOn, Filter properties for you form. Create you Filter property based upon which, either or both you filters are populated and set FilterOn = True. Note on the form_load event, set FilterOn appropriately.

Thank you very much for the reply. But, the First filter only works, the second one is not working.

 

[arnelgp]

Dim strFilter As String
If Me.comboCustomer & "" <> "" then
strFilter = "[yourFieldNameForCustomer] = '" & Me.comboCustomer & "' And "
End If
If Me.comboStatus & "" <> "" then
strFilter = strFilter & "[yourStatusField] = '" & Me.comboStatus & "'"
End if
If strFilter <> "" Then
If Instr(strFilter, "And") = Len(strFilter-4) Then
strfilter = Replace(strFilter,"And","")
End If
Me.Filter=strFilter
Me.Filter=True
End If

 

[rocky09]

Dim strFilter As String
If Me.comboCustomer & "" <> "" then
strFilter = "[yourFieldNameForCustomer] = '" & Me.comboCustomer & "' And "
End If
If Me.comboStatus & "" <> "" then
strFilter = strFilter & "[yourStatusField] = '" & Me.comboStatus & "'"
End if
If strFilter <> "" Then
If Instr(strFilter, "And") = Len(strFilter-4) Then
strfilter = Replace(strFilter,"And","")
End If
Me.Filter=strFilter
Me.Filter=True
End If

Thank you very much for your reply,

I am getting an error saying that Variable required - can't assign to this expression

at the following line : Len(strFilter - 4

 

[arnelgp]

sorry about that, try:
If Instr(strFilter, "And") = Len(strFilter)-4 Then

 

[rocky09]

sorry about that, try:
If Instr(strFilter, "And") = Len(strFilter)-4 Then

Thank you so much. You are a life saver.