Sort on Cascading Combo Box

[Gaddy]

Hi there,

I have a form which has two combo boxes:



- cboLocationID
- cboManagerID

I have the following code in cboLocationID:

Private Sub cboLocationID_AfterUpdate()    Dim sManagerSource As String


    sManagerSource = "SELECT [tblManager].[ManagerID], [tblManager].[Forename] & ' ' & [tblManager].[Surname]" & _
                     "FROM [tblManager] " & _
                     "WHERE [LocationID] = " & Me.cboLocationID.Value
    Me.cboManagerID.RowSource = sManagerSource
End Sub

When I click into cboLocationID (the first combo box) it then filters cboManagerID (the second combo box) to show only the relevant records pertaining to a certain location. This is what I need it to do, however, I cannot get it to sort by name order; it will only sort by ManagerID (as it does with the code above), or if I put ORDER BY code in, I get errors depending where it is placed, or I get no results shown.

The order needed - as it concatenates the managers forename and surname, e.g. Sam Fisher - is by the full name not ID.


Can anyone assist me?

P.S. My VBA and SQL knowledge is practically non-existent.

[rpeare]

All you need is an ORDER BY clause

WHERE [LocationID] = " & cboLocation & " ORDER BY SURNAME, FORENAME"

[Gaddy]

Thank you very much.

I've altered my code so it now reflects this:

Private Sub cboLocationID_AfterUpdate()    
    Dim sManagerSource As String
    
    sManagerSource = " SELECT [tblManager].[ManagerID], [tblManager].[Forename] & ' ' & [tblManager].[Surname] " & _
                     " FROM [tblManager] " & _
                     " WHERE [LocationID] = " & Me.cboLocationID.Value & " ORDER BY Forename, Surname"
    Me.cboManagerID.RowSource = sManagerSource
    
End Sub

I altered what you gave me slightly and also put in a space between the first double quotes and the statement, i.e. " WHERE" - has done the trick.