Sorting records in a Subform

[Guest]

hi to all

I have been making use of a great Function (sorry, I don't remember the
author) to sort the records in a Subform using a cmd Button on the Main Form.
I use 4 of these buttons placed above columns I want to sort in the Subform.
The button's On Click event in the property window is set to:

=SortForm(Forms.MasterFormName.SubFormName.Form,"PutRecordSourceHere")

The function is:

Function SortForm(frm As Form, ByVal strOrderBy As String) As Boolean
'Purpose: Set a form's OrderBy to the string. Reverse if already set.
'Return: True if success.
'Usage: Command button above a column in a continuous form:
' Call SortForm(Me, "MyField")

If Len(strOrderBy) > 0 Then
' Reverse the order if already sorted this way.
If frm.OrderByOn And (frm.OrderBy = strOrderBy) Then
strOrderBy = strOrderBy & " DESC"
End If
frm.OrderBy = strOrderBy
frm.OrderByOn = True
SortForm = True
End If
End Function


Here is my question. Can this function be altered so that more than one
sort criteria could be used? Perhaps the results of one sort could be
'remembered' when a second sort is clicked. It would be great, for example,
to click LastName, then City to alphabetize sales people by city.

Any clues? This type of programming is still beyond my reach.

 

[Douglas J. Steele]

Yes, the OrderBy property can take multiple fields: separate them by commas.

However, you'll have to make changes to the code if you still want it to
reverse the order. If you need that capability, try the following untested
air-code:

Function SortForm(frm As Form, ByVal strOrderBy As String) As Boolean
'Purpose: Set a form's OrderBy to the string. Reverse if already set.
'Return: True if success.
'Usage: Command button above a column in a continuous form:
' Call SortForm(Me, "MyField")

Dim intLoop As Integer
Dim strOrder As String
Dim varFields As Variant


If Len(strOrderBy) > 0 Then
' Reverse the order if already sorted this way.
If frm.OrderByOn And (frm.OrderBy = strOrderBy) Then
varFields = Split(strOrderBy, ",")
For intLoop = LBound(varFields) To UBound(varFields)
strOrder = strOrder & varFields(intLoop) & " DESC,"
Next intLoop
strOrderBy = Left(strOrder, Len(strOrder) - 1)
End If
frm.OrderBy = strOrderBy
frm.OrderByOn = True
SortForm = True
End If
End Function

 

[Guest]

Douglas - thanks for your prompt answer. As a newcomer to VBA, it took me a
while to sort through the code, but I finally see how it works. May I be
permitted 2 follow-up questions?

a) Is the normal way to keep track of the cumulative OrderBy string (eg
CityName, CustomerName) to have a textbox store its value on the form? I did
this using txtOrderByCumulative to store strOrderByCumulative, and it seems
to work fine.

b) But... this means that the code you supplied needs to work with
strOrderByCumulative on my form. I was hoping to use this code from several
different forms, so I'm not sure how to work the reference.

Thanks for any suggestions.

 

[Douglas J. Steele]

Afraid I don't know what the "normal way" is. I've never had a need to use
OrderBy in any application I've built: I've always sorted the query that
comprises the form's RecordSource.

I don't understand your second point. You call the routine by passing a
reference to the form and passing a string. There's certainly nothing in the
code that relies on there being a control or variable of any specific name
on the form.