Access 2003
Database FE & BE

Problem: Providing a multi sort capability for unsophisticated users
driven by up to three fields. Only ascending sorts will be provided
and the field sort order will be controlled via code. Access’
intrinsic filter by form will not be used.

The data to be sorted displays as a child form. This form has 3
unbound option buttons that are used to select the desired fields on
which to base the sort. None or any combination of the 3 selections
are possible and the order in which the selections are made matter.
We will call chkDos the sort select for dt_service, chkDescthe sort
select for svc_new, and chkSvcCd the select for svc_new_num. There is
a “Sort” button that is clicked after sorts are selected. Currently,
the code I have sorts the fields in the following order (if
selected): dt_service, svc_new, svc_new_num. An additional
requirement has been imposed stating that the order that the option
buttons are selected matters. For example, clicking the sort option
controls in this order svc_new_num, dt_service, & svc_new, would make
the sort order the same. The OrderBy = “svc_new_num, dt_service,
svc_new”. Selecting only svc_new_num and svc_new would create an
OrderBy= “svc_new_num, svc_new”.

The existing code:

Sub sFilterForm()
'================================================= =======================
' Procedure : sFilterForm
' Purpose : Logic that drives sorting of form.
' Author : Bob
' Phone :
' Email:
' DateTime : 2/5/2010 14:00
' Notes : Sort can be on any combination (and/or) of the following
fields:
' dt_service, svc_new, and svc_new_num. Called by Sub btnSort_Click.
'------------------------------------------------------------------------
'Revision History
'================================================= =======================
Dim bDos As Boolean
Dim bDesc As Boolean
Dim bSvc As Boolean
Dim strSort As String

On Error GoTo sFilterForm_Error
bDos = False
bDesc = False
bSvc = False

With Me
If .chkDos = -1 Then
bDos = True
End If

If .chkDesc = -1 Then
bDesc = True
End If

If .chkSvcCd = -1 Then
bSvc = True
End If

If (bDos = False) And (bDesc = False) And (bSvc = False) Then
.OrderByOn = False
.lblbtnSort.Caption = "Sort"
MsgBox "No sort criteria selected.", vbInformation
Exit Sub
End If

If (bDos = False) And (bDesc = False) And (bSvc = False) Then
strSort = "~"
ElseIf (bDos = False) And (bDesc = False) And (bSvc = True)
Then
strSort = "svc_new_num"
ElseIf (bDos = False) And (bDesc = True) And (bSvc = False)
Then
strSort = "svc_new"
ElseIf (bDos = False) And (bDesc = True) And (bSvc = True)
Then
strSort = "svc_new, svc_new_num"
ElseIf (bDos = True) And (bDesc = False) And (bSvc = False)
Then
strSort = "dt_service"
ElseIf (bDos = True) And (bDesc = False) And (bSvc = True)
Then
strSort = "dt_service, svc_new_num"
ElseIf (bDos = True) And (bDesc = True) And (bSvc = False)
Then
strSort = "dt_service, svc_new"
ElseIf (bDos = True) And (bDesc = True) And (bSvc = True) Then
strSort = "dt_service, svc_new, svc_new_num"
Else
strSort = "~"
End If

If strSort = "~" Then
.OrderByOn = False
.lblbtnSort.Caption = "Sort"
Else
.OrderBy = strSort
.OrderByOn = True
.lblbtnSort.Caption = "Sort= " & strSort
End If
End With

ExitHere:
Exit Sub

sFilterForm_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") " _
& "in procedure sFilterForm of VBA Document Form_sfrmClaimGen"

End Sub


The existing form has limited real-estate. I was considering hiding a
text box control to store field sort (and resetting/ordering as
required) information or a popup dialog box, but I’m unsure how to
implement either. I would appreciate advice on how to proceed with a
solution based on the requirements and user limitations.

Regards,
Bob

On Feb 9, 5:19*pm, Bob <bob.o... (AT) gmail (DOT) com> wrote:
Hi Bob,

I had a similar problem, and I solved that as follows.

Sorting is only relevant in continuous forms, and for simplicity I
will talk about rows and columns.
When a user clicks on the columns header, a procedure starts that gets
the forms' RecordSource and splits off the Order-part. A new Order-
part is composed by concatenating the Fieldname of the clicked column
and the original Order-part. With this new Order-part the RecordSource
of the form is refreshed. This triggers the OnActivate event, and the
form is sorted on the selected column.
During the string manipulation for a new Order-part, you can arrange
that a certain field does not occur twice in the new Order-part.
If EACH column header in the form acts in this way, you can sort on
ANY field in the form.

In fact what I described is what happens when I click the right
mousebutton. with immediate ascending sorting.
When I click the left mousebutton a Column_form is opened, with the
name of the original form passed through OpenArgs, to make a Form-
variable of the original form.
In the Column_form I have much more sort possibilities. I can select
ASC or DESC, I can select to go to the beginning of the sorted set, to
the end, or even to the original record. But I can also choose to sort
on other criteria, e.g. length of the string, sorting on Month-Day of
a Date (birthday calender), sorting after a certain character,
whatever you want.
By the way, the Column_form has also other possiblities than sorting,
such as summing, changing values, statistics, occurences, etc, that
all work on the recordset of the original form.

Because I use generalized forms, all these possibilities are
automatically build in in every continuous form.

HBInc.

Thank you for the reply. I have implemented code that adds a field to
the OrderBy string if the column head text lable is clicked. I used
InStr to determine if the field already exist. I am using a text box
to store the string. If the text box is double clicked, the forms
default sort order is restored - I could use a buttton, but am unsure
which might be the least painful to reset the sort order.

On click event examples of two of my labels:
Private Sub lblSvcCd_Click()
sSorter "svc_new_cd"
End Sub

Private Sub lblSvcNew_Click()
sSorter "svc_new_num"
End Sub


The sub called by the above:
Sub sSorter(strFldName)
Dim strSort As String
With Me
If InStr(.txtSort, strFldName & ",") = 1 Then
MsgBox "Field already in sort"
Exit Sub
End If
strSort = .txtSort & strFldName & ", "
.txtSort = strSort

.OrderBy = Mid(.txtSort, 1, Len(.txtSort) - 1)
.OrderByOn = True
End With
End Sub



Regards,
Bob