check if a filter is applied after ac cmdfiltermenu

[chrisjames25]

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

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]

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]

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

 

[MajP]

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.

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

End Sub

 

[chrisjames25]

Superb. Worked a treat. Many thanks.

 

[sxschech]

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.

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]

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

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