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.
Hello Ben,
Your technique for modifying the recordsource dynamically works great for forms. But in the second article from Allen Browne you are citing above, it says that for reports an Order By clause in the recordsource is ignored. This article is recommending to change the report grouping levels programatically, or to adjust the OrderBy property. But you point out that design changes can be risky since they can be saved. What is the safest way you would recommend for sorting reports dynamically?
Thank you,
Robert
Robert, excellent question!
I read the article again and the way I remember this was this was to address specifically the problem with using Filter/OrderBy property and the article really wasn’t considering grouping at that time. I know that sometime I’ve opted to explicitly avoid grouping and instead use something like left outer join query where the “1st group” would have rows repeated for each record in “2nd group” and so on. That lets us flatten the report and thus have more control over how it may be sorted and can get it sorted in SQL, too.
Other times, we do need grouping, typically because of some subtotaling or needing headers/footers on each group. In this case, we do what Allen recommends. This is not as problematic as using Filter/OrderBy because we’ve not observed a prompt to save design changes as a result of changing the group level. Note that is also true when you use, for example, WhereCondition of the OpenReport so though it does put the criteria into the Filter property, there is no prompting to save the design changes, which is mainly what we wanted to avoid.
I hope that helps, Robert.
Hi Ben,
“the form/report already has submitted the request” Do you mean the form/report already has a recordsource?
I suppose
If Len(Me.OpenArgs & vbNullString) Then
is True if Me.OpenArgs is greater than 0 but what’s the purpose of & vbNullString?
Grovelli –
Thanks for the great questions.
1) Yes, I was referring to recordsource – in this context, the Access object has to submit the request for data to the database engine and that has already occurred by time the Open event fires. One way you can see this for yourself is to test:
?Me.Recordset.BOF
Assuming there are actually data returned, the return would be “False”, indicating that the form’s Recordset is already open and has data. For a small table, that may be no big deal but when we’re binding a form or report to a large table or expensive query/view, that may not be desirable especially if you’re going to be changing the recordsource at the runtime.
2) This allows us to protect against “Invalid Use of Null” errors we may get if we get a Null value. As you may know, concatenating using & operator is null-safe, and “vbNullString” is a built-in constant for a zero-length string. This allows us to convert any Null values into a valid string without actually modifying the string as thus:
Null & “” = “” (Len = 0)
“” & “” = “” (Len = 0)
“Hello, world” & “” = “Hello, world” (Len=12)
The decision of using constant vbNullString or the literal “” is probably a matter of preference, however. I like to refer to built-in constants instead of using literals in my code.
I hope that addresses your questions. We always appreciate feedback.
-Ben
Hello,
… I thought would jump in — first post here on AccessExperts.net! 🙂 …
Len() will return a Null if a Null is passed to it (ie: no error is raised). A Null value returned by the expression used to evaluate truth in an If..Then..Else code block will branch an If..Then..Else code block to the “Else” branch. If an “Else” branch does not exist, the code jumps to the End If line.
It may also be appropriate to note that the expression used to evaluate truth in an If..Then..Else code block is compared to 0 …
So …
If Len(“someValue”) Then
MsgBox “Expression was not equal to 0”
End If
Len(“someValue”) returns a value of 9 which is not equal to 0, therefore the “truth” block of the If..Then is executed and the message box pops up. Please take note that 0 is not the same as equal to True.
Also to illustrate how a Null result in the expression will branch to the Else block, please take a look at the execution path of the following code:
If Len(Null) Then
MsgBox “You won’t see this message”
Else If Len(Null) = Null Then
MsgBox “You won’t see this message either, but Null = Null results in Null”
Else
MsgBox “You will see this message”
End If
Now, with all that said, I like to do as Ben has shown and force Len() to return a value other than Null — It just feels better to me :). Although, I do use some literals in my code … “”, 1, and 0.
—-
I hope this makes sense and adds to great information already provided by Ben.
Brent
Thanks for the excellent addition, Brent.
I think it does illustrate that Nulls can be tricky since they may be treated differently in different, like how it could appear to be “false” when tested in a If/Then construct but if that’s what we walked away with, we’d be in hot water in other cases. For that reason, I tend to think it’s a good practice to always write code that will handle all possible cases not just gracefully but also consistent so we don’t get unexpected surprises.
Ben