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

• Microsoft Access version 7.0

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.