Microsoft KB Archive/109715

{|
 * width="100%"|

ACC1x: How to Sort on Different Fields Dynamically

 * }

Q109715

-

The information in this article applies to:


 * Microsoft Access versions 1.0, 1.1

-

SUMMARY
This article demonstrates how to design a filter that you can use to dynamically select the field on which to sort records.

MORE INFORMATION
The example below uses the Choose function in a filter to sort the records displayed on a form. Toggle buttons in an option group are used to sort on different fields. The Choose function accepts the value of the option group as its first argument, and selects the field from the subsequent list based on that value.

The following example describes how to create a form and a filter that demonstrate this technique:

 Open the sample database NWIND.MDB.  Create the following new form:

      Form: Customer Filter Test Caption: Customer Filter Test RecordSource: Customers Text Box: Company Name ControlSource: Company Name Text Box: Contact Name ControlSource: Contact Name Text Box: Address ControlSource: Address   Create the following option group on the form:

      Option Group: Field Selector AfterUpdate: Apply Sort Macro   Place the following toggle buttons in the option group:

      Toggle Button: CompName OptionValue: 1 Caption: Company Name Toggle Button: ContName OptionValue: 2 Caption: Contact Name Toggle Button: Addr OptionValue: 3 Caption: Address  View the form in Form view. Choose Edit Filter/Sort from the Records menu.  Create the following calculated field:

NOTE: In the following example, an underscore (_) is used as a line- continuation character. Remove the underscore when re-creating this example.

<pre class="FIXEDTEXT">      Field: Choose(Forms![Customer Filter Test]![Field Selector], _          [Company Name], [Contact Name], [Address]) Sort: Ascending ' The order of the fields in the Choose function must match the ' order of the OptionValue of the toggle buttons </li> From the File menu, choose Save As Query. Save the filter as Customer Sort Filter.</li> Save and close the form and close the filter.</li>  Create the following new macro and save it as Apply Sort Macro:

<pre class="FIXEDTEXT">      Apply Sort Macro Actions Action: ApplyFilter Filter Name: Customer Sort Filter </li> Open the Customer Filter Test form. Click the Contact Name toggle button.</li></ol>

The records on the form will be sorted by the Contact Name field. Clicking the other toggle buttons will cause the records to be sorted on the fields specified by the toggle buttons.