2007.11.15 06:45 PM

Access Windows Collection

One thing Access has always lacked is a programmatically accessible collection of open windows. I've always thought this a strange omission by Microsoft, particularly because Access is an MDI application meant for programmatic manipulation. And if there's one thing every MDI application has, it's M D's. Generally, it's a good thing if those Ds can be enumerated and manipulated programmatically. Word, Excel, and PowerPoint are MDIs and they each expose a collection of windows. In fact, PowerPoint exposes two: one for Document windows and one for SlideShow windows. However, this highlights a difference between Access and its Office brethren that might explain its arrested development in this area. Access juggles a number of different kinds of Ds: tables, queries, forms, reports, data access pages, macros, etc.; whereas, Word, Excel, and PowerPoint mostly traffic in just one (or two). Exposing a heterogeneous collection of Ds in Access would no doubt be tricky, as the Ds represented in the various windows would embody behavior exclusive to the Ds' types, behavior the exposed window objects would have to accommodate.

As it stands, Access exposes a hodgepodge of collections and commands meant to satisfy the window-manipulating desires of its programmers. There's the Forms collection, which includes a reference to every opened Form-type D, and a similar collection for Reports and DataAccessPages. There are also the Allxxx collections, which were introduced with Access 2000: AllTables, AllQueries, AllForms, AllReports, AllDataAccessPages, AllMacros, and AllModules. And, for Access Data Projects, or ADPs, there are these: AllDatabaseDiagrams, AllFunctions, AllStoredProcedures, and AllViews. These collections expose all of the saved objects found in an Access project and data store database, whether or not they're open in a window, but each is exposed as an AccessObject, which includes an IsLoaded property indicating whether the object is currently open in a window, as well as a CurrentView property indicating how the object is being presented in the window (e.g., form browse, datasheet, preview, etc.). Then there's the Screen object's ActiveForm, ActiveReport, ActiveDataAccessPage, and ActiveDatasheet methods. While not collections themselves, they do poke into the (inaccessible) windows collection in order to do their thing. These methods satisfy a very limited (and, frankly, kind of odd) need where you know what kind of object you are looking for and you're only looking for it to be in the active window. If the active window doesn't contain the type of object sought, an error is raised. And, finally, there are the various object window manipulation methods of the DoCmd object, such as Maximize, Minimize, and Close. By default, all of these commands target the active window; however, some, like Close, support optional parameters for identifying specific objects by type and/or name. For those that don't support parameterized targeting, there is the SelectObject method, which can be used to first activate the window of a particular object.

When it comes to window manipulation, these collections and commands all suffer from the same basic problem: they all deal in Ds, not the windows that house them. At best, with these collections and methods you can get to objects that are known to be in windows, somewhere; however, you can't get to the windows themselves. For instance, you can't use these collections or commands to determine which object window is currently active (you can home in on it, at least for some objects, with the Screen object's methods, but it's not easy), and you certainly can't figure out the activation order of all the open windows, or enumerate the open windows to identify a particular one for activation. For many of the objects gleaned from these collections you can't tell whether their windows are hidden or minimized, and you can't subsequently hide or minimize them (or restore or maximize them). And for those objects whose window states you can (modestly) probe and manipulate, such as Forms and Reports, many of the commands provided by Access break down when faced with multiple instances because they rely on the use of object names to establish context. Similarly, the AccessObject CurrentView property breaks down when faced with multiple instances. And if that weren't enough, many of the properties available for window state evaluation and manipulation aren't consistently available across objects, and when they are available they don't all return consistent results. For instance, the Form and DataAccessPage objects expose a CurrentView property with which to determine (not change) an object's window presentation mode, but Report objects don't (at least not until Access 2007). And, the CurrentView values returned by Form and DataAccessObject objects don't correspond with the values returned by the generic AccessObject's CurrentView property, and the formers' CurrentView properties don't distinguish at all between normal views and print preview.

Anyhow, the point is that Access needs a single programmatically accessible collection of windows, so I wrote one (link in the last paragraph). It began as an effort to identify and get a handle on the object type (the D) present in the active window in order to support a journaling feature for a client's database. The journal is triggered by a hotkey, loaded at start-up in an AutoKeys macro, and when popped up needs to know (and get a handle on) the object present in the active window in order to provide a contextual journal entry experience. In other words, if the user was looking at a particular form when they pressed the journal's hotkey, I needed to know what kind of form they were looking at, and for certain types of forms I needed to get my hands on the form itself in order to retrieve certain values from it for the journal entry. If the user was looking at a particular report in preview mode when they pressed the journal's hotkey, I needed to know what kind of report, whether it was in design or preview mode, and be able to get my hands on the report object to evaluate its properties. Same for tables, queries, macros, and data access pages. Later I found myself also needing a handle on not just the current active window, but also the last active window containing objects of a particular type. By the time I was finished, I basically had a collection of Access windows in activation order that I could enumerate and manipulate at will. To make the collection more interesting, I added a number of methods to the collection's window class to support direct window manipulation, such as Activate, Maximize, Minimize, and Restore, as well as Hide and Unhide. These methods are constrained by characteristics of the windows' underlying objects to prevent improper operations, such as hiding a modal form or minimizing a form with a dialog border (unless in preview or design mode). I added a CurrentView property and wired it so that it works consistently for all object types, including reports, and it correctly identifies preview mode for all object types. I added some find methods to the collection class allowing windows to be sought by their handle (hWnd), title, object name (great for dealing with multi-instance forms and reports), object type, view mode, and visibility (note that the first two methods return a single window instance, or Nothing; the latter all return collections). And, finally, I added some sample code illustrating how to use it all (well, most of it) by constructing a Window menu replacement that includes an activation-ordered list of visible windows that includes icons next to each window illustrating the window's current view (e.g., design, datasheet, preview, etc.), as well as an expanding Unhide menu (the database also contains some sample forms, reports, macros, etc. with which to test the sample menu, which opens when the database is opened).

I think the code is mostly self-explanatory. It consists of just two classes: WindowsClass and WindowClass. The former is a custom collection class, the latter represents the objects held in the collection. There are some comments in the code highlighting certain important points, like the use of the Get/SetWindowPlacement APIs instead of the WM_MDIxxx messages for state changes; a difference with Access in the handling of layered modal forms and reports; the use of undocumented attributes on the custom collection class to expose an IUknown enumerator and default indexed Item property; and the fact that the WindowsClass collection is intended to be instantiated each time when needed, not held on to, because the collection is an immutable snapshot that will go stale. The best way to deal with this is to create a module-level function named Windows that simple returns a new instance of WindowsClass when needed (this is illustrated in the Window menu replacement sample).

There are some additional shortcomings and potential issues worth mentioning. For instance, I've noticed that the window-hiding logic in the WindowClass.Visible property's setter logic isn't removing windows from the Windows Taskbar if the database is configured to show them there. This is probably due to using the ShowWindow API instead of the WM_MDIxxx messages, as mentioned above. Another issue is that the WindowClass is currently limited to MDB-based Access databases, as it relies on DAO for access to the TableDefs and QueryDefs collections for the WindowClass.Object property. Maybe someday I'll get around to making it more ADP friendly. Finally, it's important to note that this database and its code were put together in Access 2003 on Windows XP. It is very possible that Access 2007 (or even Vista, due to the extensive use of Windows APIs) will bust it wide open.

The code in this Access database is free for use any way you want. The only restriction is that you don't try and pawn it off as your own, unless of course you whack it up so much that it can't be identified anyway. No warranties, no guarantees, and no refunds. Proceed with extreme caution. I've tested this code, some, but only portions of it have been used in an actual production environment. For all you and I know it could go to hell in a hand basket at any moment, so make sure you understand what you're getting into. That said, if you have any problems with it, or add any interesting features, please come back and tell us about it. After all, I'm not just the author, I'm a user, too.

Good luck.



TrackBack URL:  https://www.typepad.com/services/trackback/6a00d8341c7bd453ef00e54f0530db8834

Listed below are links to weblogs that reference Access Windows Collection: