Allowing the User to sort a List Box as required

Allowing the User to sort a List Box as required

If you wish to allow the user to have the ability to sort a List Box as required, then this can be achieved by placing Toggle Buttons above and below each Column in the List Box, and then utilising the Toggle Button's OnClick event together with the Tag property.

For example, if you have a List Box whose RowSource is (using the Northwind sample database):

SELECT Products.ProductID, Products.ProductName, Categories.CategoryName, Suppliers.CompanyName, Products.UnitPrice
FROM Suppliers
INNER JOIN (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) ON Suppliers.SupplierID = Products.SupplierID;

And the first Column, ProductID, is hidden from the user with a ColumnWidth of 0, whilst the other ColumnWidths are 4.5cm;3cm;5.5cm;1.95cm. Add four Toggle Buttons to the Form above the List Box, and four below, the same width as the columns in the List Box, and aligned with them. If you want to, place a picture on them to indicate the direction that the column will be sorted. With each Toggle Button, change the name to something more useful, such as 'tglProductNameASC', and set the Tag property of the control to '[ProductName] ASC' (without the quotes). Once you have completed this, you need to write a Sub procedure that will loop through the controls on the Form, and see if this control is the one that has just been pressed, and also use the OnClick event for each of the Toggle Buttons to call this procedure:

Option Compare Database
Option Explicit

Sub sSortToggle(strSortBy As String)
    Dim ctl As Control
    Dim strSQL As String
    strSQL = "SELECT Products.ProductID, Products.ProductName, Categories.CategoryName, Suppliers.CompanyName, Products.UnitPrice" _
     & " FROM Suppliers INNER JOIN (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) ON Suppliers.SupplierID = Products.SupplierID ORDER BY "
    For Each ctl In Me.Controls
        If ctl.Name = strSortBy Then
            Me!lstResult.RowSource = strSQL & ctl.Tag
        Else
            ctl.Value = False
        End If
    Next ctl
End Sub

Private Sub tglCategoryNameASC_Click()
    Call sSortToggle("tglCategoryNameASC")
End Sub

Private Sub tglCategoryNameDESC_Click()
    Call sSortToggle("tglCategoryNameDESC")
End Sub

Private Sub tglCompanyNameASC_Click()
    Call sSortToggle("tglCompanyNameASC")
End Sub

Private Sub tglCompanyNameDESC_Click()
    Call sSortToggle("tglCompanyNameDESC")
End Sub

Private Sub tglProductNameASC_Click()
    Call sSortToggle("tglProductNameASC")
End Sub

Private Sub tglProductNameDESC_Click()
    Call sSortToggle("tglProductNameDESC")
End Sub

Private Sub tglUnitPriceASC_Click()
    Call sSortToggle("tglUnitPriceASC")
End Sub

Private Sub tglUnitPriceDESC_Click()
    Call sSortToggle("tglUnitPriceDESC")
End Sub

You can download an Access 97 database with this sample in from here.

Top

Last modified at 06/06/2006 14:55:19