Changing How Form Looks When Filter is Applied

[LQ]

I have a form that users search using Filter By Form. Some of the users, however, get confused trying to figure out when the filter is on and when it is not. So my idea was to have a little label pop up when the form was being filtered. After searching and searching, I finally found this bit of code and adapted it to my form. I put it in the form's On Timer event and set the timer interval to 100.

Private Sub Form_Timer()
' Makes the label flash when a filter
' has been applied so the user knows a filter
' is being used.
If Me.Filter <> " " Then
' Find out what the current color is
' and change it to the other color
' so it looks like the label is flashing.
If Me.lblFilter.ForeColor = 0 Then
Me.lblFilter.ForeColor = 255
Else
Me.lblFilter.ForeColor = 0
' Change the caption.
Me.lblFilter.Caption = "FILTERED"
End If
Else
' No filter exists so make the label static.
Me.lblFilter.Caption = "UnFiltered"
Me.lblFilter.ForeColor = 0
End If

The problem is, this code makes the word FILTERED flash red when the filter has been applied (which is good) but when you click on the Search button to start a new Filter by Form, the word FILTERED still appears, although it does not flash. I am a beginner at VBA and try as I might, I cannot figure out why this does this. My "Search" cmd button, btw, applies filter by form and clears the grid. I don't know if this has something to do with my problem.

Thanks for any suggestions you might have and sorry this is long-winded.

 

[KDg]

Hi LQ,

i've never used this but there's an OnFilter event for the form, you may be able to do something with code like the below

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
   If ApplyType = 0 Then
        'filter off
        me.lblName.Visible = False
    Else
        'filter being applied
        me.lblName.Visible = True
    End If
End Sub

HTH

Drew

 

[LQ]

Well, I tried what you suggested, but my label is still always visible, whether or not the form is in Filter by Form view. I set the label's visible property to No, btw, when I put in the code in the Apply Filter event.

Why isn't this working? It seems like the code you suggested, Drew, or the code in the Timer event *should* have worked. Would the fact that the form initially opens in Filter by Form view affect anything?

I am sooo frustrated! Any suggestions will be welcomed!

 

[Fornatian]

try testing the FilterOn value in VBA like:

If FilterOn = True Then
'do label stuff
Else
'hide the label
End if


Ian

 

[LQ]

One other thing I meant to mention...when I first go into the form from the main switchboard, the "filter" label does not show up. But the first time I apply the filter, the label pops up and then does not disappear, even when I toggle back and forth between filtered records and the Filter by Form view. Am I correct in thinking that in the Filter by Form view, the filter has not yet been applied, so according to my code, the label should not show up?

Ian -- I am not sure what you mean by testing the value. Can you please elaborate?
I have tried putting the code in various places, I have tried my code and Drew's code and nothing seems to work even though logic would seem to dictate that it should. I know I must be doing something wrong.

Thanks

 

[Fornatian]

if you do a search for FilterOn in VBA you can read up on it. Basically you can read whether a filter is applied or not because FilterOn returns True or False depending on whether there is a filter in place.

I've never used it personally but I am sure it will fit your needs (unless there are some restrictions to it)

Hope that helps a little - no promises made though!

Ian

 

[LQ]

I'm like a terrier that won't let go of something once its teeth have sunk into it.

This is going to be kinda long-winded so please excuse me...

I have been fiddling with this form and have finally got it to display a label when the form's filter is on. I put the code from my first post in the On Timer event. But what I realized was that people were doing a search using filter by form when they first entered the form and then going right from there and clicking Start New Record, which meant that they still technically had the filter applied even though they were entering new data. So I put the following code behind the start new record button so that the filter would be taken off.

Private Sub Newrec_Click()
On Error GoTo Newrec_Click_Err
If FilterOn = True Then
FilterOn = False
ElseIf (IsNull(Me![Combo20]) Or Len(Combo20) = 0) And Not IsNull(Me.Question) Then
MsgBox "You must enter a payor!"
ElseIf (IsNull(Me![IssueStatus]) Or Len(IssueStatus) = 0) And Not IsNull(Me.Question) Then
MsgBox "You must enter an issue status!"
DoCmd.GoToControl "[issuestatus]"
Else: DoCmd.GoToRecord , "", acNewRec
End If

Newrec_Click_Exit:
Exit Sub

Newrec_Click_Err:
MsgBox Error$
Resume Newrec_Click_Exit

End Sub

The thing is, now that I have the code to turn off the filter on top of the code that is checking for some blank entries (don't worry, I also put the data validation code into the before update event), I have to click once and then click *again* to get a new, blank record. Why does this happen? I can't figure this out -- is it a syntax issue or what?

Thanks, as always, for any suggestions!

 

[Rich]

Isn't it easier to hide the new rec button when apply the filter?

 

[LQ]

That would be a partial solution, Rich, and it may be worth implementing. The problem is that users are getting filtered records and then there is a blank record at the end where they could mistakenly enter search criteria. I am seeing a lot of entries in the table that are insurance codes with a wildcard at the end, so I know that they are getting confused.