Drown End-User Blues In Custom Menu Bars

by Jim Bailey

As users increasingly learn how to create their own customized forms and reports, more of these Access end users want to be included in the development process. Local management and customers are less willing to accept the old excuse, "The computer won't let us do it that way." However, creating a seamless link between a developer's work and the needs of the end user is a daunting problem.

End users expect each new release to handle all their custom documents. As a developer, I sometimes have trouble understanding the requirement for so much flexibility—but I have end users who would string me up by the thumbs at the first sign of software that didn't support the custom forms and reports they create.

The process is complicated if multiple sites are involved, especially when each site has a different set of custom objects. Upgrades in such an environment can be a little tricky. Dynamic menu bars created at runtime offer developers some stability in such situations, while protecting the end users' precious custom documents.

What progress! In just three paragraphs, we've solved the entire problem of custom forms and reports. In the remainder of this article, we'll discuss a system of defining and structuring dynamic menu bar commands.

Architectural overview

Our database system stores dynamic menu-bar property settings in MS Access tables. Access reads those properties at runtime to create menu bars, customized for each location. You can create dynamic menu bars by copying commands from existing toolbars (called templates here) and/or by reading the properties from the Access tables.

I use three tables to create menu bar specifications—tbl_MenuBar, ButtonImages, and tbl_MenuBarButton—as shown in Figure A. The tbl_MenuBar table lists custom menu bars specified by the menu bar's name, type, and other related properties.

Three types of menu bars will work in this scheme: docking, pop-up, and template. For docking and pop-up types, the value of the MenuBarType field specifies the menu bar's Position property.

You can position docking menu bars at the top, bottom, left, or right edge of the Access window. They may also appear as floating menu bars.

Pop-up menu bars appear when you right-click on a form. Link any form to a specific pop-up menu bar by setting the ShortcutMenu and ShortcutMenuBar form properties. Set ShortcutMenu to Yes and enter a menu bar name in the ShortcutMenuBar property to complete the link.

End users often want to create custom menu bars, as well as custom forms and reports. The template menu bar lets end users and developers work together in the process of creating dynamic menu bars. End users can customize menu bars by adding favorite shortcuts and commands. Such a menu bar becomes a template or pattern. When creating a new menu bar at runtime, each button on the template is copied to the new menu bar, thus allowing the developer and end users to work together in harmony. To specify a template menu bar, make an entry in the InitializeFrom field of the tbl_MenuBar table.

The ReplaceActive field is type YesNo. When the field's value is set to Yes, the menu bar created at runtime will replace the active menu bar. Forms, reports, tables, queries, and other database objects all have default active menu bars. For example, an Access form's default active menu bar contains File, Edit, Insert, View, and other pop-up command buttons. If ReplaceActive's value is No, the new menu bar will appear in addition to the default menu bar.

The InitializeFrom field may be blank, or it may contain the name of an existing menu bar. When this value is set, the controls on the template menu bar are copied to the dynamic menu bar when it's created at runtime.

The tbl_MenuBarButton table in Figure A describes all the buttons to be created at runtime. This table employs a self join to describe two types of menu bar controls: pop-up buttons and command buttons. (Because of the self join, the table appears twice in our architecture.) It also defines placeholders (described in more detail later).

A pop-up button, when clicked, pops up a stack of command buttons. A command button, as defined for this discussion, opens a form, a report, or a query. It can also execute a macro or function.

The overall architecture described here allows for nested pop-up buttons on a menu bar. However, our example application doesn't support that provision. For purposes of this discussion, pop-up buttons may appear only on the dynamic menu bar itself and not on its branches.

As shown in Figure A, the tbl_MenuBarButton table's self-join foreign key field is named LnkGroupID. When this field is null, a button is a pop-up control or a placeholder. When it isn't null, the LnkGroupID field serves as a foreign key relating command buttons to pop-up buttons or placeholders.

Command buttons linked to placeholders appear s commands on the root of a menu bar. Commands linked to pop-up buttons appear only when the user clicks the related pop-up button. A placeholder isn't created on the menu bar at all—it simply holds a place for one or more command buttons.

The third table shown in Figure A, ButtonImages, lists images available for menu bar command buttons. Each image is assigned an ID, which is stored in a menu bar button property Microsoft named FaceID. As far as I know, there's no complete list of images and their related FaceID values. The ButtonImages table lists the first 1,714 images from my system, along with their related FaceID values. (I can't remember why I stopped at 1,714).

Beyond the architecture

Now that we've described the architecture, let's see how to use it to specify and test dynamic menu bars. I use one main form and two nested subforms to facilitate menu bar specification. One of the subforms is nested in the other, creating three levels of parent-child relationship.

The main form, shown in Figure B, is bound to tbl_MenuBar. It shows three records, one of each menu bar type (template, docking, and pop-up). Notice that the menu bar named RunTimeMB in the first record uses a template specified by a setting in the InitializeFrom column.

The caption of the Try button in the upper-left corner of Figure B changes with the current record. Clicking the button creates the selected menu bar for testing purposes. Note that when you create a pop-up menu bar, you must click the Try button and then right-click on the main form to display the new menu bar. Menu bars created using the Try button contain a Kill command to delete the menu bar.

Figure C shows the first subform, which manages pop-up control buttons and placeholders. If the PopUpButton field isn't checked, the entry is a placeholder.

The second subform, shown in Figure D, manages menu bar commands. Every record on this form is a command and must be linked to a placeholder or pop-up button from Figure C. For purposes of this discussion, a command opens a form, a report, or a query. It might also execute a macro or Visual Basic for Applications (VBA) function.

The table structure lets users and developers control the appearance and positioning of pop-up and command buttons on a menu bar. The actual location of a control on the menu bar is determined by the values in the SortOrder field shown in Figures C and D. This field doesn't require unique values, but absolute button position can only be controlled with a unique value.

The BeginGroup property allows you to specify whether a horizontal or vertical divider line will appear between command buttons. Figures E and F show menu bars created with BeginGroup set to False and True, respectively. This value has no effect on a placeholder.

Specify the tool tip for each menu bar object with the Tooltip property. The tool tip is the text that appears when the mouse floats over an object. This property has no effect on a placeholder.

Five commands are available for menu bars created using this architecture, and Figure D shows one of each: Open Form, Open Query, Open Report, Execute Function, and Execute Macro. Most of the fields on this form require no explanation. However, the column labeled Form, Query, Report, Macro or Function Name may be an exception.

When the Command Type column is set to ExecuteFunction, the entire syntax, including the parameter list, is required in the column labeled Form, Query, Report, Macro or Function Name. Figure D shows such an entry.

Setting Command Type to any other option (such as OpenForm or OpenReport) causes a list of related object names to appear in the column labeled Form, Query, Report, Macro or Function Name. For example, if OpenForm is selected as the CommandType, a list of forms in the CurrentDB file appears in the Form, Query,… column. Selecting OpenQuery yields a list of QueryDef object names, and so on.

The system we're discussing provides generic functions for handling each database object through a menu bar button's OnAction property. The OnAction property defines what the button does when it's clicked. Listing A shows how this property is set for each of the command options using a function named MakeOnAction.

Listing A: Code to create an OnAction property setting for a menu bar command button

Function MakeOnAction(r As Recordset) As String
     'create the OnAction property for a button
    Dim q As String * 1 
    Dim Synchronize As String
    Dim Command As String
    Dim s As String  'returns function value
    Dim params As String
    
    Command = Nz(r!Command, "")
    If Command <> "" Then
        If Nz(r!Synchronize, False) Then
           Synchronize ="True"
        Else
           Synchronize ="False"
        End If
        q = Chr$(34)  'double quote character
        params = "(" & q & Command & q 
        params = params & "," & Synchronize _
                 & ")"

        Select Case r!CommandType
        Case CMD_OPEN_FORM             '1
            s = "=MenuBarOpenForm" & params
        Case CMD_OPEN_QUERY            '2
            s = "=MenuBarOpenQuery" & params
        Case CMD_OPEN_REPORT           '3
            s = "=MenuBarOpenReport" & params
        Case CMD_EXECUTE_MACRO         '4
            s = Command
        Case CMD_EXECUTE_FUNCTION      '5
            s = "=" & Command
        End Select
    End If
    MakeOnAction = s  'Set return value
End Function

The Recordset argument passed into the MakeOnAction function is a dynaset of the menu bar command button definitions to be created at runtime. Recordset navigation is managed outside of the function. The OnAction strings used to create the runtime menu bar described in Figure D are as follows:

=MenuBarOpenForm("frmFaxCoverSheet",True)
=MenuBarOpenQuery("qStatus",True)
=MenuBarOpenReport("rptPaidDocuments",True)
=FindDiscrepancy("Last")
macroDisr

These Open functions, which open forms, queries, and reports, use the Synchronize field shown in Figure D. Since Access considers forms and reports to be documents, we can reasonably refer to these generic functions as document handlers.

It isn't uncommon to synchronize the current record in one form with that of another. As you can see, the value of the Synchronize field is passed into the document-handler routines as a function argument. When using this idea in your own applications, include code in the document handler to manage synchronization, as required in your system. Doing so relieves the end user of the responsibility of including code or macro calls in a custom form or report.

The FaceID property for each command button is managed by the Style and Face columns in Figure D. You can set the style to Auto, Icon, Text, or both (Icon and Text). These values are documented in Access help files, so we won't discuss them further here.

The Face column is bound to the FaceID property. You set this value using an integer from the ButtonImages table. However, the Face Assignment menu bar shown in Figure D makes this process a little easier—simply select a record on the form, then click a button on the Face Assignment menu bar to manage the FaceID value setting.

Implementation

You can find our sample application at www.zdjournals.com/adj. If the program won't compile on your system, open a VBA module in design view and choose the Tools | References… menu item, then select MS Office 8.0 Object Library (from the default module menu bar). The module included with the sample file includes all the code required to run the new menu bar setup and testing application.

To link the functionality to your own Access applications, import or link the tables shown in Figure A to your MDB file(s). Also import the forms, queries, and module. The module contains a BuildMenu function, which creates a new menu bar based on table data. Call BuildMenu at the beginning of your application to create one or more custom menu bars for use while the application runs.

BuildMenu's argument list is as follows:

Function BuildMenu(TargetMenuBarID As Long,
    Optional VisibleSetting As Boolean = True, 
    Optional doKillBtn As Boolean = True, 
    Optional doCloseBtn As Boolean = True) 
As Boolean

The TargetMenuBarID argument corresponds to the primary key in the tbl_MenuBar table. The VisibleSetting argument determines whether the new menu bar will be visible after creation. The doKillBtn argument determines whether the menu bar will contain a kill button, as we described earlier.

The doCloseBtn argument determines whether Access will include a Close Form button as the last item in the menu bar. I like to put a Close Form button on menu bars; such a button lets users know exactly where the close button is for every form (the last item on a menu bar), and it works on any form. You could modify it to work with reports and queries as well.

Conclusion

Many Access users expect to be part of the development process. Creating forms, reports, and queries is well within the grasp of users with minimum technical skills. However, designing a system to accommodate this requirement is a daunting task for application developers. As we've explained in this article, dynamic custom menu bars created at runtime provide seamless integration between end users and application developers.

Copyright © 1998, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.