Sorting List Box

[MJD1]

thread702-1516961

I found the above thread where someone posted the below code that works great for creating a sort button for a list box on a form.
The only problem that I can't seem to solve is that I lose the formatting in the fields that I sort. For example, I have a total field that has the currency formating and when I use the sort button, I lose that formatting, same for my date column.

Would anyone be able to guide me pls in finding a way to maintain my formatting.
------------------------------
Private Sub cmd1_Click()

Dim rs As DAO.Recordset
Set rs = Me.List1.Recordset.Clone

If cmd1.Tag = "OrderID" Then
cmd1.Tag = "OrderID DESC"
Else
cmd1.Tag = "OrderID"
End If
rs.Sort = cmd1.Tag
Set rs = rs.OpenRecordset
Set Me.List1.Recordset = rs

end sub

---------------------

Thanks
Martin

 

[MajP]

Here is a more eloquent solution. I put a command button above each column with the column name. Then in the tag property I put the field name with Desc to start. Ex "OrderID Desc". Now I can have many command buttons.
Works like Outlook when you sort messages
Example

Option Compare Database
Option Explicit

Private Sub cmdOrderID_Click()
  Call sortList
End Sub

Private Sub cmdUnitPrice_Click()
  Call sortList
End Sub

Private Sub cmdDate_Click()
  Call sortList
End Sub

Public Sub sortList()
  Dim rs As DAO.Recordset
  Dim strTag As String
  strTag = ActiveControl.Tag
  Set rs = Me.List0.Recordset.Clone
  If Right(strTag, 4) = "Desc" Then
    strTag = Left(strTag, Len(strTag) - 4)
  Else
    strTag = strTag & " Desc"
  End If
  ActiveControl.Tag = strTag
  rs.Sort = strTag
  Set rs = rs.OpenRecordset
  Set Me.List0.Recordset = rs
End Sub

As for the formatting, I do not know why that would happen. You could post a stripped down version of your data on 4shared.com or another file sharing site. I could then take a look.