Disabling FilterOnLoad and OrderOnLoad in mixed MS Access application deployments

Me, I don't curse the person who came up with FilterOnLoad and OrderOnLoad. But if I met the people who decided to default these properties to TRUE in a dark alley, their life prospects would be pretty grim. By defaulting to TRUE, these properties changed the way that forms had behaved in all previous versions on Access.

Before their invention, when you filtered or ordered a form, when you closed and reopened it, those filtering and ordering options were not immediately applied again. So users became used to the safety net: 'If I really hose this up by filtering and ordering it, all I have to do is close and reopen it to make it pristine again.' The default of TRUE removed that safety net.

Now, if all you run is Ribbon versions of Access (2007 and later,) fixing this annoyance is just a bit of scutwork. You open every form in design view, open the Properties window, navigate to the Data tab and set FilterOnLoad to false and OrderOnLoad to false. One-and-done, no big hairy deal... except if you are still using Access 2003 and earlier to develop, maintain or run a distributed front-end application that will also be run by Access 2007+ clients. Then you have a very big problem.

Access 2003 and earlier do not have these lovely properties, so you cannot change them in those versions. Opening objects in later versions for editing and then use in earlier versions has two downsides. First, all the VBA references silently update to the later version numbers, and do not downgrade when opened in the previous version. That necessitates the developer going into the code project and fixing the broken references. This is an annoyance, but not a deal-breaker.

The second IS a deal-breaker. Objects that have been edited in Ribbon versions of Access have a nasty habit of becoming corrupt when subsequently edited by non-Ribbon versions. And nobody wants to corrupt their application.

One solution is to just eliminate non-Ribbon versions of Access. But:

•  The help system on Access 2003 remains superior to anything shipped since
• The Database Window remains superior to the Nav Pane (or Pain) for large applications
• The ability to right-click an object and bring up the Properties window on a loaded form is an invaluable timesaver.
• And none of the things added to the Access filetype (accdb) since 2003 (like multi-value fields, attachment fields, SharePoint integration) are useful in a large project that will have a backend in SQL Server.

So you may not want to kick Access 2003 to the curb.

Now, VBA is our friend and can do many things, but you cannot issue VBA like this

If Access.Application.Version > 11 Then
                          Me.FilterOnLoad = False
                      End If

Select all Open in new window

because the non-Ribbon versions of Access will balk at Me.FilterOnLoad as an unknown property and fail to compile. Conditional compilation is of no help either

Dim verPlus As Boolean
                      verPlus = Application.Version > 11
                      #Const ver12Plus = verPlus
                      #If ver12Plus = True Then
                          Me.FilterOnLoad = False
                          'MsgBox "This is later than Access 2003"
                      #Else
                          'MsgBox "Access 2003"
                      #End If

Select all Open in new window

because if this is compiled on Access 2003, the Me.FilterOnLoad = False will be left out, and if compiled on Access 2007+ will be left in the compilation, breaking the downlevel clients. So you then either have to compile on each version and distribute them separately, or compile on each end users machine. Not ideal!

Now, when the Ribbon first deployed, there were methods to get a custom Ribbon to display on the Ribbon versions of Access in a mixed deployment.  You could create the following property

CurrentDb.Properties("CustomRibbonID")

Select all Open in new window

with appropriate datatype and set it to the value needed by the Ribbon versions.

The success of this tactic depended upon the nature of the Properties collection.

A Property does not exist by default. MS creates some. The developer can create others in VBA. Actions in the development GUI create more still.

If the Property is one MS has documented, it must be created with a specific name and datatype and range of values. If it is one that the developer creates, then it can be anything the developer would like that does not clash with what Access has reserved within it for use by MS.

The trick is that Access 2003- thinks CustomRibbonID is a developer-created property that it can ignore, while Access 2007+ sees it as an MS documented property, and acts on it.

Nice enough, but where's the payoff?

Well, it turns out that it is not just the CurrentDb.Properties collection that behaves this way, but the Properties collection for Forms does too.  So THIS CODE

If Access.Application.Version > 11 Then
                          Me.Properties("FilterOnLoad") = False
                          Me.Properties("OrderOnLoad") = False
                      End If

Select all Open in new window

will compile on all versions of Access and work on all versions of Access.

So, in a mixed Access environment, you can put that code sliver into a suitable form event (ie Open, Load, Close) and be done with the stupid default of TRUE. And then Access will behave the way that the end users have gotten used to.

Now, if I had only figured this out a decade ago!