By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,526 Members | 2,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,526 IT Pros & Developers. It's quick & easy.

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

P: 76
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub filter_closed_Click()
  2.     If filter_closed.Caption = "Filter Closed" Then
  3.         filter_closed.Caption = "Unfilter Closed"
  4.     filter_closed.FontBold = True
  5.     filter_closed.ForeColor = 255
  6.  
  7.         If Filter_EP.Caption = "Filter EP" Then
  8.             DoCmd.ApplyFilter , "Status <> 'Closed'"
  9.         Else
  10.             DoCmd.ApplyFilter , "[Product Line] <> 'EP' and Status <> 'Closed' "
  11.         End If
  12.  
  13.     Else
  14.     filter_closed.Caption = "Filter Closed"
  15.     filter_closed.FontBold = False
  16.     filter_closed.ForeColor = 0
  17.         If Filter_EP.Caption = "Filter EP" Then
  18.             DoCmd.RunCommand acCmdRemoveFilterSort
  19.         Else
  20.             DoCmd.ApplyFilter , "[Product Line] <> 'EP'"
  21.         End If
  22.     End If
  23. End Sub
  24.  
  25. Private Sub Filter_EP_Click()
  26.  
  27.     If Filter_EP.Caption = "Filter EP" Then
  28.         Filter_EP.Caption = "Unfilter EP"
  29.     Filter_EP.FontBold = True
  30.     Filter_EP.ForeColor = 255
  31.     'Filter_EP.Caption = "Unfilter EP"
  32.         If filter_closed.Caption = "Filter Closed" Then
  33.             DoCmd.ApplyFilter , "[Product Line] <> 'EP'"
  34.         Else
  35.             DoCmd.ApplyFilter , "[Product Line] <> 'EP' and Status <> 'Closed' "
  36.         End If
  37.  
  38.     Else
  39.     Filter_EP.Caption = "Filter EP"
  40.     Filter_EP.FontBold = False
  41.     Filter_EP.ForeColor = 0
  42.         If filter_closed.Caption = "Filter Closed" Then
  43.             DoCmd.RunCommand acCmdRemoveFilterSort
  44.         Else
  45.             DoCmd.ApplyFilter , "Status <> 'Closed'"
  46.         End If
  47.     End If
  48.  
  49.  
  50.  
  51. End Sub
  52.  
Any help with this would be appreciative.

Thanks,

Brian
Oct 23 '07 #1
Share this Question
Share on Google+
15 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Oct 24 '07 #2

P: 76
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?
Oct 24 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 24 '07 #4

P: 76
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
Oct 24 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 24 '07 #6

P: 76
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?
Oct 24 '07 #7

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 24 '07 #8

P: 76
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.
Oct 24 '07 #9

nico5038
Expert 2.5K+
P: 3,072
The answer was posted in my previous comment # 6 :-)

Nic;o)
Oct 24 '07 #10

P: 76
If Me.FilterOn = True Then
txtFilter = Me.filter
Else
txtFilter = ""
End If
Oct 24 '07 #11

P: 76
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.
Oct 24 '07 #12

nico5038
Expert 2.5K+
P: 3,072
Make sure a filter is set and add after the EndIf:

Me.Requery

Nic;o)
Oct 24 '07 #13

P: 76
Still nothing. I have other things in the Form_Current sub already.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. On Error GoTo Err_Form_Current
  3.  
  4.     'Form.AllowEdits = False
  5.  
  6.  
  7. Me.Priority.Locked = True
  8. Me.Importance.Locked = True
  9. Me.Issue_Type.Locked = True
  10. Me.Status.Locked = True
  11. Me.Product_Line.Locked = True
  12. Me.Fike_part__.Locked = True
  13. Me.Product_s_.Locked = True
  14. Me.Product_Version_s_.Locked = True
  15. Me.Date_Entered.Locked = True
  16. Me.Date_Started.Locked = True
  17. Me.Initial_Fike_Contact.Locked = True
  18. Me.Feature.Locked = True
  19. Me.Problem_nickname.Locked = True
  20. Me.Conditions_of_Occurence.Locked = True
  21. Me.Text385.Locked = True
  22. Me.Problem_Description.Locked = True
  23. Me.Date_Found.Locked = True
  24. Me.Found_By.Locked = True
  25. Me.Completed_By.Locked = True
  26. Me.Date_Completed.Locked = True
  27. Me.Completion_Verified_By.Locked = True
  28. Me.Product_version_issue_resolved_in.Locked = True
  29. Me.Hyperlink.Locked = True
  30.     formlock.FontBold = False
  31.     formlock.ForeColor = 0
  32.     formlock.Caption = "Form Locked"
  33. Exit_Err_Form_Current:
  34.     Exit Sub
  35.  
  36. Err_Form_Current:
  37.     MsgBox Err.Description
  38.     Resume Exit_Err_Form_Current
  39.  
  40.     If Me.FilterOn = True Then
  41.     txtFilter = Me.filter
  42. Else
  43.     txtFilter = ""
  44. End If
  45.     Me.Requery
  46.  
  47. End Sub
  48.  
Oct 24 '07 #14

nico5038
Expert 2.5K+
P: 3,072
Looks to me the code isn't executed due to an "exit sub" in line 34....

Nic;o)
Oct 24 '07 #15

P: 76
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
Oct 25 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.