Authored by Ben Clothier

A Question of Order
A common request that crops up is the ability to select a certain sorting order at runtime for forms and reports. When we’re talking about filtering, it’s usually easy to pass along a Where condition whenever we open the forms and/or reports. But there’s no OrderCondition. The first impulse would be to reach for that OrderByOn property that’s available on Forms and Reports.

However, in our experience, we’ve found that both FilterOn and OrderByOn properties can be tempermental. For me there are two major issues that also are effectively show-stopping:

1) Changing those properties at runtime is considered a design-time change and users may be prompted to save the changes. If the poor user click Yes and saves the changes, it’ll be there next time it’s opened. This can be a bad thing.

2) It doesn’t always work. Allen Browne has documented some cases such as here and here.

Workarounds do exist but may throw you for a loop, since you may end up using different methods in different projects, creating a nightmare to maintain all around.

Further, we have the additional burden of ensuring that our users won’t be prompted to save the design changes, and thus change form/report and possibly breaking it in the process. Since we’re not certain when users will save changes when prompted, it’s better to avoid the scenario all together.

So, what’s the preferred technique then?

By far, building SQL strings dynamically and assigning them to the form or report’s recordsource is the most reliable and consistent technique. Here are three steps you can use to setup dynamic sorts:

1) Set up the initial SQL statement to not pull data

When we design a form and/or report, we always use a query rather than a table and use a record source similar to:
SELECT a, b, c FROM tblMyData WHERE 1 = 0;
The beauty of this approach is that at design time we get all fields we need to set up our forms & reports but without actually pulling any data. This is also true when we consider that when we change SQL statement in form’s/report’s Open event, the form/report already has submitted the request even though the form isn’t displayed on the screen just yet. It’d be waste of resources if we’re going to change the SQL, so hence the impossible criteria. We could just leave the source blank, but that means no field list & invalid ControlSource errors on all bound fields when we design the form & reports.

2) Set up the Open event

Modify the form or report’s open event to dynamically build the record source property:
Private Sub Form_Open(Cancel As Integer)
Const InitSQL As String = _
"SELECT e.EmployeeID, e.FirstName, e.LastName " & _
"FROM tblEmployees AS e "

If Len(Me.OpenArgs & vbNullString) Then
Me.RecordSource = InitSQL & Me.OpenArgs
End If
End Sub

You can use the OpenArgs clause of the DoCmd.OpenForm or DoCmd.OpenReport to pass along the OrderBy clause or use a  TempVar.

There are two major advantages gained over the WhereCondition clause here – we can specify a custom sort order and we can join additional tables by manipulating the record source string accordingly.

3) Set up the Close Event

This is not strictly necessary since changes made to Recordsource property are discarded, but we typically like to clean up after ourselves and reset the Recordsource back to the impossible criteria.

The technique of manipulating the SQL string at runtime has served us well, making our code more consistent and therefore easier to maintain. We hope you find it just as useful as we do.