• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2683
  • Last Modified:

Creating Toggle Filter Button in Access VBA

I would like to code a button that will essentially do what the "toggle filter" button does in the Access ribbon.  I would like it to filter on the current field's value.  I would then like a button to remove it.  Does anyone know how to do this?
0
marku24
Asked:
marku24
  • 6
  • 5
  • 4
  • +1
2 Solutions
 
mbizupCommented:
Your Filter On code (for Numeric data):

Me.Filter = "[YourField] = " & txtYourIDField
Me.FilterOn = TRUE

Or for TEXT data:

Me.Filter = "[YourField] = " & chr(34) &  txtYourField & chr(34)
Me.FilterOn = TRUE




To toggle the filter off:

Me.Filter = ""
Me.FilterOn = False
0
 
marku24Author Commented:
thank you - it could be one of three text fields.  How do I tell the script which is my current / active field?
0
 
mbizupCommented:
Give this a try:


Me.Filter = Me.ActiveControl.Controlsource =  & Chr(34)  & Me.ActiveControl & chr(34)
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mbizupCommented:
Or:

Me.Filter = Me.ActiveControl.Controlsource & " = "  & Chr(34)  & Me.ActiveControl & chr(34)
0
 
Dale FyeCommented:
Actually, once you have the form filtered, your button that toggles the filter on and off can simply change the FilterOn property of the form.

Private Sub cmd_FilterToggle_Click

    me.FilterOn = Not me.FilterOn

End Sub
0
 
marku24Author Commented:
The filter doesn't work - Run time error 438:  Object doesn't support this property or method.  any ideas?
0
 
mbizupCommented:
Sorry - if you're clicking a command button after entering the data needed, youll have to refer to the PREVIOUS control (the active control is the command button):

Me.Filter = Screen.PreviousControl.Controlsource =  & Chr(34)  & Screen.PreviousControl & chr(34)
0
 
mbizupCommented:
Actually, try this:


Me.Filter = "[" & Screen.PreviousControl.Controlsource & "] = "  & Chr(34)  & Screen.PreviousControl & chr(34)
0
 
marku24Author Commented:
The filter off does work but the filter code above does not.  It doesn't error but does not filter.
0
 
Dale FyeCommented:
Mark,

You have 15 responses.  Without indicating which idea you are responding to it is a little difficult.  Can you provide the code you are currently using?
0
 
marku24Author Commented:
sorry - currently using this one:

Me.Filter = "[" & Screen.PreviousControl.Controlsource & "] = "  & Chr(34)  & Screen.PreviousControl & chr(34)
0
 
mbizupCommented:
Did you include the following after that line?

Me.filteron=true
0
 
Dale FyeCommented:
Try:

Me.Filter = "[" & Screen.PreviousControl.Controlsource & "] = "  & Chr(34)  & Screen.PreviousControl.Value & chr(34)

But make sure you have the following line following that:

Me.FilterOn = true
0
 
Dale FyeCommented:
Or, better yet, try:

Dim strCriteria as string

strCriteria = "[" & Screen.PreviousControl.Controlsource & "] = "  & Chr(34)  & Screen.PreviousControl.Value & chr(34)

debug.print strCriteria

Me.Filter = strCriteria
me.FilterOn = true
0
 
marku24Author Commented:
thank you
0
 
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

Yes please also state if you are filtering for text or numeric values as well...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now