By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

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

100+
P: 137
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub RunFilter()
  2. Dim strFilter As String, strOldFilter As String
  3.  
  4. 'CheckFilter produces the new Filter depending on the values currently in
  5. 'the various filter boxes.
  6.  
  7.     strOldFilter = Me.Filter
  8.     'FilterRef_ID - Numeric
  9.     If Me!FilterRef_ID > "" Then _
  10.         strFilter = strFilter & _
  11.                     " AND ([Ref_ID]=" & _
  12.                     Me!FilterRef_ID & ")"
  13.     'FilterPageTitle - Text
  14.     If Me!FilterPageTitle > "" Then _
  15.         strFilter = strFilter & _
  16.                     " AND ([PageTitle] Like '*" & _
  17.                     Me!FilterPageTitle & "*')"
  18.     'FilterPageAddress - Text
  19.     If Me!FilterPageAddress > "" Then _
  20.         strFilter = strFilter & _
  21.                     " AND ([PageAddress] Like '*" & _
  22.                     Me!FilterPageAddress & "*')"
  23.     'FilterFieldElement - Text
  24.     If Me!FilterFieldElement > "" Then _
  25.         strFilter = strFilter & _
  26.                     " AND ([Field/Element] Like '*" & _
  27.                     Me!FilterFieldElement & "*')"
  28.     'FilterTestFor - Text
  29.     If Me!FilterTestFor > "" Then _
  30.         strFilter = strFilter & _
  31.                     " AND ([TestFor] Like '*" & _
  32.                     Me!FilterTestFor & "*')"
  33.     'FilterType - Text
  34.     If Me!FilterType > "" Then _
  35.         strFilter = strFilter & _
  36.                     " AND ([TypeOfTest] Like '" & _
  37.                     Me!FilterType & "*')"
  38.     'FilterPriority - Text
  39.     If Me!FilterPriority > "" Then _
  40.         strFilter = strFilter & _
  41.                     " AND ([Priority] Like '" & _
  42.                     Me!FilterPriority & "*')"
  43.     If strFilter > "" Then strFilter = Mid(strFilter, 6)
  44.     If strFilter <> strOldFilter Then
  45.         Me.Filter = strFilter
  46.         Me.FilterOn = (strFilter > "")
  47.     End If
  48. End Sub
  49.  
  50.  
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.
Jan 27 '11 #1

✓ answered by TheSmileyCoder

Im not 100% sure about this, but try moving the Me.FilterOn outside of the if-statement.
Expand|Select|Wrap|Line Numbers
  1.     If strFilter <> strOldFilter Then 
  2.         Me.Filter = strFilter 
  3.         Me.FilterOn = (strFilter > "") 
  4.     End If 

Share this Question
Share on Google+
4 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 27 '11 #2

100+
P: 137
Tried that. Didn't work.

It still required me to change the text in the filter box first, and then it would filter.
Jan 27 '11 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Im not 100% sure about this, but try moving the Me.FilterOn outside of the if-statement.
Expand|Select|Wrap|Line Numbers
  1.     If strFilter <> strOldFilter Then 
  2.         Me.Filter = strFilter 
  3.         Me.FilterOn = (strFilter > "") 
  4.     End If 
Jan 27 '11 #4

100+
P: 137
Bingo. I knew it would be an easy fix that I'd overlooked.

Thanks Smiley.
Jan 27 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.