none
Access 2010 filter by form problem

    Question

  • 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:

    http://office.microsoft.com/en-us/access-help/convert-a-database-to-the-accdb-file-format-HA010341552.aspx

    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:

    http://office.microsoft.com/en-us/access-help/apply-a-filter-to-view-select-records-in-an-access-database-HA010341691.aspx#formfilter


    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.

    Regards

    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