Using Helen Feddema s Add-Ins | Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))

3 4

I’ve written several application-specific add-ins for Access as well as one COM add-in that runs in Access. Chapter 21, "Creating Your Own Add-Ins," describes how I created the LNC Rename Access add-in and the LNC Rename COM add-in. In this section, you’ll learn how to use these add-ins and one other in the Access interface.

You can create your own add-ins to save time or add functionality to Access. To find out how, see Chapter 21, "Creating Your Own Add-Ins."

The add-ins referenced in the following sections appear on the companion CD

Using the LNC Rename Add-In

The LNC Rename add-in allows you to apply the Leszynski Naming Convention (LNC) to existing objects in a database. The LNC Rename add-in includes three menus for renaming database objects and controls on forms and reports plus two builders for renaming an individual control or all controls on a form or report.

See Chapter 20, "Customizing Your Database Using VBA Code," for details on the LNC.

Installing the LNC Rename Add-in

To install the LNC Rename add-in, follow these steps:

Copy the LNC Rename.mda file from the companion CD to your AddIns folder (typically C:\Documents and Settings\<username> \Application Data\Microsoft\AddIns for a stand-alone Office installation in Windows 2000 or C:\Windows\Application Data\Microsoft\AddIns for Windows Me).
Using the Add-In Manager, install the LNC Renaming add-in. (When you load the add-in, Access might warn you that the LNC Rename add-in contains macros—which can carry viruses—and ask whether you want to enable the macros. The macros don’t contain viruses, and you must enable the macros for the add-in to work properly.)

Three selections now appear on the Add-In menu (Rename Form Controls, Rename Report Controls, and Rename Database Objects).

Installing Find and Replace

The LNC Rename add-in uses the Find and Replace add-in for some chores (renaming database objects in particular). (Find and Replace is an Access add-in created by Rick Fisher; a 30 day trial version of Find and Replace is included in the Third-Party Add-Ins section of the companion CD.) If you already have Find and Replace version 9.00e installed, you’re all set; otherwise, follow these steps to install Find and Replace:

Double-click the repl9.exe file on the companion CD to run the WinZip Self-Extractor.
Unzip the Find and Replace files to the AddIns folder (C:\Documents and Settings\<username> \Application Data\Microsoft\AddIns for Windows 2000 or C:\Windows\Application Data\Microsoft\AddIns for Windows Me).
Find and Replace Help opens. You can close it.
Using the Add-In Manger install Find and Replace 9.00e item add-in.

Now you’re ready to use the LNC Rename add-in. It will invoke Find and Replace as needed to rename references to database objects.

Rename Database Objects

You can use the Rename Database Objects command to apply the LNC to a database that’s totally devoid of a naming convention. The sample database used to demonstrate the LNC Rename add-in is Event Management.mdb. Event Management.mdb is a database created using the Access Database Wizard. Because the wizard doesn’t use any naming convention when it creates database objects and controls, the database needs extensive renaming.

To use Rename Database Objects to apply the LNC, follow these steps:

On the Add-Ins menu, click Rename Database Objects to open the Choose Database Objects form, as shown in Figure 15-33.
Figure 15-33. On the Choose Database Object form, select the type of database object to rename.
Click Tables as the type of database object to rename, and then click the Rename Database Objects button.

tip

The majority of references in VBA code will be to tables, so it’s advisable to rename tables first and then rename queries, forms, reports, macros, and modules (although in many cases there will be no need to rename macros or modules).
The Tables To Rename dialog box (shown in Figure 15-34) will appear, listing all the tables in the database and suggesting new names using standard LNC tags. If you don’t want to rename a table, clear its check box in the Use column.
Figure 15-34. The tables in a database are renamed with the LNC tbl tag.
Click the Rename Checked Tables button. The first time you click the button, Find and Replace will ask you to locate the add-in’s help file (Replace9.hlp).
Click OK and navigate to the directory to which you extracted the repl9.exe files (C:\Documents and Settings\<username> \Application Data\Microsoft\AddIns for Windows 2000 or C:\Windows\Application Data\Microsoft\AddIns for Windows Me), select the Replace9.hlp file, and click Open. A dialog box will be displayed indicating this is a shareware version of Find and Replace that you can evaluate for 30 days.
Click OK. Find and Replace will open, with the information about old and new table names preloaded, and it will proceed to locate all the references to the old object names in the database so you can accept or reject the proposed change. In a database in which the same name has been used for different database objects, be sure to check the context before you accept the proposed changes so you won’t, for example, rename a form with the tag for a table (as shown in Figure 15-35).
Figure 15-35. Some of the proposed name changes might be inappropriate.

Rename Form Controls and Rename Report Controls

After renaming all the database objects, you can run the Rename Form Controls and Rename Report Controls commands to rename controls on all forms or all reports. To rename controls on all forms, follow these steps:

On the Access Add-Ins menu, click Rename Form Controls to open the Forms To Rename dialog box (shown in Figure 15-36). By default, all forms are selected, but if you don’t want to rename controls on a specific form, clear its Use check box.
Figure 15-36. In the Forms To Rename dialog box, you select forms whose controls you want to rename.
Click the Rename Controls On Checked Forms button. The add-in will proceed to rename all the controls on the selected forms and then display a message asking whether you want to save the original control name to each control’s Tag property.
The default button is No, to not save the original control name to the Tag property. Generally, this is the appropriate choice. However, if you have some reason to preserve the original control names, click Yes.
For each control on the form, you’ll see a message box containing the control name, the control source (for bound controls), and the proposed new name, using the LNC tag for that type of object. Click Yes to accept the proposed new name; click No if you want to modify the name. Most of the proposed names will be correct and can be accepted as is, but you might have to type in the correct name in the case of very long descriptive labels or controls with calculated expressions.
After all the controls have been processed, you’ll get a message asking whether you want to save and close the form. Normally, you can click Yes to save and close the form, but occasionally you might want to review a form before saving it with the renamed controls. In that case, click No to leave the form open in Design view. When all the forms have been processed, you’ll see a message stating that all forms have been processed.

The LNC Builders

If you want to rename a single control on a form, you can use the LNC Rename Current Control builder. To do so, follow these steps:

On the control’s properties sheet, click the Build button next to the control’s Name property, as shown in Figure 15-37.
Figure 15-37. To rename a control, click the Build button next to its Name property on its properties sheet.
The Choose Builder dialog box (shown in Figure 15-38) will open, offering you a choice of LNC Rename Current Control, which renames just the current control, or LNC Rename All Controls, which renames all the controls on the current form or report.
Figure 15-38. The Choose Builder dialog box lists the two LNC builders.
Select the LNC Rename Current Control builder, and then click OK. If you the name does not use LNC, you’ll see a message suggesting the proposed new name using the appropriate LNC tag for the control type.

InsideOut

By default, Access gives a bound control the same name as its field. This can lead to circular reference errors, so it’s especially important to rename bound controls in order to avoid confusion with fields of the same name.

See Chapter 6, "Working with Form Controls," for more information about circular reference errors.

To rename all controls on the current form or report, you can use the LNC Rename All Controls builder. To use this builder, open the detail section’s properties sheet, and click the Build button next to the Name property. In the Choose Builder dialog box, click LNC Rename All Controls and click OK. The builder will display a message suggesting a new name for each control in turn.

Renaming Controls Using the LNC Rename COM Add-In

I wrote the LNC Rename COM add-in to fill a gap between the Rename Form Controls (or Rename Report Controls) commands and the LNC Rename All Controls in the Choose Builder dialog box. The LNC Rename COM add-in renames form controls or report controls on all currently open forms or reports. This add-in places a Rename Controls button on the Form Design and Report Design toolbars for quick access, as shown in Figure 15-39.

figure 15-39. the rename controls button lets you quickly rename the controls on a form or a report.

Figure 15-39. The Rename Controls button lets you quickly rename the controls on a form or a report.

You can click the Rename Controls button to cycle through all controls on all open forms or reports, renaming each control, and displaying the same message box as for the Access add-ins. The LNC Rename COM add-in is located on the companion CD and is named LNC Rename.dll. For instruction on how to install, see the section "Installing a COM Add-In" earlier in this chapter.

Creating a Main Menu Using the Menu Manager Add-In

The Menu Manager add-in creates a main menu for an Access application, with drop-down lists for selecting database objects. It consists of three menu add-ins, one for creating the main menu and two for doing housekeeping chores later on. To install and test the add-in, follow these steps:

Copy the Menu Manager.mda add-in included on the companion CD to your AddIns folder (C:\Documents and Settings\<username> \Application Data\Microsoft\AddIns for Windows 2000 or C:\Windows\Application Data\Microsoft\AddIns for Windows Me).
Open the Test Database.mdb file included on the companion CD. This database contains some Northwind components, with the LNC applied, and a line of code added to the Close or Unload event of its forms to display the main menu when a form is closed.
Click the Tools menu, Add-Ins, and Add-In Manger.
Use the Add-In Manager to install the Menu Manager add-in.
Click Create Main Menu on the Add-Ins menu.

note

The Menu Manager relies on capabilities in Microsoft Word 2002. If you don’t have Word 2002 installed, you will likely get the following error when you try to use the Menu Manager add-in: "Microsoft Access can’t find the wizard, or the wizard has not been installed, or there is a syntax error in the Declarations section of a Visual Basic module." To eliminate this error, you will need to install Word 2002.
After some processing, the Menu Sidebar Picture Picker dialog box will open. Enter the application title, and select a sidebar picture for the main menu, as shown in Figure 15-40.
Figure 15-40. Select a sidebar picture for the main menu.
Click the Apply Picture button to save the selected picture to the main menu form. A message box will appear, asking whether you want to open the main menu now. Click Yes to open the main menu, as shown in Figure 15-41.
Figure 15-41. The new main menu lets users select forms and reports or create Word letters.

On the main menu, the Orders button opens the application’s primary form (which is identified by the fpri tag). (The form name can also be hard-coded if you don’t want to use this tag.) The other forms in the database are accessed via the Other Forms drop-down list, and the reports are accessed via the Reports drop-down list, as shown in Figure 15-42. Reports are listed by their captions, with a prefix indicating the paper size. These combo boxes are based on lookup tables that the add-in filled with form and report data.

figure 15-42. users can select a report from the main menu’s reports drop-down list.

Figure 15-42. Users can select a report from the main menu’s Reports drop-down list.

The Word Letters drop-down list lets users select a Word letter to fill with data from Access. Users can select recipients from the Recipients drop-down list. This part of the interface requires some coding to select your Word templates and the specific data you want to export to Word.

After adding or deleting forms or reports, you can refresh the form and report look-up tables by choosing Tools, Add-Ins and then clicking Refresh Lookup Tables.