How to sort filtered ListBox

Asked by At

I have created search box for a ListBox in form. Search function works as intended, but I would like results to show in ascending order based on first column. I am having trouble understanding how to do that. My code:

Private Sub searchTB_Change()

Dim strSource As String, strSearch As String
'code for searching records
    strSearch = Replace(searchTB.Text, "'", "''")
    strSource = "SELECT [ID], [VP_veiklioji], [VP_invented_name], [Pareisk_pav], [Par_gavimo_data], [Finished] " _
        & "FROM TerPar_Oldsys " _
        & "WHERE [ID] LIKE '*" & strSearch & "*' " _
        & "Or [VP_veiklioji] LIKE '*" & strSearch & "*' " _
        & "Or [VP_invented_name] LIKE '*" & strSearch & "*' " _
        & "Or [Pareisk_pav] LIKE '*" & strSearch & "*' " _
        & "Or [Par_gavimo_data] LIKE '*" & strSearch & "*' " _
        & "Or [Finished] LIKE '*" & strSearch & "*' " _
       'up to this part everything works
        'line below do not work (it gets whole code in red in debugger) and I do not know how to fix it
        & "ORDER BY"  "[ID]" ASC," 

    'bottom two lines work too, I have tryed DoCmd.SetOrderBy but do not understand how to make it work either

    Me.oldListBox.ColumnWidths = "1.5 cm; 3 cm; 4 cm; 4 cm; 2 cm; 0.6 cm"
    Me.oldListBox.RowSource = strSource

End Sub

EDIT: In my opinion it is not duplicate, since I am aiming at totally different architecture which turns out needed only quotes removal as Gustav suggested.

1 Answers

1
Gustav On Best Solutions

Remove the quotes and the comma:

& "ORDER BY [ID] ASC"