marku24
asked on
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?
ASKER
thank you - it could be one of three text fields. How do I tell the script which is my current / active field?
Give this a try:
Me.Filter = Me.ActiveControl.Controlso urce = & Chr(34) & Me.ActiveControl & chr(34)
Me.Filter = Me.ActiveControl.Controlso
Or:
Me.Filter = Me.ActiveControl.Controlso urce & " = " & Chr(34) & Me.ActiveControl & chr(34)
Me.Filter = Me.ActiveControl.Controlso
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
Private Sub cmd_FilterToggle_Click
me.FilterOn = Not me.FilterOn
End Sub
ASKER
The filter doesn't work - Run time error 438: Object doesn't support this property or method. any ideas?
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.Con trolsource = & Chr(34) & Screen.PreviousControl & chr(34)
Me.Filter = Screen.PreviousControl.Con
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The filter off does work but the filter code above does not. It doesn't error but does not filter.
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?
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?
ASKER
sorry - currently using this one:
Me.Filter = "[" & Screen.PreviousControl.Con trolsource & "] = " & Chr(34) & Screen.PreviousControl & chr(34)
Me.Filter = "[" & Screen.PreviousControl.Con
Did you include the following after that line?
Me.filteron=true
Me.filteron=true
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or, better yet, try:
Dim strCriteria as string
strCriteria = "[" & Screen.PreviousControl.Con trolsource & "] = " & Chr(34) & Screen.PreviousControl.Val ue & chr(34)
debug.print strCriteria
Me.Filter = strCriteria
me.FilterOn = true
Dim strCriteria as string
strCriteria = "[" & Screen.PreviousControl.Con
debug.print strCriteria
Me.Filter = strCriteria
me.FilterOn = true
ASKER
thank you
<No Points wanted>
Yes please also state if you are filtering for text or numeric values as well...
Yes please also state if you are filtering for text or numeric values as well...
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