ACC1x: How to Sort on Different Fields Dynamically

ID: Q109715

The information in this article applies to:

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:

  1. Open the sample database NWIND.MDB.


  2. 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


  3. Create the following option group on the form: 
    
       Option Group: Field Selector
          AfterUpdate: Apply Sort Macro


  4. 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


  5. View the form in Form view.


  6. Choose Edit Filter/Sort from the Records menu.


  7. 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


  8. From the File menu, choose Save As Query. Save the filter as Customer Sort Filter.


  9. Save and close the form and close the filter.


  10. Create the following new macro and save it as Apply Sort Macro: 
    
       Apply Sort Macro Actions
       ------------------------------------
       Action:  ApplyFilter
          Filter Name: Customer Sort Filter


  11. 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 FmsOthr 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 2, 1999