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.
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
- From the File menu, choose Save As Query. Save the filter as Customer Sort Filter.
- Save and close the form and close the filter.
Create the following new macro and save it as Apply Sort Macro:
Apply Sort Macro Actions ------------------------------------ Action: ApplyFilter Filter Name: Customer Sort Filter
- Open the Customer Filter Test form. Click the Contact Name toggle button.
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.
REFERENCES
Microsoft Access "Language Reference," version 1.0, "Choose Function," page 65
For more information on the Choose() function, search for "Choose" using the Microsoft Access Help menu.
Additional query words: change column dynamic sorting
Keywords : kbusage
Issue type : kbhowto
Technology :
Last Reviewed: November 4, 2000 |