sort drop-down based on another drop-down

[aarontra]

I have 2 drop-down boxes and I want the first to sort the 2nd one. The 2nd drop-down box is not being sorted.

1st drop down: sort_Class_List
Row Source Type: Value List
Row Source data: 'class_name';'campus';'class_name';'start_date'
Default Value: 'campus'

On click:
Private Sub sort_Class_List_Click()
Class_List.Requery

End Sub

2nd drop down: Class_List

Row Source Type: Table/Query
Row Source:

SELECT Classes.ID, Classes.Class_name, Classes.Start_date, Classes.End_date, Classes.Campus
FROM Classes
ORDER BY Forms!Roster_Reports!sort_Class_List;


Thank you
Aaron

 

[willir]

You want the 2nd combo box to display in the order selected in the 1st combo box.

Use the AfterUpdate event procedure

Assuming your field names and control names on the form are correct...

Dim strSQL as String, strOrderBy as String

If Len(Nz(Me.sort_Class_List, "")) Then

   strSQL = "SELECT ID, Class_name, Start_date, End_date, " _
   & "Campus FROM Classes "

   strOrderBy =  "ORDER BY " & Me.sort_Class_List

   Me.Class_List.RowSource = strSQL & strOrderBy
   Me.Clase_List.Requery

End If

Richard

 

[TheAceMan1]

Willir . . . . .

[blue]Nicely laidout instructional![/blue]

Don't forget, when you change the RowSource of a combobox, [blue]it automatically requeries![/blue]

See Ya! . . . . . .

 

[aarontra]

Thank you willir!

That worked perfect. Now I can make expand on it to make it sort by more than one field.

Thanks again
Aaron