none
Prompt when filtering has changed

    Question

  • I have a form that used to be bound to a table. A 2-character text field with half-a dozen different values used to be used for filtering (place cursor on control and click funnel icon), this used to display the normal prompts followed by all the possible values with checkboxes.

    Now I changed the data source to be 'SELECT * FROM tablename WHERE ...', this particular control now acts differently when filtering. I get the standard prompts but dont get the list of values and the checkboxes.

    How can I get these values and checkboxes back. I cannot do without them.

    Now I get:   I used to get:

    Saturday, July 12, 2014 9:04 AM

Answers

  • Copying and pasting did not help. Nor did compact/repair.

    I ended up converting the record source to a query and putting the query name in the record source. That fixed the problem. Another Access 2010 mystery!

    Saturday, July 12, 2014 5:02 PM

All replies

  • The filter dialogue lists all values in the form's recordset from the column to which the current control is bound.  If the recordset is restricted by a WHERE clause then only those values returned by the restricted recordset will be listed.  So the first assumption would be that in your case the column in question has no values in the recordset returned when you restrict the query by the WHERE clause.  If this is not the case, and you are sure that the recordset includes rows with values in the column in question, then this is not normal behaviour, and I cannot reproduce it in Access 2007.  I can't speak for other versions.

    PS:  From your screenshot the Assigned column appears to have a value of KM in the current record, so it would appear that you are not experiencing the normal behaviour of the dialogue.  Try copying the form and pasting it under a new name.  This often will cure any corruption.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Saturday, July 12, 2014 12:49 PM Post script added
    Saturday, July 12, 2014 12:44 PM
  • Hi,

    The only time I know that could happen is when there's a lot (not sure exactly how many) of values to display for the checkboxes. So, how many unique values are there in your table for that column?

    Saturday, July 12, 2014 2:58 PM
  • As I said previously half a dozen values. The odd thing is I have another form with a similar record source but the WHERE has IS NOT NULL instead of IS NULL and it works fine and displays the values and checkboxes.
    Saturday, July 12, 2014 4:56 PM
  • True, but I don't see how that that can be the reason here as the number of values returned should be at most the same, and more likely less, by virtue of the restriction of the form's recordset by the WHERE clause, than when the set is showing correctly.  I can't reporoduce the bevaviour.

    Ken Sheridan, Stafford, England

    Saturday, July 12, 2014 5:01 PM
  • Copying and pasting did not help. Nor did compact/repair.

    I ended up converting the record source to a query and putting the query name in the record source. That fixed the problem. Another Access 2010 mystery!

    Saturday, July 12, 2014 5:02 PM