Trying to filter using a button and filter by another Field in a form.

[Briansmi1116]

I created two buttons, that filter my form, They filter in different Fields, and if one is filtered, and the other is not there is a certain amount of record, if they are both pushed, then there is less. This works great, but now I want to filter within what is left in the form, without adding the other records.

The code I use to filter using my buttons is:

Private Sub filter_closed_Click()
    If filter_closed.Caption = "Filter Closed" Then
        filter_closed.Caption = "Unfilter Closed"
    filter_closed.FontBold = True
    filter_closed.ForeColor = 255
 
        If Filter_EP.Caption = "Filter EP" Then
            DoCmd.ApplyFilter , "Status <> 'Closed'"
        Else
            DoCmd.ApplyFilter , "[Product Line] <> 'EP' and Status <> 'Closed' "
        End If
 
    Else
    filter_closed.Caption = "Filter Closed"
    filter_closed.FontBold = False
    filter_closed.ForeColor = 0
        If Filter_EP.Caption = "Filter EP" Then
            DoCmd.RunCommand acCmdRemoveFilterSort
        Else
            DoCmd.ApplyFilter , "[Product Line] <> 'EP'"
        End If
    End If
End Sub
 
Private Sub Filter_EP_Click()
 
    If Filter_EP.Caption = "Filter EP" Then
        Filter_EP.Caption = "Unfilter EP"
    Filter_EP.FontBold = True
    Filter_EP.ForeColor = 255
    'Filter_EP.Caption = "Unfilter EP"
        If filter_closed.Caption = "Filter Closed" Then
            DoCmd.ApplyFilter , "[Product Line] <> 'EP'"
        Else
            DoCmd.ApplyFilter , "[Product Line] <> 'EP' and Status <> 'Closed' "
        End If
 
    Else
    Filter_EP.Caption = "Filter EP"
    Filter_EP.FontBold = False
    Filter_EP.ForeColor = 0
        If filter_closed.Caption = "Filter Closed" Then
            DoCmd.RunCommand acCmdRemoveFilterSort
        Else
            DoCmd.ApplyFilter , "Status <> 'Closed'"
        End If
    End If
 
 
 
End Sub
 

Any help with this would be appreciative.

Thanks,

Brian

[nico5038]

The increasing number of filter combinations can become rather "code intensive".
I would like to ask you to check first:
http://www.geocities.com/nico5038/xR...nstruction.zip
This way no code is needed and I just give this description to my users.
Saved me lots of code and the user can filter every field (s)he likes.

Idea ?

Nic;o)

[Briansmi1116]

The form I have, my engineer uses the filter option already, it is just i put in two buttons, one filters out the closed items, and the other filters out the Product we call EP. These buttons work with each other, using what the caption of the button is. My engineer wants to be able to filter other Fields, and use these buttons. Every time he does, the button filter, or the other filter, gets canceled, and just applies the last filter. So I was wondering is there a way to lock the filters when the button is used?

[nico5038]

The popup filter will allow multiple selections and these will be added using an "AND".

When you want to code this I would use the .filter property.
That way you can test or Len(Me.Filter) > 0 and add a new field with a prefized " and ".

Personally I prefer to use a datasheet subform for this and "push" the applied filter from the subform to a (read only) textbox on the mainform. Even using the right-click will be making the active filtering visible.

Checkout:
http://www.geocities.com/nico5038/xS...tAction-97.zip

Nic;o)

[Briansmi1116]

I like the way the filter shows up in a text box. Your example shows it in the main form for the subform. Is there a way to show it in the main form from the main form? I think this exaple otherwise would work

Brian

[nico5038]

Even easier, define an unbound textbox (e.g. named txtFilterMain) and use the OnCurrent event to test FilterOn to be true to move the Me.filter when true and to set the field to "" when FilterOn is False.

I did once create a database that allowed the user to store the contents of this textbox under a name so the filter could be recalled and/or changed. Only experienced users that know to write WHERE clauses for queries should be given this option...

Nic;o)

[Briansmi1116]

I'm not seeing the OnCurrent event. I'm still kinda new to this, so could you show me what I would typ in VB?

[nico5038]

Open your form in designmode and immediately open the properties window.
(There's a small gray square top left between the form rulers that will have a black square inside, indicating the form has been selected)
Now go to the Events tab and double-click the OnCurrent text.
The textbox will be filled with "Procedure" and behind the text a [...] button will allow you to open the code for the OnCurrent event.
Check in my sample database the subform to see how to use the code.

Nic;o)

[Briansmi1116]

OK, but the example says:

If Me.FilterOn Then
Parent.Form.txtFilter = Me.Filter
Else
Parent.Form.txtFilter = ""
End If

Since this is the main form, this doesn't work. What does it need to be.

[nico5038]

The answer was posted in my previous comment # 6 :-)

Nic;o)

[Briansmi1116]

If Me.FilterOn = True Then
txtFilter = Me.filter
Else
txtFilter = ""
End If

[Briansmi1116]

I tried this:

If Me.FilterOn = True Then
txtFilter = Me.filter
Else
txtFilter = ""
End If

I am just not sure the syntax is right. It doesn't show in my textbox.

[nico5038]

Make sure a filter is set and add after the EndIf:

Me.Requery

Nic;o)

[Briansmi1116]

Still nothing. I have other things in the Form_Current sub already.

Private Sub Form_Current()
On Error GoTo Err_Form_Current
 
    'Form.AllowEdits = False
 
 
Me.Priority.Locked = True
Me.Importance.Locked = True
Me.Issue_Type.Locked = True
Me.Status.Locked = True
Me.Product_Line.Locked = True
Me.Fike_part__.Locked = True
Me.Product_s_.Locked = True
Me.Product_Version_s_.Locked = True
Me.Date_Entered.Locked = True
Me.Date_Started.Locked = True
Me.Initial_Fike_Contact.Locked = True
Me.Feature.Locked = True
Me.Problem_nickname.Locked = True
Me.Conditions_of_Occurence.Locked = True
Me.Text385.Locked = True
Me.Problem_Description.Locked = True
Me.Date_Found.Locked = True
Me.Found_By.Locked = True
Me.Completed_By.Locked = True
Me.Date_Completed.Locked = True
Me.Completion_Verified_By.Locked = True
Me.Product_version_issue_resolved_in.Locked = True
Me.Hyperlink.Locked = True
    formlock.FontBold = False
    formlock.ForeColor = 0
    formlock.Caption = "Form Locked"
Exit_Err_Form_Current:
    Exit Sub
 
Err_Form_Current:
    MsgBox Err.Description
    Resume Exit_Err_Form_Current
 
    If Me.FilterOn = True Then
    txtFilter = Me.filter
Else
    txtFilter = ""
End If
    Me.Requery
 
End Sub
 

[nico5038]

Looks to me the code isn't executed due to an "exit sub" in line 34....

Nic;o)

[Briansmi1116]

I see that now, I moved it then I had to remove the requery. It kept making my records flicker, and would not let me go to the next record. Thanks a lot, it works great.

Brian