Open Filter Dialog box

[Gavx]

I would like to place a button on a form that when selected opens the Filter dialog box.
So I figure I select the field I want to filter on and then press a button that opens the Filter dialog box.
Using VBA how can I do this?

thanks

 

[arnelgp]

on design view of your form, add code to the Enter Event of
each controls you want to to filter on (the control should be Bound):

=SaveControlSource()

on your form VBA, define the function SaveControlSource()

Option Compare Database
Option Explicit

Dim m_controlSource As String
Dim m_controlName As String

Public Function SaveControlSource()
    Dim ctl As Control
    Set ctl = Screen.ActiveControl
    m_controlName = ctl.Name
    m_controlSource = ctl.ControlSource
End Function

on the click of a button, try this code:

Private Sub FilterButton_Click()

    Dim strCriteria As String

    ' Prompt user for a filter value
    strCriteria = InputBox("Enter filter criteria for " & UCase(m_controlName) & " (blank removes the filter)")
    
    ' Apply the filter
    If strCriteria <> "" Then
        Me.Filter = "[" & m_controlSource & "] Like '*" & strCriteria & "*'"
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If

End Sub

 

[CJ_London]

What about right clicking on the control?

 

[Gavx]

What about right clicking on the control?(

I can do this but I have to specify a value whereas the Filter dialog box (on some fields) presents the range of records and I merely choice one - this is the behaviour I want to obtain.

 

[MajP]

might be of interest

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk

 

[LarryE]

I would like to place a button on a form that when selected opens the Filter dialog box.
So I figure I select the field I want to filter on and then press a button that opens the Filter dialog box.
Using VBA how can I do this?

thanks

Using buttons On Click Event:
Application.CommandBars.ExecuteMso ("FiltersMenu")

 

[arnelgp]

Using buttons On Click Event:
Application.CommandBars.ExecuteMso ("FiltersMenu")

you must Setfocus to the Textbox to filter before you can call this function
otherwise Runtime error will occur.

 

[Gavx]

Using buttons On Click Event:
Application.CommandBars.ExecuteMso ("FiltersMenu")

After selecting the field I want to filter I selected the button and am getting runtime error of Method of ExecuteMso... failed?

 

[LarryE]

There might be a Library Reference missing. I'll see if I can find which one.

 

[LarryE]

After selecting the field I want to filter I selected the button and am getting runtime error of Method of ExecuteMso... failed?

This worked for me:

1. Set the focus to the form and control first:
2. Forms![FormName]![ControlName].SetFocus
3. DoCmd.RunCommand acCmdFilterMenu