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

• Microsoft Access 2.0, 7.0, 97

SUMMARY

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

This article describes how to sort the values on a form based on field names 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 a 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 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 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 Default View property to Continuous Forms.

3. Use one of the following two methods:

   Method 1:

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

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

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

   Method 2:

       a. Change the RecordSource property of the form to:
   

            Select * From Customers Order By '["& Me!cboLookup &"]';
   
       b. Add a combo box to the form using the Combo Box Control Wizard.
          Select "I will type in values that I want" when prompted. 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-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 selected from the combo box.

REFERENCES

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