Controlling a How a ListView Control Sorts Items Using Callbacks

Next to the Treeview, I think the Listview is my favourite control to program with in Access. Like the treeview, it's ActiveX. There's a set of pages about it in the MSDN library at http://msdn2.microsoft.com/library/aa443482.aspx.

I have a really neat treeview implementation in one of my applications. There's one user request I haven't been able to satisfy, though: sort by price. I have a column for price and one for description (plus several others.) I have it set up so that when you click a column header it sorts by that column. But under the covers the sort is always alphabetic, as the control treats all the values as text. So $11.00 sorts ahead of $2.00.

I wasn't looking for a solution for that this afternoon when I stumbled across this article: http://vbnet.mvps.org/index.html?code/callback/lvsortcallback.htm . The article describes using a callback function to tell the system how to sort your items. It provides a number example and a date example, but you could extend this further. You could even have names in the Listview first name first, but use the callback to order items last name first. All you have to provide is a function that, given two values, tells the system which comes first.

The article is written for VB, not VBA, but I found adapting it for Access really easy. Here's what I had to change for Access 2007:

• Change:
  Private Sub ListView1_ColumnClick(ByVal ColumnHeader As ComctlLib.ColumnHeader)
  to
  Private Sub ListView1_ColumnClick(ByVal ColumnHeader As Object)
• Comment out Picture1.Cls (twice)
• Change all occurrences of Picture1.print to debug.print
• Change
  Unload Me
  to
  docmd.close

The article talks about how when you sort this way the items aren't really sorted in the underlying control, they are just displayed sorted. I guess if you read through the items using for each ... next for get the wrong sequence. I did a quick test and found I could still detect the item that had been double clicked with code like
Private Sub ListView1_DblClick()
MsgBox Me.ListView1.SelectedItem
End Sub
so I don't think the impact will be large. The article includes code to read the items using an API that returns them in the apparent order, if that's what you need to do.