[X]My Assistant

[CyberCow]

Here's an interesting method of sorting columns in a continuous form. In this case, a subform is used, but the same can be applied to a main form. All the code is behind the form, so there is no need for a regular module.
On the attached demo, all the code is in the "sfrmVST" form (a subform).
The first thing to do is to set up each column's title label with a consistant naming convention; "lblxxxxx". Then, each label has a "Tag" property value that contains the name of the 'field' it represents.
There is also another label that is set far to the left of the form. This particular label is special in a couple of ways. First, it's font is set to Marlett and it's visible property is set to "No". The caption of this label is the number "5". It is also important that the label be named: "lblSortIndicator". The "OnClick" event of this label should contain the following code:
Me.lblSortIndicator.Visible = False
Me.OrderBy = ""
This will turn off the sort all together and make the indicator invisible again.
Now, to actuate the sorting, you will need to set the "OnClick" property of each column label to execute a small line of code that reads SortForm ("lblxxxxx") (where "lblxxxxx" is the name of the label itself).
The SortForm call code looks like this:

CODE

Public Sub SortForm(strSortControl As String)
Dim ctl As Control
Set ctl = Me.Controls(strSortControl)
    If Me.OrderBy = ctl.Tag And Me.lblSortIndicator.Caption = 5 Then
        strOrderBy = ctl.Tag & " DESC"
        Me.lblSortIndicator.Caption = "6"
      Else
        strOrderBy = ctl.Tag
        Me.lblSortIndicator.Caption = "5"
    End If
    Me.OrderBy = strOrderBy
    Me.OrderByOn = True
    Me.lblSortIndicator.Left = ctl.Left + ctl.Width
    Me.lblSortIndicator.Visible = True
    Set ctl = Nothing
End Sub

You can see by the code that if you click the label again, the sort order will reverse.
There are also two private form declarations that are required. These are:
Private strOrderBy As String
Private blnShowIndicator As Boolean
So, once all the elements are set up, you can simply 'single-click' a column's label and voila! Ascending sorting for that columns occurs AND the little label that uses the Marlett font will appear to the right of the label indicating the sort order with an up or down arrow.
Because the demo is a personal db of mine, there are a few other procedures that are used to do other functions. They should be self-evident in their nature.
I use this type of sorting because I like to build all of my db's with no menu/toolbars and yet give the user some ability to sort without creating a custom menu. Those of you with fair VBA skills should have no problem getting this to work for your db's
Please PM me if you have any bugs to report.
Enjoy!
 CuBasePlugins.zip ( 504.57K )Number of downloads: 245
(contains versions A97/A2K/A2K3/A2K7 - original number of downloads: 656 - updated 3-5-2013 to include newer versons of Access)

[riccoboni]

Thank a lot for the sharing ! I used your code insted of the (Sort button + field list) I used until now .
Bruno