Control to use for sorting by column in continuous form

[Brian]

Does anybody have a suggestion for how, on a continuous form, to emulate the
type of column-based sorting such as exists, for example, in Outlook? I have
column labels in the form header and columnized data in the detail below the
headers. I want to allow the user to click on the header and get some sort of
indication of how the form is sorted.

I have no problem with the VBA to rewrite the form's record source & requery
with the ORDER BY clause(s), but I don't know what kind of control to embed
in the header such that it is a small Up Arrow when sorting ASC and Down
Arrow when sorting DESC and changes when the user clicks it. I could replace
the column header with a button, but I do not know how to combinine the text
(e.g. Customer)& graphic (up/down arrow) on the button. Even if I could, the
stock up & down button graphics are so large that they look out of place with
an eight-point font in the header.

 

[John Spencer]

Here is a bit of code that I use. I put the sub into a vba module and
then call it from the click event of the label.
sReorder Me.LabelControlName

using Me.LabelControlName lets this work in subforms and forms.


Public Sub sReorder(cntlLabel As Control)
'Reorder the displayed records on a form or subform when a label/control
'is clicked
'Uses the tag property of the label/control to decide which field is
'involved
'and to maintain the caption value of the label/control
'Tag should contain three items separated by semi-colons.
'Standard Label Tag would be
' Caption;Phrase"OrderBy";FieldName to Order by
'Example Tag: User Id;OrderBy;UserIdentifier


Dim cntlAny As Control
Dim arVar As Variant
Dim objParent As Variant 'form or subform.form
Dim strUP As String
Dim strDown As String

'With unicode fonts
strUP = ChrW(9650) 'Works with Arial to show solid UP triangle
strDown = ChrW(9660) 'Works with Arial to show solid Down triangle

'With non-unicode font uncomment the following lines
'strUP = "^"
'strDown = "/"

On Error GoTo sReorder_Error

Set objParent = cntlLabel.Parent

'If no records then don't bother to set sort
If objParent.RecordsetClone.RecordCount > 0 Then
arVar = Split(cntlLabel.Tag, ";", -1, 0) 'Get tag components

'Reset all relevant labels to default appearance
DoCmd.Echo False
For Each cntlAny In objParent.Controls
With cntlAny
If .Tag Like "*OrderBy*" Then
.Caption = Left(.Tag, InStr(1, .Tag, ";") - 1)
End If
End With
Next cntlAny

If objParent.OrderByOn = False Then
'Records were not sorted
objParent.OrderBy = arVar(2)
cntlLabel.Caption = arVar(0) & strUP

ElseIf objParent.OrderBy = arVar(2) Then
'Records were sorted by this field in Ascending order
objParent.OrderBy = arVar(2) & " DESC"
cntlLabel.Caption = arVar(0) & strDown

ElseIf objParent.OrderBy = arVar(2) & " DESC" Then
'Records were sorted by this field in Descending order
objParent.OrderBy = arVar(2)
cntlLabel.Caption = arVar(0) & strUP

Else
'Records were sorted by some other field
objParent.OrderBy = arVar(2)
cntlLabel.Caption = arVar(0) & strUP
End If

objParent.OrderByOn = True
DoCmd.RunCommand acCmdSelectRecord
DoCmd.Echo True
End If 'no records

EXIT_sReorder:
Exit Sub

sReorder_Error:
DoCmd.Echo True
MsgBox Err.Number & ": " & Err.Description, , objParent.Name & ":
sReorder"

End Sub

'Keywords: Sort Fields, Sort records, sort columns

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Brian]

Bingo!

The ChrW(9650) did the trick. After my post, I had gotten as far as trying
to insert Chr(30) & Chr(31); however, those both display as squares/unknowns
in labels. I was not aware of the more complete characters set available with
the ChrW function and so was beating my head against the wall trying to
insert buttons, etc.

Thank you so much.