Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

Dynamically sorting forms and reports

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:

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:

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.

Publicado en Access Help, VBA

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores