ACC95: FilterName Argument of OpenForm Macro Action Doesn't Sort

ID: Q154421

The information in this article applies to:

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When the FilterName argument of an OpenForm action is based on a query or an SQL SELECT Statement that includes the sorting of a field or fields in ascending or descending order, sorting will be ignored when the OpenForm action is executed.


CAUSE

Microsoft Access 7.0 does not automatically set the form's OrderByOn property to True when the FilterName argument of the OpenForm action uses a filter that is sorting by one or more fields.


RESOLUTION

To work around this problem, follow these steps:

  1. Add a new SetValue action to immediately follow the OpenForm action.


  2. Set the Item argument of the SetValue action to
    Forms!<formname>.OrderByOn

    where <formname> is the name of the form being opened.


  3. Set the Expression argument of the SetValue action to True



STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 7.0. This problem no longer occurs in Microsoft Access 97.


MORE INFORMATION

If the filter has criteria to limit the number of records to be viewed by the form, the form will, in fact, be limited to only those records which meet the criteria's condition; but all sorting, however, will be ignored.

Steps to Reproduce Problem


  1. Open the sample database Northwind.mdb.


  2. Create a new query in Design view.


  3. In the Show Table box, add the Customers table to the query and drag all the fields to the QBE grid.


  4. Set Sort for the City column to Ascending.


  5. Close and save the query as Query1.


  6. Create the following new macro: 
    
      Macro Action
      ------------
      OpenForm

      Action Arguments
      ---------------------------------
         Form Name: Customers
         View: Form
         Filter Name: Query1
         Where Condition: <leave blank>
         Data Mode: Edit
         Window Mode: Normal


  7. Save the macro as Macro1.


  8. On the Run menu, click Start. Note that the records on the form are not sorted by City as expected.



REFERENCES

For more information about the ApplyFilter action, search for "ApplyFilter Action" using the Microsoft Access 7.0 Help Index.

Additional query words: ORDER BY 


Keywords          : kbusage McrArg 
Version           : 7.0
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: April 29, 1999