Sorting on Headers in Subform

[Cheryl]

My subform is based on a query like this:

SELECT DISTINCTROW TblCharges.Abbreviation, TblCharges.Description,
TblCharges.Statute, TblCharges.Type, TblCharges.Class FROM TblCharges
ORDER BY TblCharges.Description;

Is there a way to sort it when you click on the header in the form view?
I'd like to be able to click on the Abbreviations, Description and
Statute headers and have it ascend sort. Currently it only sorts by
description.

Thank you.

 

[Allen Browne]

You can set the OrderBy property of the form in the click event of the label
(since these labels are not attached):

Private Sub lblAbbreviation_Click()
Me.OrderBy = "Abbreviation"
Me.OrderByOn = True
End Sub

 

[Cheryl]

That works nicely in form mode, but will it work if my subform is a
multi-select list box? I just can't see a way to do it yet.

Cheryl

 

[Allen Browne]

A listbox does not have these properties.

It would be possible to use its MouseDown event to calculate if the click
occurred in the header row of the list box, and if so in which column (based
on the Column Widths property), and then reassign its RowSource.

Note that measurements are in twips where 1440 twips = 1 inch.