Filter on Empty Master

One of the pitfalls of converting Access 2002 to Access 2010 is that there are new properties on forms and controls that do not exist in the old version of Access. One of these is the "Filter on Empty Master" property of a subform control. In an ideal world the main form is bound to master records such as Orders and the subform and its source object are bound to child records such as Order Details clearly related to the master records through a field called Order ID. Thus Filter on Empty Master would naturally be set to Yes and this how the conversion process configures the subform when it is converted to Access 2010.

However, a common way of using Access forms and subforms is, similar to a web page, designing a strip of navigation buttons down the left hand side of the screen with a subform window in the centre of the screen to display multiple forms selected by the navigation buttons. The navigation strip will likely be bound to the user identification table to make sure that the user see those buttons he is authorised to see. In the subform window will normally be forms to display completely unrelated child records for example a client list as a result of a search and the client details records.

In this situation the Filter on Empty Master property behaves in a completely counter intuitive way expecting the Master records to be related to the Child records and giving a blank screen except for any titles on the form. Changing the value from Yes to No produces similarly unexpected behaviour. In the case I saw the work around was to make sure that the master form was always empty as it would be if the source query used the Access function CurrentUser to select the user record and identify the user then switch the Filter on Empty Master property to No.

Tags - Access 2010, Access 2007, Access 2002, Access XP, Access 2000, Access 97, conversion, subform, Filter on Empty Master, Source Object, Link Master Fields, Link Child Fields, Microsoft, property, CurrentUser, function, fails, error ...