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