check if a filter is applied after ac cmdfiltermenu (1 Viewer)

chrisjames25 · Jan 14, 2019

Hi i have the following code and it it brings up a filter menu much like in a datasheet filter option.

Code:

Dim Items As Integer

Items = Me.RecordsetClone.RecordCount
If Items = 0 Then
Me.FilterOn = False

Else

DoCmd.GoToControl "Txt_Column1"
DoCmd.GoToRecord , "", acFirst
DoCmd.RunCommand acCmdFilterMenu
End If

Works fine. But as i choose the filters to apply after the event i have no clue how to check whether a filter has actaully be applied and if one has bring up a pic suggesting filter applied. For example i could double click on the label and decide not to filter any data or i could choose just one item form the list. I have no clue how to test if a filter has been applied or not.

Any ideas hugely appreciated .

MajP · Jan 14, 2019

Not sure how you are doing this but I would think if the filter is on and there is a filter value then the filter is applied.

if me.filteron and me.filter <> "" then msgbox "Filter Applied"

chrisjames25 · Jan 14, 2019

cheers. so what event do i put that code in?

MajP · Jan 14, 2019

I would assume in the apply filter, but depends how this is done. Many events do not occur if done via code. Example, you update a textbox in code it does no fire any events.

Code:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

End Sub

chrisjames25 · Jan 14, 2019

Superb. Worked a treat. Many thanks.

sxschech · Jan 15, 2019

I added a text box to my form to show what the filter was. Here is code if of use.

Put code in a general module so it could be used on other forms too.

Code:

Function DisplayFilter()
'Show the current filter on the active
'form.  Strip out table/queryname if
'bound, otherwise show t/q name.
'Remove parens to save display space.
'https://www.tek-tips.com/viewthread.cfm?qid=245120
'20181219
    Dim myform As Form
    Dim stFilter As String
    Dim stRecSource As String
    
    Set myform = Screen.ActiveForm
    
    If myform.FilterOn = True Then
        stFilter = myform.Filter
        stRecSource = myform.RecordSource
        If InStr(stFilter, stRecSource) > 0 Then
            stFilter = Replace(Replace(stFilter, stRecSource, ""), ".", "")
        End If
        DisplayFilter = Replace(Replace(stFilter, "(", ""), ")", "")
    End If
End Function

On the actual form put a text box.
I called mine txtFilter.
In the text box's control source: =DisplayFilter()

okiearcher · Jul 23, 2022

chrisjames25 said:
Hi i have the following code and it it brings up a filter menu much like in a datasheet filter option.

Code:

Dim Items As Integer Items = Me.RecordsetClone.RecordCount If Items = 0 Then Me.FilterOn = False Else DoCmd.GoToControl "Txt_Column1" DoCmd.GoToRecord , "", acFirst DoCmd.RunCommand acCmdFilterMenu End If

Works fine. But as i choose the filters to apply after the event i have no clue how to check whether a filter has actaully be applied and if one has bring up a pic suggesting filter applied. For example i could double click on the label and decide not to filter any data or i could choose just one item form the list. I have no clue how to test if a filter has been applied or not.

Any ideas hugely appreciated .

I chrisjames25,

I use a routine like this one to determine if I am viewing the entire recordset:

Function IsFiltered() As Boolean
Dim rs As DAO.Recordset
Dim lngTotalRecords As Long
Dim lngRSCount As Long

Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
lngRSCount = Me.RecordsetClone.RecordCount

lngTotalRecords = DCount("*", "SourceTableName")

If lngTotalRecords = lngRSCount Then
IsFiltered = False
ElseIf lngTotalRecords > lngRSCount Then
IsFiltered = True
End If

MsgBox "Filtered status: " & IsFiltered

Set rs = Nothing
End Function

check if a filter is applied after ac cmdfiltermenu (1 Viewer)

chrisjames25

Registered User.

MajP

You've got your good things, and you've got mine.

chrisjames25

Registered User.

MajP

You've got your good things, and you've got mine.

chrisjames25

Registered User.

sxschech

Registered User.

okiearcher

New member

Similar threads

Users who are viewing this thread