Access 2010 filter by form problem


  • I have an Access program with the forms recordset populated by a ado recordset with data from a SQL Sever stored procedure.

    Fiter by forms works nicely under Access 2003.

    I migrated to Office 2010 and converted my Access program to the 2007 format, recompiled the whole and started to run with Access 2010.

    And now I cannot filter by forms anymore.

    I can use the new shortcut menu but it never actually filters my data at all on the form.

    On the internet I found someone who found out that replacing a dll with the one from Office 2007 does fix this.

    And it actually does but surely this cannot be the final solution.

    Any one experienced the same behaviour and found a solution.

    Friday, June 15, 2012 12:48 PM

All replies

  • Hi,

    First, I want you to confirm whether the steps of converting Access 2003 to Access 2010 is right:

    1. On the File tab, click Open.
    2. In the Open dialog box, select and open the Access 2000 or Access 2002 - 2003 database (.mdb) that you want to convert.
    3. On the File tab, click Save & Publish, and then, under Database File Types click Access Database (*.accdb).
    4. Click Save As.

    If any database objects are open when you click Save As, Access prompts you to close them prior to creating the copy. Click Yes to make Access close the objects, or click No to cancel the entire process. If needed, Access will also prompt you to save any changes.

    1. In the Save As dialog box, type a file name in the File name box, and then click Save.

    Access creates the copy of the database, and then opens the copy. Access automatically closes the original database.

    Quote from:

    Secondly, try to reapply the filter in the Access 2010 to check the issue. Please refer to the “Filter by form” part of the following link to see the steps:

    Jaynet Zhang

    TechNet Community Support

    Monday, June 18, 2012 4:39 AM
  • Hi Jaynet,

    I followed the Save and Publish  instruction, but the filtering problem remained.

    I noticed when I bind a forms recordset to a plain access table or query,  filtering in the forms fields works fine.

    However when bound to an ado recordset (With SQL server data comming from SP's) the filtering doesn't work and keeps on showing all records.

    I am still stuck here.


    Friday, June 22, 2012 9:05 AM
  • Hi,

    Based on my research, if we use the ADO Recordset's Filter property and/or Sort property, so we need to provide a custom button that will change the filter directly upon the recordset.

    If you specify either Sort or Filter to an ADO recordset bound on a form, the form will not automatically display the changes made to sorting or filtering. Requerying or Refreshing will not work either. To update the visible changes, here is an example for using inside a form's module:

    Here’s the code:

    Me.Recordset.Sort = "FieldName"

    Set Me.Recordset = Me.Recordset

    It is not possible to use the forms Filter or Order By properties when it is bound to an ADO recordset. Also, the default shortcut menus and ribbon menus for filtering and sorting do not have any effect on ADO recordsets and my produce an error if a user attempts to use them.

    Jaynet Zhang

    TechNet Community Support

    Monday, June 25, 2012 9:04 AM
  • Hi Janet,

    Youre suggestion will work. I know this because the same application also uses this filtering technique.

    It is strange that on Access 2003 the filtering on the ado recordset - with the shortcut menu - works very well.

    (Un) fortunatly the organization where I created the application for ( and who are using it > 10 years now) downgraded their

    application servers from Office 2010 back to Office 2003.

    thanks anyhow for you effort

    Thursday, June 28, 2012 3:34 PM
  • HI Jannet :

        I have a application written on Access 2003(32bits) for ten years & working vary well.  I want to switch to Aceess 2013(64bits) now ! 

    Problem is:

        The filtering functuon  is correct on the first time after form open , but  not corrent on next change me.filter . I close the form & restore the filter and reopen the form then  it work very well!

    I modify the code that everytime change filter  close & reopen the form it work but  vary bad ! I donot like it!

        I think it have some method to solve this problem!

    I had been trying but no use :

    Private Sub Form_Activate()
    End Sub

    my code:

    Public Sub SetFilterTask()
        Call SetFilterTaskOnly  
        Dim stLinkCriteria As String
        Call WaitMSec(100)
        DoCmd.OpenForm "frmTradingApyRecAccess", , , stLinkCriteria  
    End Sub

    Public Sub WaitMSec(LastMSec As Integer)

        For waiting_availability = LastMSec To 1 Step -1
    End Sub

    my email from AndrewWang

    Wednesday, December 24, 2014 8:58 AM