ACC2000: How to Sort Form Records Using Combo Box Selection

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

To use this method, you create a form based on a table or query. You then create a combo box; the combo box's row source is a value list that contains 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 queries 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.

To sort the values on a form based on field names that you select from a combo box, follow these steps:

1. Open the sample database Northwind.mdb.
2. Create a new form based on the Customers table in Design view. If the field list is not displayed, click Field List on the View menu, 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.
   4. Continue to step 4.

   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.
   3. Continue to step 4.
   NOTE: Steps 4 through 6 apply to both methods.
   
   
4. Add the following event procedure to the AfterUpdate event of the combo box.

   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.

For more information about AfterUpdate event, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type AfterUpdate Event in the Office Assistant or the Answer Wizard, and then click Search to view the topic.