[RESOLVED] Load listview sorted

[aikidokid]

VB6 - Access 2002 - ADO

I have this SQL statement, and I know that I need to use 'ORDER BY' to load into a certain order, but I can't seem to get it to work the way I am after.

strSQL = "SELECT First_Name, Middle_Name, Surname, Memb_ID FROM tbl_Membership"

I am running this in the form load and then, the bit I am stuck on is trying to have the Listview sorted by Column 3 (Surname)

How can I sort this by this column?

Thanks

[RhinoBull]

Use the Order By clause:

strSQL = "SELECT First_Name, Middle_Name, Surname, Memb_ID FROM tbl_Membership Order By Surname"

Specify ASC or DESC after the column's name if you have to.

[aikidokid]

Thanks RhinoBull.

I have this now. Actually I had this before, but the Listview Sorted property was set to true, so it was sorting it by the first column.

That said, now I have set the Listview Sorted property set to False, and the listview is loaded how I would like, I can no longer let the user click on any of the column headers to change the sorting of the listview.

How do I overcome this without changing the way the listview is sorted at form load time?

[amrita]

Use the below function and call for Column 3 on form load

Public Sub LV_ColumnSort(ListViewControl As ListView, _
  Column As ColumnHeader)
 With ListViewControl
  If .SortKey <> Column.Index - 1 Then
   .SortKey = Column.Index - 1
   .SortOrder = lvwAscending
  Else
   If .SortOrder = lvwAscending Then
    .SortOrder = lvwDescending
   Else
    .SortOrder = lvwAscending
   End If
  End If
  .Sorted = -1
 End With
End Sub

[Hack]

Take your query out of the form load and put it into a Sub.

Call the sub from the form load so that it has a base listing.

Structure your sub in such a way as it can have multiple possibilities for sorting by adding a SortBy parameter which you will pass to it each time it is called.

Now, on your listview, use the ColumnClick event so the users can decide how they want it sorted. In orders, if they click on the First Name column header you would reexecute your sub passing it the FirstName as a SortBy Parameter.

Here is an example:

Option Explicit

'create a varible to store the sortby string
Private strSortBy As String

Private Sub Form_Load()
'establish a default
strSortBy = "LName"
LoadNames strSortBy
End Sub

Private Sub LoadNames(pstrSortBy As String)
Dim strSQL As String

strSQL = "SELECT First_Name, Middle_Name, Surname, Memb_ID FROM tbl_Membership "
     Select Case strSortBy
         Case "LName"
         strSQL = strSQL & "ORDER BY surname "
         Case "FName"
         strSQL = strSQL & "ORDER BY first_name "
         Case "MName"
         strSQL = strSQL & "ORDER BY middle_name "
     End Select
Set rs = New ADODB.Recordset
rs.Open strSQL, cn
ListView1.ListItems.Clear
'put code to load listview with recordset here
rs.Close
Set rs = Nothing
End Sub

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)

Select Case ColumnHeader
   Case "First Name"
      strOrderBy = "FName"      
   Case "Middle Name"
      strOrderBy = "MName"      
   Case "Last Name"
      strOrderBy = "LName"     
End Select
LoadNames strSortBy
End Sub

Now, in order to sort it, all they have to do is click on the column header that represents how they want it sorted.

[aikidokid]

Thanks again Hack

I will look into this and have a go. I remember reading a thread a while back about using a select case for the SQL statement, but had forgotten about it.

Just out of interest, was this something out of your code library, or did you just write it now?

[Hack]

I've been doing "ad hoc" SQL query building based on passed parameters for years.

All of my reports are configurable by the end user. I have report criteria screen with checkboxs, option buttons, listboxs (that I used to be an IN clause) and sundry other controls. Once the user has selected what they want from the criteria screen and they click OK, I pass all of the selected options back to my report sub which in turn builds the SQL query. My querys are typically "infested" with If/Then and Select Case statements that add specific IN Clauses, AND statements, GROUP BY and ORDER BY clauses.

The column click thing is something that came up as a requirement in my current project (although I've done similar things with listviews before).

[aikidokid]

A big thanks Hack for this help.

I have got it working as you described, so thats good.
But also, the concept of using the If/Select to build the SQL statement is something I will try to remember.
I can see how it would enable me to make the program much more user friendly as well as giving it a hell of a lot more functionality.

P.S. I noticed your deliberate mistake with the variables for sorting the columns

[Hack]

P.S. I noticed your deliberate mistake with the variables for sorting the columns

I was wondering if you would notice that and correct it, or whether you would post back saying there was problem. Fine job.

(PS: I do that sometimes just to see if who I'm dealing with is paying attention. )

[aikidokid]

I do that sometimes just to see if who I'm dealing with is paying attention.

Most of the time I am paying attention, but sometimes I think I must be coding in my sleep!

[aikidokid]

I have got all of this working (have had for a while now ) but what I would now like to do now, is when the user clicks on the columnheader, it sorts ascending, and if they then click on the same columnheader, sort it decending.

[amrita]

Try this function.

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
    
    'Check if the Sortkey is the same a the current one
    If ListView1.SortKey <> ColumnHeader.Index - 1 Then
	ListView1.SortKey = ColumnHeader.Index - 1
        ListView1.SortOrder = lvwAscending
    Else
        'If the column is already selected then change the
        'sortorder to be the opposite of what is currently used
        
        ListView1.SortOrder = IIf(ListView1.SortOrder = lvwAscending, _
                                lvwDescending, lvwAscending)
    End If
    
    ListView1.Sorted = True
End Sub

[aikidokid]

Thanks amrita, this works exactly as I needed