Why do my continuous form filter boxes stop working when I change the record source?

[Adam Tippelt]

Situation:
I'm using a continuous form to display a lot of information stored in a datasheet. I'm using the form because it allows me to display multiple rows of information per 'record' which is needed as some of the records have a lot of text.

However, I've needed to apply both a form of filtering system, and a way of re-ordering the form so that it is alphabetical/numerical order, depending on whiching heading is clicked. I have achieved both of these. Firstly for the filters, I used the code suggested in the dedicated thread by NeoPa on this site, which I've copied in below. For the re-ordering, I've used what you could describe as a bit of a trick, as I couldn't find an easier way - I've created a separate query for each heading, where each query re-orders a specific row. Upon clicking the headers on the form, this changes the form's recordsource between these different queries, thus changing the order.

Problem:
Both the filters and the re-order queries work perfectly well on their own, however combining both the two seems to produce some a strange problem:

If I input a value into a filter box, it filters the box. If I then click a heading, it changes the recordsource, however doesn't re-filter the new information. If I then go into the filter box and hit enter for it to re-filter, it does nothing.

This is the coding for the filter process:

Private Sub RunFilter()
Dim strFilter As String, strOldFilter As String
 
'CheckFilter produces the new Filter depending on the values currently in
'the various filter boxes.
 
    strOldFilter = Me.Filter
    'FilterRef_ID - Numeric
    If Me!FilterRef_ID > "" Then _
        strFilter = strFilter & _
                    " AND ([Ref_ID]=" & _
                    Me!FilterRef_ID & ")"
    'FilterPageTitle - Text
    If Me!FilterPageTitle > "" Then _
        strFilter = strFilter & _
                    " AND ([PageTitle] Like '*" & _
                    Me!FilterPageTitle & "*')"
    'FilterPageAddress - Text
    If Me!FilterPageAddress > "" Then _
        strFilter = strFilter & _
                    " AND ([PageAddress] Like '*" & _
                    Me!FilterPageAddress & "*')"
    'FilterFieldElement - Text
    If Me!FilterFieldElement > "" Then _
        strFilter = strFilter & _
                    " AND ([Field/Element] Like '*" & _
                    Me!FilterFieldElement & "*')"
    'FilterTestFor - Text
    If Me!FilterTestFor > "" Then _
        strFilter = strFilter & _
                    " AND ([TestFor] Like '*" & _
                    Me!FilterTestFor & "*')"
    'FilterType - Text
    If Me!FilterType > "" Then _
        strFilter = strFilter & _
                    " AND ([TypeOfTest] Like '" & _
                    Me!FilterType & "*')"
    'FilterPriority - Text
    If Me!FilterPriority > "" Then _
        strFilter = strFilter & _
                    " AND ([Priority] Like '" & _
                    Me!FilterPriority & "*')"
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.Filter = strFilter
        Me.FilterOn = (strFilter > "")
    End If
End Sub
 
 

Each filter box calls this process in it's AfterUpdate event.

At first I thought that this is what was causing the problem, as changing the record source does not 'update' the filter boxes. However I have tried changing the event to things like On Current, calling the RunFilter process on the Form's AfterUpdate/OnCurrent events - I've even tried assigning the value of the filter to a variable, changing the value of the filter to a hardcoded value and then changing it back to the filter value stored in the variable. Still none of these solve the problem.

So I put my query to this community, in the hope that someone can solve my problem with what will probably be a very obvious answer.

Thanks in advance.

[TheSmileyCoder]

Well if you simply enter the textbox and click enter without changing anything the AfterUpdate will not run (since it was not updated).

I would suggest to simply add the RunFilter at the bottom of whatever procedure you have linked to clicking a heading.

[Adam Tippelt]

Tried that. Didn't work.

It still required me to change the text in the filter box first, and then it would filter.

[TheSmileyCoder]

Im not 100% sure about this, but try moving the Me.FilterOn outside of the if-statement.

    If strFilter <> strOldFilter Then 
        Me.Filter = strFilter 
        Me.FilterOn = (strFilter > "") 
    End If 

[Adam Tippelt]

Bingo. I knew it would be an easy fix that I'd overlooked.

Thanks Smiley.