Creating and Testing Add-Ins for Microsoft Access for Windows 95

Microsoft Corporation

October 1995

Click to view or copy the files for the sample application.

Abstract

An add-in is a tool such as a wizard, a builder, or a menu item that you can create to provide additional functionality to Microsoft Access for Windows® 95. When you develop an add-in, you may receive various error messages during the testing and debugging process. This document describes six scenarios that illustrate the most common problems you may encounter with add-in development. In addition, this document provides step-by-step instructions for reproducing and solving the problems using the sample files included in the Addins.exe file.

Getting Started

To use this document, you need to set up the sample files (included in the Addins.exe file) by copying them to specific folders on your computer.

1. Copy Wzaddin.mda to your Microsoft Access folder, for example:

   \msoffice\access

2. Copy Wzcodlib.mda to your \Program Files\Common Files folder, for example:

   c:\program files\common files

   Or, in Microsoft® Windows NT®, copy Wzcodlib.mda to your \Winnt35\Msapps folder, for example:

   c:\winnt35\msapps

This document assumes that you are familiar with writing an add-in database, creating a USysRegInfo table, using the Add-in Manager to install an add-in, and creating references to library databases. If any of these topics are new to you, please read the following information before proceeding with this document:

• For information about creating and installing add-ins, please see "Building Applications with Microsoft Access for Windows 95," Chapter 16, "Creating Wizards, Builders, and Add-ins," pages 349–375.
  
  
• For information about creating a USysRegInfo table, please see the USysReg.doc file, which is included in the Addins.exe file.
  
  
• For information about referencing library databases, please see "Building Applications with Microsoft Access for Windows 95," Chapter 12, "Using Library Databases and Dynamic-Link Libraries," pages 293–295.

Creating and Testing Add-ins

Scenario #1

You want to add a menu item to the Add-ins submenu (Tools menu), which runs a specific function. To implement the menu add-in, you perform the following tasks:

• You create a custom function in a library database (.mda) file.
  
  
• You create a USysRegInfo table in the .mda file and add information for a menu add-in that calls the custom function.
  
  
• You create a new database (.mdb) file and try to test the menu add-in (by clicking the Tools menu and pointing to Add-ins).

Problem

When you point to Add-ins on the Tools menu, the menu add-in does not appear on the submenu.

Cause

You did not run the Add-in Manager to load the add-in.

Solution

Run the Add-in Manager (from the Add-ins submenu on the Tools menu) to set up the add-in.

Steps to Reproduce and Solve

1. Create a new database called Test1.mdb.
   
   
2. On the Tools menu, point to Add-ins. Note that no custom add-ins appear on the submenu; only the following Microsoft Access add-ins appear:

   Switchboard Manager
   Add-in Manager
   Database Splitter
   Linked Table Manager
   Menu Builder

3. On the Add-ins submenu, click Add-in Manager.
   
   
4. In the Add-in Manager box, select Wzaddin.mda, and then click Install. This should load the add-in database and make its features available in Test1.mdb. Close the Add-in Manager box.
   
   
5. On the Tools menu, point to Add-ins. Note that there is an additional add-in at the bottom of the submenu:

   Menu Addin Example

Scenario #2

You create a builder such as a "color picker" for use when setting the BackColor property of a control or section in a form or report. To implement the builder, you perform the following tasks:

• You create a form in an add-in database to act as a color picker.
  
  
• You create a custom function to open the color picker form.
  
  
• You create (or modify) a USysRegInfo table in the add-in database by adding information for the builder.
  
  
• You create a new database (.mdb) file and use the Add-in Manager to load the add-in database.
  
  
• You create a new form and try to set the BackColor property of the detail section. In the Choose Builder box, you select the color picker and click OK.

Problem

When you try to open the builder from an event property, you receive the following error message:

Can't start the wizard, builder, or add-in.

Cause

You have an incorrect setting for the builder in the USysRegInfo table. For example, the specified library has an incorrect path setting or filename.

Solution

Uninstall the add-in database (by using the Add-in Manager), and then restart Microsoft Access. Open the add-in database, and then verify the settings in the USysRegInfo table for the builder.

Steps to Reproduce and Solve

1. Open the Test1.mdb database, which you created for Scenario #1.
   
   
2. Create a new form not based on any table or query in Design view.
   
   
3. Select the form's detail section, and open the property sheet.
   
   
4. Select the BackColor property, and click the Build button.
   
   
5. In the Choose Builder box, click Simple Color Picker, and then click OK. You should receive the error message "Microsoft Access can't start the wizard, builder, or add-in". Click OK to close the message box.
   
   
6. On the Tools menu, point to Add-ins, and then click Add-in Manager. Select the Wzaddin.mda file, and click Uninstall.
   
   
7. Close the Test1.mdb database, and click No when asked whether you want to save changes to any objects.
   
   
8. Quit and then restart Microsoft Access.
   
   
9. Open the Wzaddin.mda add-in database (located in your Microsoft Access folder).
   
   
10. On the Tools menu, click Options.
    
    
11. In the Options box, click the View tab. Under Show, click to select the System Objects box, and then click OK.
    
    
12. Open the USysRegInfo table, and locate the following record:

    Subkey  HKEY_CURRENT_ACCESS_PROFILE\Wizards\Property Wizards\BackColor\Simple Color Picker

    Type: 1

    ValName:Library

    Value: |ACCDIR\testaddin.mda

    Note: "|ACCDIR\testaddin.mda" is listed in the Value field. This is an incorrect name for the add-in database.

13. Modify the Value field (for the Simple Color Picker) to the following name:

    Value: |ACCDIR\Wzaddin.mda

14. Close the USysRegInfo table to automatically save the change.
    
    
15. Close the Wzaddin.mda database.
    
    
16. Open the Test1.mdb database.
    
    
17. On the Tools menu, point to Add-ins, and then click Add-in Manager. Select the Wzaddin.mda file, and then click Install.
    
    
18. Repeat steps 2 through 4 for Scenario #2. Note that the Simple Color Picker opens without error.
    
    
19. In the Simple Color Picker, click Red, and then click OK. Press ENTER to change the color of the detail section to Red.
    
    
20. Close Test1.mdb, and click No when asked whether you want to save changes to any objects.

Scenario #3

You create an add-in wizard that automatically adds code to a module in a user's database (.mdb) file. This new code calls a function in your add-in database (.mda) file. To implement the wizard, you perform the following tasks:

• You create two custom functions in an add-in database; the first function inserts code into a form module that calls the second function.
  
  
• You create (or modify) the USysRegInfo table in the add-in database to add information about the wizard.
  
  
• You create a new database (.mdb) file and use the Add-in Manager to load the add-in wizard.
  
  
• You create a new form in Design view and turn on control wizards in the toolbox. You try to add a command button to the form. In the Choose Builder box, you select Simple Button Wizard and click OK.

Problem

When you try to open the add-in, you receive the following error message:

Sub or Function not defined.

Cause

The current database (.mdb) file does not have a reference to the add-in database.

Solution

In the current database, create a reference to the add-in database by using the References command on the Tools menu (in Design view of a module). Note that you cannot programmatically create a reference by using Visual Basic; you must create it manually.

Or, you can avoid creating a reference by using the Application object and Run method to call the function in the add-in database. For example, you can use the following syntax in your first function that adds code automatically to the current database:

Application.Run "<library>.<function>", [arguments]

This technique is used by Microsoft Access when you add an AutoDialer button to a form by using the Command Button Wizard. The AutoDialer automatically adds code to the form module that calls the wlib_AutoDial() function in the Utility.mda file.

Note that you can use the "Application.Run" syntax as long as the following requirements are met:

• The add-in database must have an .mda file extension.
  
  
• The add-in database must be located in the same folder that is specified as the AddInPath in the Registry.

You can locate this string value under the following Registry key:

HKEY_LOCAL_MACHINE\Software\Microsoft\Access\70\Wizards

Steps to Reproduce and Solve

1. Open the Test1.mdb database, which you created for Scenario #1.
   
   
2. On the Tools menu, point to Add-ins, and then click Add-in Manager. Verify that Wzaddin.mda is installed. If it is not installed, click Install. Close the Add-in Manager box.
   
   
3. Create a new form not based on any table or query in Design view. In the toolbox, turn on control wizards.
   
   
4. Add a command button to the form. In the Choose Builder box, select the Simple Button Wizard, and click OK.
   
   
5. Enter a caption for the new button, such as Test Button, and then click OK.
   
   
6. View the form in Form view, and then click the Test button. You should receive the following error message:

   Run-time error '2465'

   Microsoft Access can't find the field Wzaddin.mda referred to in your expression.

7. In the message box, click Debug.
   
   
8. Change the code that reads:

   Private Sub Command0_click()
   Beep
   [wzaddin.mda].wzad
   End Sub
   

   to read as follows:

   Private Sub Command0_click()
   Beep
   ' [wzaddin.mda].wzad
   Application.Run "wzaddin.wzad"
   End Sub
   

9. Save the code changes, and close the form.
   
   
10. Return to your form in Form view, and click the Test Button. You should receive a message box reporting that the Wzad procedure (from Wzaddin.mda) ran successfully. Note that you can use the "Application.Run" syntax in any type of add-in—a wizard, a builder, or a menu add-in—to run procedures located in an unreferenced library database.

Scenario #4

You create an add-in that uses two databases: a code library that contains common or generic functions (for use in multiple applications) and an add-in database that calls functions in the code library. To implement this type of add-in, you perform the following tasks:

• You create a code library database (.mda) file that stores generic functions.
  
  
• You create an add-in database (.mda) file and a form that calls a function in the code library.
  
  
• You manually create a reference in the add-in database to the code library.
  
  
• You create (or modify) the USysRegInfo table in the add-in database to add information about the add-in.
  
  
• You open a new database and run the Add-in Manager to install the add-in.

Problem

When you launch your add-in from the current database, you receive one of the following error messages:

Cannot find project or library

Or:

Compile Error

Cause

Microsoft Access cannot find the code library after searching the following locations for the code library:

• The folder that contains the add-in database.
  
  
• The folder that contains the current database.

Solution

You can move the code library file to the folder that contains the add-in or current database.

Steps to Reproduce and Solve

1. Open Windows Explorer (or File Manager), and verify that the following files are stored in different folders, for example:

   File Name	Location
   Wzaddin.mda	c:\msoffice\access
   Wzcodlib.mda	c:\program files\common files —or— c:\winnt35\msapps

   
   
2. Open the Test1.mdb database, which you created for Scenario #1.
   
   
3. In the Database window, click the Modules tab, and then click New.
   
   
4. In a new module, click the Build button on the toolbar (or using the right mouse button, click the module window to open the shortcut menu, and then click Build).
   
   
5. In the Choose Builder box, click Simple Code Wizard.
   
   
6. In the Simple Code Wizard box, click the "Call Code in Wzcodlib.mda" button. You should receive the following error message:

   Microsoft Access can't start the wizard, builder, or add-in.

7. Click OK to close the message box.
   
   
8. Close the Test1.mdb database, and click No when asked whether you want to save changes to any objects.
   
   
9. Quit Microsoft Access.
   
   
10. Switch to Windows Explorer (or File Manager), and move the code library database to the same folder as the add-in database.
    
    
11. Start Microsoft Access, and open the Test1.mdb database.
    
    
12. Repeat steps 3 through 5 (in Scenario #4).
    
    
13. In the Simple Code Wizard box, click the "Call Code in Wzcodlib.mda" button. You should receive a message box reporting that a function from the code library (Wzcodlib.mda) ran successfully.
    
    
14. Close the Simple Code Wizard box.

Scenario #5

You create a library database of generic functions that users can call from any database (for example, a library of statistical functions), which you sell as a separate product. To implement this type of code library, you perform the following tasks:

• You create a new code library database (.mda) file and store it in your Microsoft Access folder.
  
  
• You create generic functions in the code library.
  
  
• You open an existing database and call a generic function in the code library from an object (such as a form, a query, a report, or a module).

Problem

You receive one of the following error messages:

Sub or Function not defined.

Or:

Undefined function '<name>' in expression.

Or:

#Name?

Cause

The new, current database does not have a reference to the code library database.

Solution

You need to create a reference in the current database to the library database before calling any library functions. You must create the reference manually by using the References command on the Tools menu (in Design view of a module). You cannot programmatically create a reference by using Visual Basic.

Steps to Reproduce and Solve

1. Open the sample database Northwind.mdb.
   
   
2. Create a new module.
   
   
3. Open the Debug Window, type the following code, and then press ENTER:

   ? GetMedian("Orders","OrderID")
   

4. When you receive the "Sub or Function not defined" error message, click OK.
   
   
5. On the Tools menu, click References.
   
   
6. In the References box, click Browse.
   
   
7. In the Add Reference box, locate the Wzaddin.mda database, and then click OK.
   
   
8. In the Reference box, make sure that Wzaddin.mda is listed (as an available reference) and selected. Click OK to close the References box.
   
   
9. Repeat step 3. You should see the following statistical median in the Debug window:

   10662.5

Scenario #6

You created an add-in library with many user-defined types and Visual Basic procedures.

Problem

The library takes a while to load when a user opens the add-in.

Solution

You can slightly reduce the loading time of your add-ins by adding a Loadonstartup key to the Windows Registry. This key forces the add-in's type information to load when a database is opened. The key does not, however, load the actual add-in code. Add-in code is loaded "on demand" when a user starts the add-in.

IMPORTANT   This solution involves editing the Windows Registry. Before you edit the registry, you should first make a backup copy of the registry files (System.dat and User.dat). Both are hidden files in the Windows folder.

For information about how to edit the registry, view the Changing Keys And Values online Help topic in Registry Editor (Regedit.exe). Note that you should make a backup copy of the registry files (System.dat and User.dat) before you edit the registry.

WARNING   Using Registry Editor incorrectly can cause serious problems that may require you to reinstall Windows 95. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

To add the Loadonstartup key to the registry, follow these steps:

1. In Windows 95, click the Start button, and then click Run. In Windows NT, open File Manager, and click Run on the File menu.
   
   
2. In Windows 95, type "regedit" (without the quotation marks) in the Open box, and then click OK. In Windows NT, type "regedit32" (without the quotation marks) in the Run box, and then click OK.
   
   
3. Locate the HKEY_LOCAL_MACHINE registry folder.
   
   
4. Expand the HKEY_LOCAL_MACHINE folder until you locate the Software\Microsoft\Access\7.0\Wizards folder.
   
   
5. Click Wizards.
   
   
6. On the Edit Menu in the Registry Editor, click New, and then click String Value.
   
   
7. A new value is created in the right pane of the Registry window. Type "Loadonstartup" (without the quotation marks), and then press ENTER.
   
   
8. While the Loadonstartup key is highlighted, click the Edit menu, and click Modify. Type "rw" (without the quotation marks) for "read/write" as the data value, and then click OK.
   
   
9. Close the Registry Editor.