Creating a Main Menu with the Menu Manager Add-In

When all of the major forms and reports for the database have been created, you can run my Menu Manager add-in to create a main menu for the database, allowing users to select forms and reports from a combobox on the main menu.

Installation

To install the add-in, copy the Menu Manager add-in’s library database (Menu Manager.mda) to your AddIns folder (usually C:\Documents and Settings\Administrator\Application Data\Microsoft\AddIns). Open the Toy Workshop database (or any other database where you want to create a main menu), select Tools|Addins to open the Add-ins menu, and select the Add-In Manager selection. The Add-In Manager dialog opens, as shown in Figure 6.35.

Figure 6.35

There should be a Menu Manager entry; if not (perhaps because it was placed in a nonstandard location), click the Add New button and browse for the file. Once it appears in the list of available add-ins, select it and click Install. A appears next to the Menu Manager selection to indicate that it is installed. Close the dialog to complete the installation.

The Add-ins menu will now have three new selections: Create Main Menu, Change Menu Picture, and Refresh Lookup Tables, as shown in Figure 6.36.

Figure 6.36

Preparation

Before running the Create Main Menu command, there are a few things you should check, to prevent errors. The Menu Manager add-in assumes that the main form in the database (the one used most frequently) has the prefix fpri, so first select the form you want to be the main form, and give it this prefix. I renamed the main Orders form as fpriOrders.

When changing a form name, if there are comboboxes or subforms on the form, check their row or record sources to see if there are references to the form name that also need to be changed. In this case, a reference to frmOrders in cboShipAddressID’s row source needed to be changed to fpriOrders.

If you don’t make this change, you will get the annoying “Enter Parameter Value” error message, which doesn’t tell you where the incorrect or missing parameter is needed. An incorrect reference to a form name in the following places can cause this message:

* ORDER BY clause on a form or subform

* Combobox or listbox row source

* Query criteria

* Subform data source

The Menu Manager also needs references to the Data Access Objects (DAO), Scripting Runtime and Word object libraries, so you’ll need to check that you have these references, or add them if you don’t. To check your references, open a code module and select References from the Tools menu to open the References dialog. Figure 6.37 shows the References dialog for the Toy Workshop database.

Figure 6.37

The DAO 3.6 Object Library reference is checked, but not the Scripting Runtime and Word 10.0 Object Libraries, so you’ll need to scroll down the list of references and check them. Figure 6.38 shows the Scripting Runtime selection checked, and the Word object library selection being checked.

Figure 6.38

The list of available references is alphabetical—sort of. There are two exceptions: the checked references are all at the top of the list, and any references that have previously been checked are listed directly under the checked references, before the A’s, to make it easier to locate and check them again.

If you see #Name instead of your primary form name next to the large button on the top left of the menu, this is a sign that you have a missing reference.

Running the Add-in

To create a main menu for an application, select the Create Main Menu selection on the Add-ins menu. After some flickering as various database objects are processed, the Menu Sidebar Picture Picker dialog opens, as shown in Figure 6.39. Here you can enter an application title, and select one of a set of standard sidebar pictures for your application. These pictures use a color palette that works well even on low-resolution monitors, and can be stretched without distortion.

Figure 6.39

After entering an application title, and selecting an image, click the Apply Picture button to create the main menu. You will get a confirmation message asking if you want to open the main menu now; on clicking Yes, the main menu will open, as shown in Figure 6.40.

Figure 6.40

After creating a main menu using the add-in, select Tools|Startup from the database window, and select fmnuMain as the Display Form/Page, as shown in Figure 6.41. From now on, when the database is opened, the main menu will automatically open.

Figure 6.41

Curiously, the forms in the Display Form/Page drop-down list aren’t in alphabetical order, so you may have to scroll down a few screens to find fmnuMain.

The main menu has several large command buttons with images, and comboboxes displaying lists of database objects for the user to select, as an alternative to the confusing (and difficult to maintain) array of pop-up menus created by the built-in Access Menu Wizard.

• The large Orders button opens the application’s primary form (in this case, fpriOrders). The primary form is indicated by the “fpri” tag; its name is saved in tblInfo.

• The Backup button offers to create a backup copy of the database, including the date and an incrementing number. The backups are created in a folder called “Backups” under the database folder. This folder will be created if it doesn’t already exist.

• The Exit button closes the database and exits from Access.

• The Other Forms button opens the form selected from its combobox, which lists all forms with a check in the Use checkbox in tlkpForms, excluding the primary form, system forms, and other inappropriate entries, based on their LNC tags. To remove a form from the drop-down list (without removing it from the database), just uncheck the Use checkbox for the form’s record in tlkpForms.

• The Reports button and combobox work similarly, with the addition of a few extra controls useful for reports, where users can choose to open the report in print preview or just print it, and a set of date textboxes, which can be used to filter a report by a date range. To remove a report from the drop-down list (without removing it from the database), just uncheck the Use checkbox for the report’s record in tlkpReports.

• The Word Letters and Recipients button and comboboxes let you select a Word template and a recipient for a letter. Automation code on the command button fills custom document properties in the Word letter with name and address data from Access. To test this feature with sample data, select Test Letter in the Word Letters combobox, then select a recipient from Recipients and click the Word command button. To replace these with real templates and data, see Chapter 11, Working with Word.

The tlkpForms and tlkpReports lookup tables contain several fields of useful information, other than what is displayed in the drop-down lists. The tlkpForms lookup table is shown in Figure 6.42.

Figure 6.42

The DisplayName field is filled with form captions. If you forgot to enter a caption for a form or report, the DisplayName field will contain “[No caption]” (see frmRepairs). After entering the form’s caption in its properties sheet, you can manually edit the caption in tlkpForms to match (or run the Refresh Lookup Tables command from the Add-ins menu). A portion of the tlkpReports lookup table is shown in Figure 6.43.

Figure 6.43

The DisplayName field is what shows in the drop-down list; it is picked up from the report’s caption. The RecordSource field lists the report’s record source, and the Width field gives the report’s width, which is used to create a LTR or LGL prefix to indicate whether reports use letter- or legal-sized paper. If you only use letter-sized paper, and don’t need the prefix, you can replace the calculated expression listed below

Description: IIf([Width]>11.25,”LGL: “ & [DisplayName],”LTR: “ & [DisplayName])

with the DisplayName field in the cboReports combobox’s row source.

The RecordSource property is used to determine whether or not a report has any records, before attempting to open it. This feature will only work with table or query record sources, so if you frequently use SQL statement record sources for reports, and you don’t want to convert them to saved queries, you can comment out (or delete) the lines of code that check the record source before opening the report.

The Use field is checked when you want the form or report to appear in the drop-down list on the menu; uncheck it to hide the form or report, for example for a form that is only opened from another form. You’ll probably want to uncheck any report templates (they start with the tag rtmp). I unchecked the Use checkbox for the report templates in the Toy Workshop database.

You can change the menu picture at any time by running the Change Menu Picture command from the Add-ins menu; it opens the Picture Picker dialog again. After creating new forms and reports, you can either add them to the lookup tables manually, using a drop-down list on the Object Name field in Datasheet view and entering the other information manually (OK for just a few changes), or you can run the Refresh Lookup Tables command to rerun the functions that fill the lookup tables with form and report names and other information.

The main menu is bound to a table called tblInfo, which stores selections for future use. This technique allows you to preserve your selections for a form, report, Word template, and/or date range from one application session to another. The table can also be used to store other miscellaneous information you need to use in the database (as a more stable alternative to global variables, or to preserve information indefinitely). I use extra fields in tblInfo to store information for creating faxes in Chapter 14, Working Outside of Office.