databasedev.co.uk - database solutions and downloads for microsoft access

Creating a Simple Microsoft Access Add-In - Pt.2

Now, if you have followed the previous article called "Creating a simple MS Access Add-In" you should have a database library called "addin2.mda" together with a complete understanding of what needs to be done in order to create a Menu add-in. In this article we will put all of that theory in practice and write an add-in for the Add-ins submenu of the Microsoft Access user interface.

Open up the addin2 database which by the way should look something like this:

Figure 1. The Add-Ins database, showing the System tables.

What we want to do is to create three menu items on the Add-in submenu, for lack of better names let’s call them:

• MenuItem One
• MenuItem Two
• MenuItem Three

Of course there are two sides to creating menu add-ins (as is the case with any other add-in for that matter), you have to create the menu items and also write the code to run them. Now for the sake of brevity, I did not write any complicated code for the three menu items. I simply wrote three functions which display a message box stating which of the three menu items a user has selected. Before we get to the code, let’s first put the items on the menu.

Open up the USysRegInfo system table and add the following information:

Subkey	Type	ValName	Value
HKEY_CURRENT_ACCESS_PROFILE \Menu Add-ins\&MenuItem One	0
HKEY_CURRENT_ACCESS_PROFILE \Menu Add-ins\&MenuItem One	1	Library	|ACCDIR\addin2.mda
HKEY_CURRENT_ACCESS_PROFILE \Menu Add-ins\&MenuItem One	1	Expression	=MenuItem1()
HKEY_CURRENT_ACCESS_PROFILE \Menu Add-ins\&MenuItem Two	0
HKEY_CURRENT_ACCESS_PROFILE \Menu Add-ins\&MenuItem Two	1	Library	|ACCDIR\addin2.mda
HKEY_CURRENT_ACCESS_PROFILE \Menu Add-ins\&MenuItem Two	1	Expression	=MenuItem2()
HKEY_CURRENT_ACCESS_PROFILE \Menu Add-ins\&MenuItem Three	0
HKEY_CURRENT_ACCESS_PROFILE \Menu Add-ins\&MenuItem Three	1	Library	|ACCDIR\addin2.mda
HKEY_CURRENT_ACCESS_PROFILE \Menu Add-ins\&MenuItem Three	1	Expression	=MenuItem3()

Once you’ve populated the USysRegInfo system table go to the tools menu and select Add-ins » Add-in Manager. You should now see a dialog window that looks like this:

Figure 2. The Add-In manager dialog box

Provided you’ve put your add-in database file in the correct path as discussed in the previous article, addin2 should already be listed. All you have to do is to click on the ‘Install’ button and you are ready to go. Once you’ve installed the add-in, your add-in submenu should look something like this:

Figure 3. Displaying the Add-In Submenu

At this stage, if you click on any of the newly added menu items, nothing will happen. This is because we have not added any code to run the menu items, so create a new module in the Visual Basic Editor and add the following code:

Option Compare Database

Public Function MenuItem1()
    MsgBox "Menuitem One Selected"
End Function

Public Function MenuItem2()
    MsgBox "Menuitem Two Selected"
End Function

Public Function MenuItem3()
    MsgBox "Menuitem Three Selected"
End Function

Basically, all that this code does is to display a message stating which of the three menu items the user selected. For example if I selected menu item number one, I will get the following message box:

Figure 4. Selecting one of the new menu items displays the message box

Finally a quick look at the registry confirms that the USysRegInfo system table has indeed written the three menuitems to the registry:

Figure 5. Viewing the Registry after installing the Add-In

If you look closely at Fig. 5, you will see that different types of add-ins are stored in different registry keys, so they need different paths in the USysRegInfo system table, which correspond to the different keys.

Fig 5 shows the Registry Editor open to the:

HKEY_LOCAL_MACHINE_SOFTWARE \Microsoft\Office\10.0\Access\Menu Add-Ins key (where USysRegInfo information is stored when the table specifies the HKEY_CURRENT_ACCESS_PROFILE key, Access user profiles aren’t in use, and Office XP is the current version).

Conclusion
Add-ins can be very useful and can be used for a variety of reasons. Microsoft Access makes it extremely easy to create them, but to build truly useful add-ins you will need some programming knowledge of Visual Basic and careful planning.

Download the sample Microsoft Access Add-Ins database (12kb)