ACC: How to Sort Form Records Using Field Names from Combo Box

ID: Q153579

The information in this article applies to:

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes how to sort the values on a form based on field names that you select from a combo box.


MORE INFORMATION

To use this method, you create a form based on a table or query. You then create a combo box whose row source is a value list containing the field names to be used for sorting the records on the form. The record source for the form is an SQL statement that sorts the data on the form based on the values selected in the combo box. The AfterUpdate event of the combo box contains a procedure that requeries the record source of the form each time that you select a different field to sort by. This example uses the Customers table from the Northwind sample database.

  1. Open the sample database Northwind.mdb (or Nwind.mdb for Access 2.0).


  2. Create a new form based on the Customers table in Design view. If the field list is not display, on the View menu, click Field List, and then drag all of the fields from the field list to the form. Set the form's DefaultView property to Continuous Forms.


  3. Use one of the following two methods:

    Method 1:

    1. Create a combo box, and set the Name property to cboLookup.


    2. Set the RowSourceType property to Field List. This will show all fields.


    3. Set the RowSource property to the same table or query that the form is based on.


    Method 2:

    1. Change the RecordSource property of the form to:
      Select * From Customers Order By '["& Me!cboLookup &"]';


    2. Use the Combo Box Control Wizard to add a combo box to the form. When prompted, select "I will type in values that I want." Select a single column and type in the field names to sort on. Accept the defaults for the remainder of the wizard. Name the combo box cboLookup.


    NOTE: Steps 4 through 6 apply to both methods.


  4. Add the following event procedure to the AfterUpdate event of the combo box.

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code. 
    
      Sub cboLookup_AfterUpdate()
          Me.RecordSource = "Select * From Customers ORDER By _
                            [" & Me!cboLookup & "];"
      End Sub


  5. Save the form and open it in Form view.


  6. Click the combo box and select a field name.

    Note that the data in the form is sorted based on the field name that you select from the combo box.



REFERENCES

For more information about the AfterUpdate event, search the Help Index for "AfterUpdate," or ask the Microsoft Access 97 Office Assistant.

Additional query words: Sort Field Names Combo Box howto 


Keywords          : FmsCmbo 
Version           : 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 23, 1999