Rhdr Rhdr - 2 months ago 4
SQL Question

Sort List-box Conditionally without changing the Row-source

If I got a list box say lstABC with 3 columns A, b & c.
And there is 3 sorting buttons say btnSortA, btnSortB, btnSortC.
Is there a way to use the on_click event on the buttons that allow the user to sort lstABC without changing lstABC.rowsource every time?

I was trying to achieve something in the line of this:

Private sub btnSortA_Click()
lstABC.Orderby = "ColumnA ASC"
me.lstABC.OrderByOn = True
End Sub


Sure I could build SQL strings and set/requery lstABC.rowsource = string(for every button), but I am looking for something simple and efficient that don't overcomplicated things. Thanks

Answer

In my opinion, you can not make it simplier than changing the row source. You could also read all rows in the listbox and reinsert all the rows in the right order with .addItem...

Private Sub sortListbox(criteria As String)
  Dim strSQL As String

  strSQL = "SELECT A,B,C FROM TableName" _
    & " ORDER BY " & criteria

  Me.yourListboxController.RowSource = strSQL
  Me.yourListboxController.Requery
End Sub

And you just add in the click event :

Private Sub btnSortA_Click()
  Call sortListbox("A")
End Sub

Private Sub btnSortB_Click()
  Call sortListbox("B")
End Sub

Private Sub btnSortC_Click()
  Call sortListbox("C")
End Sub

I do not think you can have simplier than that.

Comments