Chapter 20

Applying Development Shortcuts


It is unlikely that you will choose to do the same task over and over again when you have the option to challenge yourself with new tasks. In most expert solution development, new challenges are commonplace and provide much of the enjoyment of the development work. Thus, as a solution developer you are inclined to eliminate or at least streamline the drudgery of repetitive tasks from your workflow. To minimize repetitive tasks, you create tools and systems that aid your work. This chapter focuses on techniques for creating development tools and systems in an Access environment:

How to create keyboard shortcuts to repeat common keystrokes

How to make the most of the component object model

How to improve the communication within a development team

How to create and install code libraries, wizards, and builders

Building Blocks

"Being a member the Microsoft Office product family has distinct marketing benefits for Access, but you may not realize that development benefits accrue as well. By replacing some of our home-grown components with common Office components-for example, File Open and Save dialogs, the setup engine, and command bars-the Access group saved one person-year of development time on Access 95, and more time than that on Access 97."

Tod Nielsen, General Manager, Microsoft Access Business Unit

I've been a tool builder since my early consulting days, constantly tinkering with ways to make the development process more automated. (The tools that I built for developing in the R:BASE database product were so engaging to its publisher that the company bought the tools from me and sold them as retail products.)

So, naturally, the first day that I saw Access I was already thinking about how to extend it, build tools for it, and create development shortcuts. I was pleased with the extensibility I discovered in the product.

You may not realize the wealth of tools that you have at your disposal when doing development in Access. Some of the helpful devices are Access features or extensions that are exposed for your immediate use, and others are building blocks from which you assemble your own tools and shortcuts.

Here are some of the Access features that you can employ to make your development work easier and quicker:

In this chapter, we'll explore these various options for building a more enjoyable and productive application development environment for yourself.

I've included the macros, toolbars, wizards, and other utilities described in this chapter on the CD in the file AES_SCUT.MDB, AES_LIB.MDA, and AES_WIZ.MDA. Before you waste time typing one of this chapter's examples into Access, check the CD-ROM first to see if I've already provided it for you.

Employing Techniques That Streamline Development

No craftsman or mechanic can build or fix things without good tools. Software mechanics are no different-they rely heavily on their tools as well. Two of the first tools that developers usually build are keyboard shortcuts and code libraries.

Defining Keyboard Shortcuts

While much of your Access development work is mouse-centric, keyboard shortcuts are still a part of the core user interaction when working with any Office product (imagine life without the Cut, Copy, and Paste shortcuts Ctrl+X, Ctrl+C, and Ctrl+V.)

Access provides the ability to define a single macro group called AutoKeys that contains your own special keyboard shortcut assignments. When the database containing the AutoKeys macros is opened, the keyboard is remapped by the commands in the macro group, and hotkeys are created that execute specific actions for you.

In Chapter 17, "Bulletproofing Your Application Interface," I discuss remapping the keyboard to provide a more restrictive and data-friendly interface for application users. My focus in this section, however, is on the needs of the developer, which are different from the user's needs.

Depending on both the requirements of the application, and the style of the developer, you issue certain commands more frequently than all others. In order to determine which keyboard shortcuts are useful for your specific style, you must observe your behavior and note which commands you rely heavily on.

Having listed the commands that are most important in your environment or development team, you must determine how to assign these commands to keystrokes. AutoKeys macros have significant limitations in the specific keystrokes that are allowed. Table 20.1 lists the keystroke combinations that you can map in AutoKeys. (You can also redefine the Insert and Delete keys, but this capability is marginally useful.)

Table 20.1 Keystroke Combinations That Can Be Remapped With AutoKeys

Combination

Syntax

A function key

{functionkey}

Ctrl plus a letter key

^letter

Ctrl plus a number key

^number

Ctrl plus a function key

^{functionkey}

Shift plus a function key

+{functionkey}

Each key combination in the preceding table that involves either the Ctrl or the Shift key can also be paired with both. For example, Ctrl+Shift+functionkey (^+{functionkey}) is a valid AutoKeys combination, as is the alternative syntax Shift+Ctrl+functionkey (+^{functionkey}). Both of these key combinations execute the same macro when pressed on the keyboard.

Table 20.2 shows the shortcut keys that are already in use by Access but meet the criteria for remapping as defined in the previous table. Use this table to determine if a keystroke combination that you want to remap is already in use, and what Access shortcut you will be replacing if you usurp the keystroke from the built-in functionality.

Access default shortcut keys that cannot be remapped are not shown here, such as Ctrl+; to insert the current date into a datasheet.

Table 20.2 Valid AutoKeys Combinations That Are Already Used By Access Itself

Keys

Action

Ctrl+A

Execute Edit, Select All

Ctrl+C

Execute Edit, Copy

Ctrl+F

Execute Edit, Find...

Ctrl+F2

Execute a builder

Ctrl+F2 (M)

Move focus to object combo box

Ctrl+F3 (M)

Find the next match for highlighted text

Ctrl+F4

Execute File, Close

Ctrl+F6

Cycle between open windows

Ctrl+F8 (D)

Toggle column move mode

Ctrl+F8 (M)

Execute Debug, Run To Cursor

Ctrl+F9 (M)

Execute Debug, Set Next Statement

Ctrl+G

Execute View, Debug Window

Ctrl+H

Execute Edit, Replace...

Ctrl+I (M)

Execute Edit, Quick Info

Ctrl+J (M)

Execute Edit, List Properties/Methods

Ctrl+L (M)

Execute View, Call Stack...

Ctrl+M (M)

Execute Edit, Indent

Ctrl+N

Execute File, New Database...

Ctrl+O

Execute File, Open Database...

Ctrl+P

Execute File, Print...

Ctrl+R (F)

Execute Edit, Select Form/Report

Ctrl+S

Execute File, Save

Ctrl+Shift+F2 (F)

Move focus to builder button

Ctrl+Shift+F2 (M)

Execute View, Last Position

Ctrl+Shift+F8 (M)

Execute Debug, Step Out

Ctrl+Shift+F9 (M)

Execute Debug, Clear All Breakpoints

Ctrl+Shift+F10

Display toolbar shortcut menu

Ctrl+Shift+I (M)

Execute Edit, Parameter Info

Ctrl+Shift+J (M)

Execute Edit, List Constants

Ctrl+Shift+M (M)

Execute Edit, Outdent

Ctrl+V

Execute Edit, Paste

Ctrl+W

Execute File, Close

Ctrl+X

Execute Edit, Cut

Ctrl+Y (M)

Cut the current code line to the Clipboard

Ctrl+Z

Execute Edit, Undo

F1

Execute Help, Microsoft Access Help

F2 (D)

Toggle insertion cursor or expand hyperlink

F2 (M)

Execute View, Object Browser

F3 (M)

Locate the next find or replace match

F4

Open a combo box

F5 (D)

Go to the record number box

F5 (F)

Switch to form view from design view

F5 (M)

Execute Run, Go/Continue

F6 (D)

Cycle forward through sections

F6 (F, M)

Switch window panes

F7

Execute Tools, Spelling...

F8 (D)

Cycle forward through extend modes

F8 (M)

Execute Debug, Step Into

F9 (D)

Refresh/recalculate controls or fields

F9 (M)

Execute Debug, Toggle Breakpoint

F10

Activate the menu bar

F11

Display the Database window

F12

Execute File, Save As/Export...

Shift+F1

Display the What's This help pointer

Shift+F2 (D)

Display the Zoom box

Shift+F2 (M)

Execute View, Definition

Shift+F3 (M)

Locate the previous find or replace match

Shift+F4 (D)

Locate the next find or replace match

Shift+F6 (D)

Cycle backward through sections

Shift+F8 (D)

Cycle backward through extend modes

Shift+F8 (M)

Execute Debug, Step Over

Shift+F9 (D)

Requery the underlying data

Shift+F9 (M)

Execute Debug, Quick Watch...

Shift+F10

Display current shortcut menu

Shift+F12

Execute File, Save

(D) Indicates shortcut keys that are used in datasheet view and form view

(F) Indicates shortcut keys that are used in form or report design view

(M) Indicates shortcut keys used in the Module window

Scanning the previous table gives you a comprehensive understanding of almost all of the built-in keyboard shortcuts in Access.

Figure 20.1 shows an AutoKeys macro with sample keyboard mapping entries.

Fig. 20.1

Developers can use an AutoKeys macro group to apply keyboard redefinition macros.

The following itemization provides some of my personal favorite AutoKeys combinations, in order to give you examples of using this feature and to supply you with a starting point for your planning. For each keystroke, I've listed the arguments to enter into the macro grid. The listing includes helpful key maps for each of the Ctrl key combinations that are not used by Access except for Ctrl+U. I've included a reminder to help you remember each keystroke if you choose to use it in your application development.

Macro Name

+{F4}

Action

RunCommand

Command

Properties

Macro Name

+{F7}

Action

RunCommand

Command

ViewCode

Macro Name

^B

Action

RunCommand

Command

SizeToFitForm

Action

RunCommand

Command

Save

Macro Name

^D

Action

SendKeys

Keystrokes

^{F2}{Down}{(}{Tab}

{Down}{(}{Enter}

Wait

No

Macro Name

^E

Action

MoveSize

Right

0"

Down

0"

Height

4"

Macro Name

^I

Action

RunCommand

Command

TabOrder

Macro Name

^J

Action

RunCommand

Command

SizeToGrid

Macro Name

^K

Action

RunCommand

Command

Duplicate

Macro Name

^O

Action

RunCommand

Command

Options

Macro Name

^Q

Action

RunCommand

Command

Run

Macro Name

^T

Action

ShowToolbar

Toolbar Name

tbrDevelopment

Show

Yes

With dozen of keys available for your redefinition, you can create a wealth of shortcuts to speed up your development work and build an environment optimized for your work habits.

In an AutoKeys keyboard shortcut macro, you can enter an expression into the Condition column of the macro to cause it to execute the actions only in a specific context. For example, a keystroke sequence that you only want to execute when a form or report has the focus would be restricted by use of the following condition:

 [Application].[CurrentObjectType] In (2,3)

This restriction limits the keyboard shortcut so that it only executes if the current object with focus is a form (CurrentObjectType = 2) or a report (CurrentObjectType = 3). ). The values for the CurrentObjectType property are shown in Table 20.3. Refer back to figure 20.1 to see an example of these restrictions in the macro design grid.

Table 20.3 Values for the CurrentObjectType Property When Used in AutoKeys

Value

Object

0

Table

1

Query

2

Form

3

Report

4

Macro

5

Module

Creating Development Utilities

The new command bar objects for user interface construction provide developers with a handy device for building tools. You can create small utility routines and attach them to the Access menu system or a custom toolbar for use during development.

The keyboard shortcuts in the previous section can be coded as standard macros rather than keyboard macros if you prefer to use them with the mouse rather than the keyboard. By giving each keyboard macro a name rather than a keyboard assignment in the Macro Name column of the design grid, the named macro can then be called as the action for a custom menu item or toolbar button.

Let's examine two common development tasks that are easily automated and assigned to a toolbar.

Whether you are testing a form or debugging an application, there are times when you want the code to provide you with instant feedback when a problem is encountered. Unlike your users, for whom you create elaborate error handlers and polite messages, as a developer you want to jump to an offending line of code in order to debug and fix its problem. Access facilitates this nicely with the Break on All Errors option setting.

I create two custom toolbar buttons to help me toggle this setting on and off. These toolbar buttons are shown on my custom development toolbar in Figure 20.2. Each of the two debugging buttons calls the routine shown in Listing 20.1, which is a Public function in a standard module. One button calls the function with the argument value True to enable the Break on All Errors setting, and one calls the same function and passes False. Figure 20.2 shows the Control Properties dialog for the button that sets the Break on All Errors setting to True; notice how the name and argument for the custom function go in the OnAction property setting.

This utility is quite useful when unit testing a piece of code, or when integration testing an application. When you see an anomalous event or receive an error alert, click the custom toolbar that sets Break on All Errors on. Repeat the event of interest and you will drop to the offending line of code, where you can debug the issue. When you are done fixing and testing the feature, click on the button that returns Break on All Errors to its off setting, and continue your testing.

Fig. 20.2

Command bars can contain custom toolbar buttons that call utility functions.

Listing 20.1 AES_SCut.Mdb-A Utility Routine to Toggle the Break on
All Errors Setting

Public Function lci_TbrBoAESet(blnState As Boolean)

' Purpose:   Turn Break on All Errors on or off

' Arguments: blnState:=True/False

  On Error Resume Next

  Application.SetOption "Break on All Errors", blnState

  Beep

  MsgBox "Break on All Errors has been set to " _

    & IIf(blnState, "True", "False"), vbInformation, "lci_TbrBoAESet"

End Function

The custom toolbar in Figure 20.2 shows several other handy tools that are useful during development. Some of the tools are not explained in the chapter text. You will want to explore the custom toolbar in the sample database file for this chapter to learn how the other tools work.

Refer back to Chapter 14, "Navigating in Forms and Applications," to learn how to create toolbars using the new command bar technology.

Another example of a handy development utility that I use often is a routine to quickly view the code for the currently active form or report. When developing or testing an application, I'm often in a mode that re-creates the user's view of the system and thus does not provide access to the built-in toolbars. When using such a system, there is no menu option or toolbar button available to jump to the code behind the currently executing form or report. Thus, a routine that performs such a task makes a perfect candidate for a toolbar button on my development toolbar, which is even available when I'm testing applications. My custom toolbar button calls the Public function lci_TbrViewCode() in its OnAction property argument. The function, shown in Listing 20.2, determines the state of the currently selected form or report (either open or simply highlighted in the database window) and issues the appropriate RunCommand method to toggle to design view and show the object's module.

Listing 20.2 AES_SCut.Mdb-A Utility Routine to View the Code for
the Current Object

Public Function lci_TbrViewCode()

' Purpose: Change form or report object into design view and show code

'          Issue from the Database window or on an open object

  Dim bytState As Byte

  Dim bytType  As Byte

  Dim strName  As String

  bytType = Application.CurrentObjectType

  strName = Application.CurrentObjectName

  If bytType = acForm Or bytType = acReport Then

    bytState = SysCmd(acSysCmdGetObjectState, bytType, strName)  ' Get state

    If bytState = acObjStateOpen Then  ' If open

      RunCommand acCmdDesignView  ' Toggle to design view

    End If

    RunCommand acCmdViewCode  ' Execute View Code

  End If

End Function

The code in this listing is assigned to the View Code button on my custom development toolbar (the second button from the left in Figure 20.2).

The Access toolbar also provides a tool to view code. The difference between the built-in toolbar button and my custom button is that the Access version is disabled when a form or report is running. My custom tool does not have this limitation, and is therefore more useful during debugging.

An additional opportunity to create development tools arises from the new Module object exposed in Access 97. A Module object is one module in an application's Modules collection. Access provides several methods and properties for a module that can be manipulated from code.

In Chapter 11, "Expert Approaches to VBA," I suggested placing an index at the top of each module during development that lists the procedures in the module. My indexes look similar to this:

' Index:

  ' lci_TbrBasIndex     - Recreate module index

  ' lci_TbrBoAESet      - Turn Break on All Errors on or off

  ' lci_TbrHourglassOff - Turn off the hourglass cursor

By writing code against the new Module object, you can create a utility that generates this index automatically. The code in Listing 20.3 shows you how. Running this code from a toolbar button with a module open creates an index string comment block at the top of the module that matches my style.

Listing 20.3 AES_SCut.Mdb-A Utility Routine to Create a Module Index
Comment

Public Function lci_TbrBasIndex()

' Purpose: Recreate module index

'          A module must have focus when this is called

  Dim blnRet       As Boolean  ' Result of Find

  Dim bytType      As Byte     ' Current object type

  Dim lngEndCol    As Long     ' Find method argument

  Dim lngEndLine   As Long     ' Find method argument

  Dim lngProcKind  As Long     ' ProcOfLine method argument

  Dim lngStartCol  As Long     ' Find method argument

  Dim lngStartLine As Long     ' Find method argument

  Dim bas          As Module   ' The module to search

  Dim strIndex     As String   ' The index

  bytType = Application.CurrentObjectType  ' Object with focus

  If bytType <> acModule Then  ' Object with focus is not a module

    GoTo lci_TbrBasIndex_Exit

  End If

  ' Get a pointer to the current module

  Set bas = Modules(Application.CurrentObjectName)

  strIndex = "' Index:" & vbCrLf

  ' Loop through the module and find each target string

  ' The find target is assembled in pieces so it is not found

  Do

    lngEndLine = bas.CountOfLines  ' Search to the end

    blnRet = bas.Find("' " & "Purpose:", lngStartLine, lngStartCol _

      , lngEndLine, lngEndCol, True, True)

    If blnRet Then  ' The string was found

      ' Add the found line's procedure name and purpose to index string

      strIndex = strIndex & "  ' " _

        & bas.ProcOfLine(lngStartLine, lngProcKind) & " - " _

        & Trim(Mid(bas.Lines(lngStartLine, 1), 11)) & vbCrLf

      Else

        Exit Do

    End If

    ' Increment the start of next find

    lngStartLine = lngStartLine + 1

  Loop

  ' Insert the index at the top of the module

  bas.InsertLines 1, strIndex

End Function

The listing introduces you to some of the new methods and properties of the Module object; refer back to the listing to see each of these properties and methods in action:

Add-ins you create that manipulate modules can be run as toolbar buttons, as in this example, or as wizards or expression builders, as explained later in this chapter.

Using Component Objects

The greater the percentage of each day that you spend in Access application development, the more highly motivated you are to reuse your code and objects wherever possible. Coding the same routine twice or building the same form over and over does not increase job satisfaction for a majority of developers. Instead, you will attempt to create "component objects" (easily-reusable items).

As you create application objects, constantly ask yourself this question: Under what circumstances would I or another developer use this object again? Many objects and procedures that have a very narrowly defined purpose in an application can be easily expanded to provide broader functionality. I have found that a very specific object or routine can usually be made generic with an extra effort of only 10 to 30 percent.

For example, assume that you must create a routine to check specific fields on a form to ensure that they are not Null. The routine inspects specific form controls by name, as in this code:

If IsNull(Me!txtCustName) Then...

When designing this routine, it occurs to you that almost every form you will create for this and other applications will need a similar routine. You determine that a standard, generic routine that can be used on any form would solve not only the current problem, but future similar problems. You create a module-level collection of controls mcolNonNull at form load, and the improved validation routine simply checks all of the controls in the collection, without need of their names:

For Each ectl In mcolNonNull

  If IsNull(ectl.Value) Then...

Refer back to Chapter 12, "Understanding Form Structures and Coding," if you are not familiar with the use of control collections.

Let's assume that the original (hard-coded) approach would have taken you 20 minutes to write. It is unlikely that the new routine, employing a different metaphor but not more lines of code, would take you more than 30 minutes to create instead. This provides the benefit of a 20 minute time savings the very next time the routine is used, for only a 10 minute extra investment up front.

As you create routines like this (and component objects), you must store the master copy of each object in a central location from which all developers can extract the reusable objects. I call such a central repository an "object store". An object store of Access objects can be built as an Access database.

One of the keys to success with reusable objects is to enable them with the flexibility to support various arguments or application statuses. The next few sections provide a few examples of this.

Reusing Tables and Queries

Data tables are one of the most difficult objects to reuse, because each application has unique data collection needs. Nevertheless, lookup tables, system tables, and other non-transaction tables can frequently be moved from one application to the next with little or no change.

The simplest example of this point is provided by the ubiquitous state table. Lookup tables of states, provinces, cantons, districts, or similar geographic fixtures are quite static, and can be used in different databases. Because these lists are non-variable, a single table object with this type of data can often be used throughout an entire company, by placing it in the object store and letting developers pull it out from there into new databases as needed. Alternately, one central copy of such a table can be kept in an object store and all applications in the company can link to the shared table.

Component tables of data that is less static provide a slightly larger problem. It is not possible to simply place the master of such a table in an object store, make its availability known to potential users, and then forget about it. Instead, an object owner must be designated to manage the table's data to keep it current.

Such an object owner has two primary responsibilities:

Consider the example of telephone area codes, which change several times a year. Without an object owner, an area code table template in an object store would quickly grow "stale" and inaccurate. To be useful to the development team, this kind of component object must be kept current and the team must be notified when the master version of the object changes. Notification that the master has changed allows a developer to update any copies of the master object already in existing applications.

Because queries reflect table data and not their own, component queries do not suffer from a lack of timeliness. Instead, the key challenge with reusable queries is understanding what they do and why they are valuable.

For reusable queries, you should label them in such a fashion that their dependencies are easily identified and thus they always travel with the table or tables on which they are based. A good naming convention, system documentation, and strategy for using the query's Description property will help with this process.

Reusing Forms and Reports

Obviously, if a component table for states is placed in the object store, then the table's primary query, its entry/edit form, and its reports should be placed there along with it. At the point where you have five or ten component objects that depend on each other like this, your object store becomes complex enough that it cries out for a documentation or tool-based solution to help users dissect its dependencies.

Thus, the biggest challenge when reusing forms and reports is to help developers understand which tables, queries, and code objects are also required in order for the form or report to function when copied into an application. In other words, developers require an easy way to determine which object they want from the store, and to extract that object and its related components from the store. Here are a few suggestions for managing this challenge:

Carrying this technique a step further, instead of using comments you can create a dummy Sub procedure in each form or report object that includes all of the code required to import the related objects from the store. Each line in the procedure is simply a TransferDatabase method that imports one component from the store. When a form or report object is imported from the store into an application, the developer opens up the object's module, copies the import procedure to the Clipboard, pastes it into a blank new module, and presses F5 to run the code.The second challenge when reusing form and report object types involves consistency. Pulling two or three component forms or reports from an object store into an application, only to discover that they were built using different interface metaphors and must be reworked does not maximize the value of reusable objects.

Before you create an object store for forms or reports, your team should define interface standards that will be used for all objects placed in the store. This ensures a consistency of "look and feel," as well as coding style.

Reusing Code

Code dependencies can be quite complex. Assume that you pull ProcedureA() and its module from the object store, only to find that it calls and requires ProcedureB(), which is in a different module. After pulling the module for ProcedureB() into your application, you discover that it calls yet another function ProcedureC() in a different module, and so on. Code dependency trees like this can become quite convoluted and thus are difficult to diagram and manage.

You can select one of three methodologies to help your team manage the situation I've just described:

You may have noticed in my code samples that I create a comment at the top of some routines labeled "Calls:" where I list the other procedures that the current procedure invokes. This gives us a quick idea of specific dependencies as we work with a code routine.

This technique is only valuable if the comment is kept accurate, and is not a good surrogate for comprehensive dependency documentation.

Tips for creating generic, reusable code procedures are found in the section "Building Library Routines" later in this chapter.

Improving Team Development

Access developers who also work in environments like Visual C++ or Visual Basic have expressed frustration year after year with the lack of internal mechanisms to do version control on Access objects. Developers have had to select between a few add-on products that attempt to assist in this area, or create their own tools or policies. However, the wait is over Visual SourceSafe and Access now communicate with each other to allow version control of Access application objects.

There are two primary issues of importance in team development of Access applications. I'll briefly describe each one.

Communicating Application Changes

Developers need to advise each other when a new object is added to an application or is completed. There is no formal notification process for this within Access.

As a result, object concurrency problems may arise by virtue of the fact that the Database window does not refresh dynamically in a multi-user development environment. Without background refreshes, a developer viewing the list of objects in the window is not guaranteed to see the most current list of objects, or the current values for the objects' properties. This can produce dangerous situations.

For example, suppose Wade has been viewing the list of tables in the Database window for several minutes and has not forced the list to refresh. He observes that nobody has created the table tblCust required by the application yet, and spends several minutes creating the table.

In actual fact, Curt created the table ten minutes ago, but without a refresh of the Database window, Wade did not see it in his table object list. When Wade tries to save the new table, Access will detect that the name conflicts with an existing object and either Wade's or Curt's time will have been wasted.

Fortunately, developers can be made aware of this situation and learn to avoid it. Access refreshes the Database window with changes to the database made by another user (additions, deletions, and object renaming) when you do the following:

The one object activity that does not refresh the Database window is opening an object in its default view. Thus, developers must be made aware that actions such as working in table or query datasheets, viewing forms or reports, or reviewing code do not cause a refresh of the Database window when completed.

If you work in a multi-developer environment, viewing the latest application object changes is important. You can create a utility routine that helps keep the Database window current by building a form that has an OnTimer event procedure with this line of code:

Application.RefreshDatabaseWindow

Set the form's TimerInterval property value to 5000 (for five seconds, or whatever interval your group deems important). Teach each developer to keep the utility form open at all times (you can hide the form so it doesn't clutter the workspace). At each regular interval when the timer fires, the form event will run and refresh the current object list in the Database window, ensuring that each developer sees additions and deletions instigated by the other.

While creating or enhancing an application, developers should utilize good habits like regularly forcing a refresh to see the most current objects. However, simply seeing the latest objects does not provide an indication of what they do or how they have been changed by another developer. Team development procedures must include regular project status meetings or an e-mail policy whereby developers advise each other of critical object additions and enhancements.

You can create a table in the application database to act as a running development log. We use the following table structure for zstblLog:

LoggedAtDate/Time
LoggedByText 10
LoggedObjectsText 255
LoggedActionsMemo

We create a keyboard shortcut Ctrl+L in AutoKeys to open this table from anywhere in the database and jump to the bottom, where we log each development activity in a new record there. This provides an ongoing audit of work done, for the benefit of both the client and the remainder of the development team. In a multi-developer environment, you should be able to review the entire history of an object by simply reading the development notes for the object added by other developers to this log table.

Controlling Object Versions

Development teams require a mechanism for ensuring that only one member of the team "owns" a specific object at a given time. This situation is known as "checkin/checkout," and is facilitated by products commonly called "version control software."

Version control software, such as Visual SourceSafe, rely on the ability to compare (or "diff") files, and on the ability to place the files in a central repository. Because Access objects are somewhat captive to their host database file, and because of the unique binary format in which they are stored, commercial version control software has not worked with Access applications in the past. That left developers with several choices, all less than perfect. Even with the addition of code control to Access, these other techniques are still available to you and my prove useful.

The first control technique is to assign objects directly to developers as a matter of policy. Thus if Sid is working on reporting, he would need to "own" all of an application's reports, and potentially the code that drives the reports and the objects on which they are based as well. Other developers needing to modify any objects related to Sid's work on reports would need to coordinate their efforts with him.

While this situation requires a significant amount of planning and ongoing communication, for many development teams this is the only affordable approach to multi-person application development.

A second approach to team development is to checkout the entire application to a single developer at one time. Extending the earlier example, at the point in the development cycle where Sid needs to work on reports, he would "own" the database and work in the application in Exclusive mode to ensure that no other developers have access to it while he adds his pieces.

This model is actually quite effective for smaller projects (several hundred hours or less), subject to scheduling considerations:

A third approach is to create a "poor-man's" version control system, which allows developers to flag objects with ownership without providing true version control.

The easiest way to implement a simple ownership strategy in Access is to use the custom Description property available on each object. This property can be set from the user interface in the Database window by selecting Properties from the View menu, or from the Properties option on the shortcut menu for a database object. Each developer on the team must set the Description property to his or her name or initials before beginning development work on an object, then remove the property value when work on that object is finished.

All developers on the team must select Details from the View menu in order to show the Description property setting in the Database window. With this scheme in place, a developer follows these steps to "checkout" an object:

  1. Refresh the contents of the Database window.
  2. Note the Description property of the desired object or objects.
  3. If the objects are not currently checked-out (the description is empty), set the Description property for each desired object and then begin development.

Figure 20.3 shows a Database window with this strategy in place.

Fig. 20.3

The Description property shown in this Database window is used to hold checkout information for multi-developer work.

This tactic requires discipline on the part of each developer or it will fail. Remembering to check objects out and in is a primary responsibility of each developer, and one forgetful team member can create a mess for all members.

You can modify this technique by building a simple add-in that all developers can use to provide a list of database objects, select and checkout a specific object, check objects back in, and test to see if any items remain checked-out to the current user before closing the database for the day.

Another option for version control is to create your own home-grown automated control application. Such a tool must be written in Access, preferably as an add-in, in order to effectively manage Access objects. This methodology works as follows:

  1. An Access application database under development has a "master" database, which serves as the repository for current copies of all its objects.
  2. The application also has a "working" database (a copy) for each developer working on it.
  3. When a developer begins work for the day, he or she runs the custom add-in to checkout the needed objects into the working database. This process makes a copy of the object from the master database into the working database, and also flags the object in the master database as owned by the developer. This process prevents any other developers from checking-out a copy until the modified object is returned to the master.
  4. During the process of copying the desired object into the working database, the add-in also fully synchronizes the working database with the master by updating it with any objects that are newer in the master than in the copy. This update is done without checking-out the updates to the current developer. This process allows developers the benefit of receiving updates of work done by other members of the team.
  5. When development is complete on an object, the developer checks it back in to the master.

As with the other version control schemes discussed in this topic, this one requires discipline on the part of each participating developer, and introduces an extra time drain on the development process.

Access' new replication capabilities can be used to distribute new objects to replica databases, but shotgunning objects out to developers' working copies of a project does not resolve the version control issues discussed here.

The newest version control technique for Access is the integration of Access 97 with Visual SourceSafe (VSS) to manage the versions of Access objects.

Version control integration between Access and VSS is enabled by purchasing the Office Developer Edition (ODE) product. However, the ODE only includes the Access-specific tool for VSS integration, not VSS itself. You must purchase VSS version 4.0 or higher separately, or purchase Visual Basic Enterprise Edition which includes it.

In a nutshell, source code control in Access 97 using VSS allows you to:

The previous version control techniques I've discussed in this section are developer-centric, and rely on the initiative of the development team to enforce the controls. The VSS model is more restrictive. A database must be added to source code control, then developers for that database must be added. Past that point, Access integrates with VSS for object version control while you work in the Database window. For example, when you open an object in design view in a version-controlled database, Access either asks you if you would like to checkout the object or opens the object's design read-only if another developer currently has the object checked out.

When a non-module object is checked out of the repository for the benefit of a developer, no other person can check it out. However, multiple developers can check out the same module. The different changes made by different developers at the same time are merged as each developer checks a module back in. This can make VBA development on a team somewhat more complex than under your current model.

In order to facilitate integration with VSS, Access must save a Database window object as a text definition file. The file is what actually gets checked in to VSS. Upon checkout, Access retrieves the text definition and recreates the object in your database file. For non-Database window objects like command bars, database properties, and relationships, Access sends these items to VSS as binary information. All of these operations take time and will noticeably impact your workflow as you create your solutions.

Creating Wizards, Builders, and Libraries

Zooming In

"When we were building Access 1.0, we were very concerned that users and developers would not be able to figure out how to extend Access using Access itself. So, we set ourselves the task of trying to find a simple example of how to enhance the product without C code, in order to prove to ourselves and our users that it could be done.

"We finally conceived of the Zoom box, and wired it into the Shift+F2 key combination. The zoom dialog was built as an Access form, and while it was a simple example, we were undeniably pleased that we had shown that Access was extensible."

Tod Nielsen, General Manager, Microsoft Access Business Unit

For developers, one of the hallmarks of a "good" development tool is whether or not it can be extended to include non-native functionality. Fortunately, Access passes the test of a "good" tool by allowing you to build your own extensions using Access and VBA, and to hook the extensions into the retail product quite easily.

Extensions to Access are generically call "add-ins." There are four subsets of the classification add-in, and the lines between them are a bit fuzzy:

I will use the terms "add-in" and "extension" to refer generically to the four types of tools in the previous list.

Access 97 does not present significant opportunities for creating new types of custom add-ins that you could not construct in prior versions, because Access has supported add-ins well for several releases. The most notable changes from Access 95 are the empowerment of module code builders through a fix to an InsertText method defect, and the exposure of the Module object and the code lines within it.

Access 95 and 97 each defined a slightly different model for connecting extensions to Access than their predecessor. The detailed steps for creating, installing, and using the specific types of add-ins are itemized in the topics that follow. Note, however, that there are several concepts that apply to all add-ins:

Each of these points will be clarified as we work through the actual steps to create add-ins.

Creating Code Libraries

It is easy to get into the mindset that the list of intrinsic functions and constants in Access and VBA is fixed. Developers get familiar with the limitations of Access early on in their interaction with it, and often develop a habit of writing similar routines over and over to perform tasks that are not built-in to the product. Visit any development team using Access and review the code of each developer, and you will find the same task's code written again and again in different applications by different coders.

You can minimize this wasteful overlap of effort by creating a standard library of code routines for your entire development team. The library should contain procedures that solve problems commonly experienced in almost every application.

Some of the issues addressed by library routines are generic to all Access users (checking to see if a form is loaded is one example of a routine that every developer needs). Other library routines evolve to satisfy needs specific to a user community (for example, a company heavily involved in telemarketing may get great benefit from a central routine that returns the name of the state that contains a passed-in area code value).

Locating and Versioning a Library

A code library can be applied to your applications in two ways:

The first of these two options is generally easier to implement, because the embedded library code modules travel with their application. Installation and debugging are simpler in this model, because application code and library code are in the same database file.

The ongoing enhancement of library routines during application development is also facilitated by this setup, because a library is "frozen" (or "versioned") in each specific application that it ships with. The master library can then be modified for the benefit of future applications without impacting its existing users, because the changes are not retroactively applied to library code in applications already in use.

I've seen environments where developers use library code as a template only. The library code is copied into a project and then can be freely modified for that application; no attempt is made to update the master library with the modifications.

By contrast, locating library routines in a separate library database file from the application code creates and solves a different set of problems than arises from the embedding of library code into applications. The issues that must be dealt with are best illustrated by an example.

Consider a library routine lib_FrmIsLoaded(), stored in a central library database AES_Lib.Mda on a shared server, and referenced by every application in the company. This arrangement provides you with the following opportunities and challenges:

It should be apparent to you that there is no single "correct" way to deploy a set of library routines. You will have to weigh the factors discussed here and adopt the best model for your organization.

Referencing a Library

Code in an add-in library database is not available to an open database unless the library database has been referenced, by creating a link through the References dialog. Once a library database is properly referenced, routines that are Public in it can be run by referencing applications. Figure 20.4 shows the References dialog with a reference for the sample library file provided with this chapter.

You can only address routines in a referenced database that are stored in standard modules and are declared as Public. You cannot initiate routines that are stored in a library database's class modules or forms from code outside the library.

Fig. 20.4

References to library databases are created via the Access References dialog.

Here are the steps to manually create a reference:

  1. Open any module.
  2. Choose Tools, References....
  3. Select the reference from the Available References list in the References dialog, or click Browse... to locate a file to be referenced.
  4. Order the new reference with respect to the other references, if the order is important. How Access uses the order of references in this dialog is discussed later in this topic.

References to a library database become invalid when a user moves the library file. References are stored in the referencing database, but Access does not detect the move of a referenced file and update the links accordingly. The user must manually recreate a reference to a relocated library file, or your code must do so.

There are several ways to call a routine in a referenced library database. Because Public variables and routines in a library database are available to the current application database as if they were local, you can call a library routine in the same fashion as with a routine in the current database:

blnRet = lib_FrmIsLoaded("frmCust")

You can also use the Run method to call a referenced routine. With this method, you can optionally specify the project name of the database in which the routine resides. Thus the three syntax examples in Listing 20.4 are equivalent.

Listing 20.4 Using Run to Invoke a Library Routine

' The standard version

Application.Run "lib_FrmIsLoaded", "frmCust"

' You can include the project name

Application.Run "AES_Lib.lib_FrmIsLoaded", "frmCust"

' The Application object is optional

Run "lib_FrmIsLoaded", "frmCust"

An Access 97 database file's project name is set on the Advanced tab of the Options dialog. By default, it is the same as the name of the database file (without the .MDB extension), but you can set it to a different string if you prefer. A file's project name is displayed in the Object Browser and the References dialog.

In the previous example, AES_Lib is the project name assigned to library database. If I was using the full database name as the project name, the prior line of code would need to look like this:

Application.Run "AES_Lib.Mdb.lib_FrmIsLoaded", "frmCust"

Note these issues that differentiate a standard call to a library function from the use of the Run method:

You should structure your library code carefully to ensure that it executes efficiently regardless of which of these methods is used to initiate routines in it. Where practical, group your library routines together so that procedures that refer to each other are in the same module.

You cannot qualify a procedure name with a project name when not using Run, as in this example:

blnRet = AES_Lib.lib_FrmIsLoaded("frmCust")  ' This fails

blnRet = lib_basSample.lib_FrmIsLoaded("frmCust")

blnRet = lib_FrmIsLoaded(rblnBeep:=True, rstrForm:="frmCust")

Application.Run "lib_FrmIsLoaded", rblnBeep:=True, rstrForm:="frmCust"

Library routines do not load into a global name space, as in previous versions of Access, but instead are loaded "on demand". This allows you to have routines with the same names in different application and library databases. If a routine exists in several places, Access uses this hierarchy to determine which one to use:

  1. A routine in the local database with the given name will be run if found.
  2. If the routine is not local, Access will check the referenced databases for the named routine in the order of the references in the References dialog, and run the first routine it finds with the target name.
  3. Alternately, Access will run a named non-local routine in the location specified if the Run method is used with the project name qualifier, or if the function call includes a module name prefix as described earlier in this topic.

Users of an application that is distributed with the run-time executable are not able to create references in the application database because they do not have access to the Tools menu. Consequently, before you distribute an application database that requires a library database, you must create the reference to the library in the application database. Because the reference includes a drive and path designation, the library must be located in the referenced directory or the reference search path for each user of the application (the search model is explained following the next code listing).

Alternately, your application must include a routine that loads the File Open dialog for the user or calls the new FileSearch object to locate the referenced file (see Chapter 11, "Expert Approaches to VBA," for an example of code that uses the FileSearch object). Once located, the original reference can be updated by updating the new References collection information, as shown in Listing 20.5.

Listing 20.5 AES_SCut.Mdb-Using the Remove and AddFromFile Methods
to Update a Reference

Private Sub cmdUpdate_Click()

' Purpose: Refresh a reference

  Dim eref As Reference

  ' Find and remove the existing reference

  For Each eref In References

    If eref.Name = "AES_Lib" Then

      References.Remove eref

      Exit For

    End If

  Next

  ' Update the reference

  References.AddFromFile (Me!txtRef)

  Beep

  MsgBox "Reference to " & Me!txtRef & " has been set." _

    , vbOKOnly, "cmdUpdate_Click"

End Sub

There are a few special noteworthy characteristics of the Access reference model that affect library code. For the purpose of explaining these nuances, assume that your working database is AES_SCut.Mdb, which contains a reference to a library database C:\Program Files\Microsoft Office\Office\AES_Lib.Mda:

The upshot of this reference-updating behavior is that you will be most successful distributing library databases to users if you mandate that they place the library in the same directory as their MSACCESS.EXE file, which is searched by the reference fixup logic.

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\RefLibPaths

Run "AES_Lib.Mda.FocusExample 

Fig. 20.5

The LoadOnStartup Registry key allows your application to find a library without a reference.

See the section "Installing Add-ins Via Self-Registration" later in this chapter for more information on creating Registry keys.

Building Library Routines

When creating code that will be located in a library database and shared repeatedly, or copied into an application database from a library template, consider these points while you are coding:

Listing 20.6 Code in a Library Database That References Both the Library
Database and the Calling Database

Dim dbsCode As Database

Dim dbsCurr As Database

Dim rstCode As Recordset

Dim rstCurr As Recordset

Set dbsCode = CodeDb

Set dbsCurr = CurrentDb

Set rstCode = dbsCode.OpenRecordset("MSysObjects")

rstCode.MoveLast

Debug.Print "Objects in code database: " & rstCode.RecordCount

Set rstCurr = dbsCurr.OpenRecordset("MSysObjects")

rstCurr.MoveLast

Debug.Print "Objects in current database: " & rstCurr.RecordCount

intStat = DCount("*", "MSysObjects")

MsgBox "Objects in current database: " & CStr(intStat)

Because versions of Access in different languages may not have the same keystrokes and strings that are in the language used for your library routines, you should avoid the use of the SendKeys statement in any application that may be used in other countries.

Defining the composition of a library begins with determining the types of tasks that are performed most frequently by a development team, and creating a list of candidate procedures to write. Additionally, you can analyze current code that exists in your organization to see what kinds of tasks are coded repeatedly.

If you do not have an existing code base, or have not been developing Access applications for very long, you can select between these two directions:

Having listed the types of routines that will appear in the library, the next step is to determine what the scope of your library will be. If you work exclusively in Access, you can write functions with no thought to portability to other platforms. If you work in other VBA-aware applications, as is quite common, you may consider writing library routines that can be used in multiple environments as much as is practical. Some VBA routines written in Access will work in other platforms with no changes; other routines will require minor platform-specific variations.

Next, standardize how your library routines will be coded, and by whom. It is helpful to decide how libraries will be organized and managed before getting started-changing them after they have been implemented in one or more user application is troublesome. Also, it may be difficult to have multiple developers working in the same library simultaneously-changes can be lost and objectives can clash, so you may wish to designate a librarian, someone who either codes all the library processes or monitors who is coding each piece, to ensure better code control.

Here are some development areas that you may want to consider for standardization as you create library code:

Creating team development standards for library code is certainly optional (because Access doesn't care), but doing so feeds an important standardization goal that should drive all of your Access development work, including the creation of libraries-you should be able to sit down with code created by anyone on your team and navigate comfortably through the code as if you had written it yourself.

Creating Builders and Wizards

The primary difference between a wizard and other types of add-ins and tools you will buy or create is that wizards enforce a linear, step-by-step approach to a single task. Commonly, your wizards will follow a prescribed interface metaphor similar to that found in the Access wizards.

A builder is a simplified wizard. While the distinction between the two tools is somewhat arbitrary, a builder is usually distinguished from a wizard by virtue of its single-screen, more utilitarian layout.

Wizards provide an interesting terminology problem in that they can also be launched from a build button (and thus are also a type of builder) and from the Tools menu (acting as an add-in.) Within your organization, you will have to decide which of these terms you will apply to a specific wizard functioning in one of these additional capacities.

The invocation process for wizards in Access that you are most familiar with is from the New Object dialog displayed when you choose to create a new Access database object. As an example, the process of creating a new form displays the dialog shown in Figure 20.6. The list in this dialog includes six wizards.

Fig. 20.6

The New Form dialog displays six form wizards that are included with Access.

This list is displayed by Access based on settings in the Windows Registry. Therefore, you can add your own custom wizards to the list by creating additional Registry entries. Figure 20.7 shows the Registry entries for the form wizards shown in Figure 20.6 previously.

Fig. 20.7

The default form wizards are listed and launched via these Registry entries.

Designing Builders and Wizards

Creating builder and wizard-style add-ins for Access is not quite as simple as just producing a quick form with some code behind it. Two important concepts come into play when creating these types of tools:

In general, wizards usually operate on objects, while builders operate on settings. This rule-of-thumb is only a guideline, and you can mingle wizards and builders as the needs of your users dictate. Using these general guidelines, and modeling your builders and wizards after the tools shipped with Access, you will derive guidelines for add-in development that resemble these:

Figure 20.8 shows a standard wizard form and notes the components of the form.

Fig. 20.8

Wizard forms follow this commonly-accepted layout.

There are two common technical metaphors for structuring a wizard form. The first design (the one often employed in Microsoft's wizards) places a Subform control on a parent form. As the user navigates through the wizard, the parent changes the SourceObject property of the Subform control in order to display another wizard "page". In this model, there is a saved form loaded as a subform for each page of the wizard, and the parent form provides only the wizard's "shell", or container with the navigation code and buttons.

The second wizard design model utilizes multiple form pages to simulate the different wizard screens. Thus, clicking the Next button on the wizard form simply displays the next page moving downward through one form.Table 20.3 summarizes the strengths and weaknesses of the two different wizard layouts.

Table 20.3 A Comparison of a Wizard Using Subforms With a Wizard Using Pagination

Area

Multiple Subforms

Multiple Pages

Loading

Faster than the multi-page model for complex wizards

Faster than the subform model for simple wizards

Navigation

Offers unlimited number of "pages"

Pages limited to the size of an Access form

Persistence

Requires that the parent form's code has a structure to save the user's selections before a new subform is loaded

User selections are retained on the pages where they are entered

Componentization

A single page (subform) can be used in more than one wizard form

Pages are captive to the parent form

Data

Each subform contains its own code, properties, and record source

Does not provide for multiple record sources, or continuous or datasheet layouts

A third approach to wizards pagination is to use the new Tab control as a container for controls, and to bring a specific tab to the top as a means of going to a "page". This method is a variation on the multi-page model described above and works well, with the drawback that it increases form load time because all of the wizard's controls and code are in a single form.

Navigation in a wizard from can be slightly challenging, primarily because you must keep track of which "pages" are allowed and disallowed in the current context. In other words, for a particular task or based on a particular user selection, it may or may not be appropriate to display a particular page of the wizard in the current context. The routines driving the Back and Next button navigation must account for this situation.

After you've created a layout and chosen a navigation model for your wizard or builder, you must engineer the code. As I inferred earlier, your wizard code should always utilize advanced error handling and provide the user with high-quality feedback and a recovery mechanism in the event of a builder or wizard error situation or failure.

Another key concept driving add-in coding is to empower your add-ins with enough flexibility to operate on a variety of objects or to respond to various inputs. You can economize on your add-in code and use one tool for more than one task with a little extra planning and coding effort.

For example, a wizard to create a new Text Box control can be hard-coded to work against a form object only, or it can be written to be applicable to both forms and reports with only about 20% more effort.

The steps for creating a builder or wizard are very similar to the steps for creating any Access application:

  1. Describe the objective. Because a builder or wizard is oriented toward accomplishing a specific task, you must narrowly define the task before starting. Unlike an application, which has a flexible and extensible layout, a builder or wizard gives you only a few screens in which to affect your result, and may be difficult to change once widely deployed.
  2. Work with your users to define the audience that will use the tool, their primary needs, the unique terminology they use in their job roles, and the level of aptitude and experience they have. Weigh each of these factors as you create the prompts, graphics, and layouts for the add-in.
  3. Describe the inputs, outputs, and objects. You must determine what information will be passed to the add-in, and what information will be entered by the user. In the case of a property builder, for example, the inputs are defined by the fixed structure of the entry function (see the section "Invoking Builders and Wizards" later in this chapter). For a more powerful tool, like a New Account Wizard, your tool may pull information from application variables (such as the ID of the current user) and information from the currently open form (a customer number, for example).
  4. Next, define the value or object returned by the tool back to the current Access session. A control or property builder or wizard returns a pre-defined value by virtue of its calling structure, but an object wizard can create one or multiple objects, add records to tables, create a new database, or perform any other task within the realm of VBA's and the DAO's capabilities.
  5. List what to collect and how to collect it. In order to economize on screen space, you must determine the builder or wizard's prompts with an eye toward simplicity and size. You must also determine how to supply data to controls and prompts. Because a wizard can draw information from both its own library database and the current application database, you have two sources for providing records to combo box controls, filling lists, and so forth.
  6. Create a prototype target object. For a new object wizard, it is convenient to work backwards from the desired result. Define the minimum set of properties and attributes of a new object to be created by the wizard, and engineer the wizard to collect enough information to achieve that objective.
  7. Create the database structure. All of your company's builders and wizards can reside together in a single library database (MDA file). Alternately, you can create multiple libraries, grouping tools by functionality or target audience. Before you begin development, you must define where each tool will reside and what components it will share with other tools.
  8. Build the required objects. An add-in will be built from some or all of the components on the following list:

Forms used with add-ins are generally dialog-style, with a dialog border, no scroll bars or record selectors, standard Windows color schemes, and the OK/Cancel button combination (for builders) or the Cancel/Back/Next/Finish button combination (common to wizards).

  1. Refine the user interface. Some application processes (administrative tasks provide the best example) can make an assumption about the skill level of the people that will execute them. In contrast, most builders and wizards are "for the masses", and must be written to your lowest common denominator user (the one with the lowest skill level). When you have prototyped the user interface for your add-in, have your lesser-skilled users review the interface and see if they find the tool self-explanatory, simple to use, and difficult to get lost in.
  2. Write the code. When creating add-ins, don't use macros-you must write VBA code in order to create appropriate error-handling mechanisms. The principles defined in the "Building Library Routines" section of this chapter for creating library code are applicable when authoring builder and wizard code as well.
  3. Create an installation procedure. In order to easily deploy your add-in, you must create a setup procedure for the user to execute. See the section "Installing Add-ins Via Self-Registration" later in this chapter for information on installing add-ins.
  4. Create documentation and define a support mechanism. As with any software application, the users of add-ins will benefit from some combination of What's This help, a standard help file, and printed documentation. Additionally, tool users in a large organization should be able to quickly determine how to receive technical support on the tool.
  5. Test, Test, Test. Before wide deployment of your add-in, test it under a variety of inputs, user aptitudes, and hardware environments.

You can modify the existing wizards shipped with Access to customize them for your specific user base. However, the wizard code shipped with Access is secured, so you must download the unsecured version of the code from CompuServe or other public forums where Microsoft has placed it. (In past versions of Access, the unlocked wizard code has been posted publicly, but at the time of this writing it was not clear whether the Access 97 wizard code would be posted unsecured.)

Be aware that the unsecured wizard libraries do not contain the performance optimizations made to the secured version. If you replace a secured version with its unsecured copy, your Access performance will suffer. Also, you cannot redistribute modified versions of Microsoft's wizard or library code with retail add-ins or applications.

Code in a builder or wizard must be aware of the fact that two databases (the add-in's library and the user's database) are active at once. Refer to the information in "Building Library Routines" earlier in this chapter for further explanation of this issue.

Once the process of creating and installing add-ins is demystified for them, companies with large numbers of Access licenses find that the creation of a small, core set of tools and utilities dramatically improves the productivity of its users and developers.

Installing Builders and Wizards

Adding your own wizards to the Access interface is easy when you understand how the object wizard Registry trees are structured:

The order of wizards presented by Access in the four lists noted previously is controlled by the Index key of each wizard's Registry entry. For example, to reverse the order of the AutoForm: Columnar and the AutoForm: Tabular wizards on the list of new form wizards, swap their Index key values.

In addition to providing you with the ability to launch these object wizards, Access allows you to define two types of specialty builders and wizards in its Registry structure:

The following list summarizes the various invocation methods for the builders and wizards you can add to Access:

You can add your own builders and wizards to the locations in the preceding list by creating additional Registry keys and values below the keys noted earlier in this topic. A combination of the following Registry values are needed in a Registry key that launches a builder or wizard:

I use the terms entry point and invocation function interchangeably in this chapter to refer to the procedure that Access calls to start your builder or wizard.

Figure 20.9 provides examples of these values in the Windows Registry structure for a custom property builder.

Fig. 20.9

These Windows Registry entries define how to launch a property builder.

While manually creating these Registry entries to support a builder or wizard is not a difficult challenge for a developer, distributing add-ins to non-sophisticated users requires a simpler, automated setup process. Access provides such a process, as detailed in "Installing Add-ins Via Self-Registration" later in this chapter.

A library database with installed builders and/or wizards does not automatically receive a reference, nor does it need one to run these add-ins. Without a reference, Access can invoke the add-in but your code cannot call procedures in a builder or wizard library. If the wizard database will also serve as a code library, it must also be installed as a referenced library, as described in the section "Referencing a Library" earlier in this chapter.

The entry points for builders and wizards are available (exposed) to Access by virtue of their supporting Registry settings. This is how Access can invoke an add-in without having referenced its database. The entry points are not shown in the Object Browser.

Invoking Builders and Wizards

There are two requirements for invoking a builder or wizard. The first is that Windows Registry entries must be created that point to the invocation function. These entries are described in the previous section.

The second requirement is the creation of properly structured invocation functions (also called entry points). The entry point for a builder or wizard is a function located in the builder or wizard's library database. This function must be structured with specific arguments, because Access calls the defined entry point and passes certain arguments to it, depending on the type of builder or wizard.

The invocation function is merely a "launching point" for your add-in. You do not need to do any work in the function itself, you can open a form or forms to collect information, or call a subsidiary procedure to do the bulk of the work. The invocation function has only two purposes:

  1. To allow Access to pass information to your add-in via the entry point's arguments.
  2. To allow your add-in to return information back to the calling object by setting the return value of the entry function.

The builder and wizard examples that follow exemplify both of these points.

Invoking Control Wizards

Control builders and wizards are launched when a new control is created on a form or report, but only if the Control Wizards toggle button in the toolbox is depressed.

When Access calls a control builder or wizard, the entry point must define two arguments:

  1. Access first passes to the invocation function the name of the target control.
  2. The second argument is the name of the target control's label.

Here is the invocation function syntax for control builders and wizards:

Public Function name(controlname As String, labelname As String) _

  As Variant

You can replace the placeholders controlname and labelname in the function template shown with any valid variable names. Although Access prescribes a specific structure for entry point functions, it does not care what actual procedure or variable names are used in the structure.

Because their structure (arguments list) is fixed and defined by Access, I use a naming convention suffix _Entry for entry point functions in order to clearly identify them, as in this example:

Public Function lci_TxtBldr_Entry(rstrCtlName As String, _

  rstrLblName As String) As Variant

Obviously the target control's name is not valuable without a pointer to its form or report object parent. When your control builder or wizard first starts, it should create an object variable that points to the target control's parent object. Listing 20.7 provides an example of code that does this.

Listing 20.7 The Entry Point Function for a Control Builder Should Begin By
Setting a Pointer to the Object That Called It

Dim obj As Object

Select Case Application.CurrentObjectType

  Case acForm

    Set obj = Screen.ActiveForm

  Case acReport

    Set obj = Screen.ActiveReport

End Select

The variable must be set before your entry point opens another form for use by the builder or wizard, because once a builder or wizard form opens, the ActiveForm property will return it rather than the original calling object.

With a pointer to the calling form or report, and the name of the target control, your builder or wizard can manipulate the target control and/or its label, changing properties as required by the add-in.

Your control builder or wizard must directly manipulate the target control. Expecting this, Access ignores the return value of its entry point function for these add-ins. You do not need to set the invocation function's return value at the completion of a control builder or wizard.

Let's examine a sample control builder which asks the user to define the base name for a text box control, then creates an LNC name for both the control and its label. Figure 20.10 shows the Registry entries for the builder.

Fig. 20.10

Windows Registry entries that launch an example Text Box control builder.

After creating the Registry entries shown, the presence of a control builder in the Registry causes the Build... option to appear enabled on the shortcut menu for TextBox controls, as shown in Figure 20.11.

Fig. 20.11

Control builders and wizards appear on the shortcut menu as a Build... button in form design.

The code in Listing 20.8 provides an example of a builder that names text boxes and their labels. Notice that the function name in the listing matches the name of the entry point function installed for the builder in the Registry, as shown previously in Figure 20.10. Also, when Access starts the builder invocation function, it automatically passes in two argument values, as described at the beginning of this topic. The pseudocode in the listing explains the flow of the builder code.

Listing 20.8 LCI_Wiz.Mda-A Builder That Requests a Name and Label
for a Text Box From the User and Applies the Entered Properties to the Control

Public Function lci_TxtBldr_Entry(rstrCtlName As String, _

   rstrLblName As String) As Variant

' Purpose:   Entry point for text box builder

' Arguments: All control builders have these arguments:

'              rstrCtlName:=Current control name

'              rstrLblName:=Current label name

' Pseudo Code:

'   1. Create an object variable on the active object

'   2. Suggest and collect the control's base name

'   3. Create LNC text box and label names from the base name

'   4. Suggest and collect the label's caption

  Dim obj     As Object

  Dim lbl     As Label

  Dim strBase As String

  Dim txt     As TextBox

  ' Set a pointer to the calling object

  Select Case Application.CurrentObjectType

    Case acForm

      Set obj = Screen.ActiveForm

    Case acReport

      Set obj = Screen.ActiveReport

  End Select

  Set lbl = obj(rstrLblName)

  Set txt = obj(rstrCtlName)

  ' Suggest the ControlSource as the base name

  strBase = Nz(txt.ControlSource, "")

  ' If the ControlSource is an expression, make no suggestion

  If Left(strBase, 1) = "=" Then

    strBase = ""

  End If

  ' Collect the control base name

  strBase = InputBox("Enter the control's base name:", "Base Name", strBase)

  If Len(strBase) = 0 Then  ' User aborted

    GoTo lci_TxtBldr_Entry_Exit

  End If

  ' Create object names

  txt.Name = "txt" & strBase

  lbl.Name = "lbl" & strBase

  ' Suggest the base name as the prompt

  If Left(lbl.Caption, 4) = "Text" Then  ' Not a good prompt

    strBase = strBase & ":"  ' Build prompt from base name

  Else  ' Prompt is good, add only a colon if needed

    strBase = lbl.Caption

    If Right(strBase, 1) <> ":" Then

      strBase = strBase & ":"

    End If

  End If

  ' Collect the prompt

  strBase = InputBox("Enter the label caption:", "Caption", strBase)

  If Len(strBase) <> 0 Then  ' Didn't abort

    lbl.Caption = strBase

  End If

End Function

The code in the example demonstrates a builder that does not rely on a form to collect information. Instead, the builder makes some judgment calls of its own, and then requests user feedback by employing the InputBox() function.

Invoking Property Wizards

Property builders and wizards are invoked by clicking the build (...) button next to a property setting's text box in the Properties dialog.

When Access calls a property builder or wizard, the entry point must define three arguments:

  1. Access first passes to the invocation function the name of the target object.
  2. The second argument passed to the add-in is the name of the current control, if any.

Some properties to which you can attach a builder or wizard apply to objects that are not controls. In such a case, the controlname argument of your invocation function will receive the name of an object that is not a control. Your invocation function must detect such instances and react appropriately.

For an example of code that deals with this issue, see Listing 20.9.

  1. The third argument passed to your invocation function is the current value of the desired property for the target object.

Here is the invocation function syntax for property builders and wizards:

Public Function name(objectname As String, controlname As String, _

  currentvalue As String) As String

Listing 20.9 shows a sample invocation function for a property builder to help the user set the SpecialEffect property. Refer to Figure 20.12, which shows the builder form, when reading the listing. Note in the figure that the presence of the builder is indicated by the Build button (the ellipses ...) on the Special Effect property in the Label control's Properties dialog box.

Fig. 20.12

A single-screen builder form to collect SpecialEffect property information from the user.

Listing 20.10 LCI_Wiz.Mda-Core Routine for a SpecialEffect Property
Builder

' The entry point in a standard module

Public Function lci_SpecialEffectBldr_Entry(rstrObjName As String, _

  rstrCtlName As String, rstrCurVal As String) As String

' Purpose:   Entry point for SpecialEffect property builder

' Arguments: All property builders have these arguments:

'              rstrObjName:=Current object name

'              rstrCtlName:=Current control name

'              rstrCurVal:=Current property value

  On Error GoTo lci_SpecialEffectBldr_Entry_Err

  Const cstrProc As String = "lci_SpecialEffectBldr_Entry"

  Dim ctl        As Control

  Dim obj        As Object

  Dim varArgs    As Variant

  

  ' Set a pointer to the calling object

  Select Case Application.CurrentObjectType

    Case acForm

      Set obj = Forms(rstrObjName)

    Case acReport

      Set obj = Reports(rstrObjName)

  End Select

  

  ' Must check for a current control name that is actually a section

  '   by setting the passed argument to a control variable and trapping failure

  On Error Resume Next

  Set ctl = obj(rstrCtlName)

  If Err.Number <> 0 Then  ' Not a control, must be a section

    varArgs = "1" & rstrCurVal  ' Set the flag for a section

  Else

    varArgs = "0" & rstrCurVal  ' Set the flag for a control

  End If

  On Error GoTo lci_SpecialEffectBldr_Entry_Err

  ' The form places the code shell in global gstrEffect

  DoCmd.OpenForm "lci_fwzmSpecialEffectBldr", , , , , acDialog, varArgs

  If Len(gstrEffect) > 0 Then

    lci_SpecialEffectBldr_Entry = gstrEffect  ' Return the form's input

  Else

    lci_SpecialEffectBldr_Entry = rstrCurVal  ' Return the original value

  End If

  

lci_SpecialEffectBldr_Entry_Exit:

  Exit Function

lci_SpecialEffectBldr_Entry_Err:

  Call lci_ErrMsgStd(mcstrMod & "." & cstrProc, Err.Number, Err.Description, 0, True)

  Resume lci_SpecialEffectBldr_Entry_Exit

End Function

' Form: lci_fwzmSpecialEffectBldr

' Code in the builder form's Load event to manage the object type

Private Sub Form_Load()

' Purpose:   Pre-select the form values

' Arguments: OpenArgs has two values, concatenated:

'              Is control (0) or is Detail section (1)

'              Current value setting

  Dim varArg As Variant

  With Me

    varArg = Mid(.OpenArgs, 2)  ' Current value

    ' Three settings are not valid for a Detail section

    If Left(.OpenArgs, 1) = "1" Then  ' Disable some if a Detail section

      !optEtched.Enabled = False

      !txtEtched.Locked = False

      !optShadowed.Enabled = False

      !txtShadowed.Locked = False

      !optChiseled.Enabled = False

      !txtChiseled.Locked = False

    End If

    !grpSpecialEffect = _

      Switch(varArg = "Flat", 0 _

           , varArg = "Raised", 1 _

           , varArg = "Sunken", 2 _

           , varArg = "Etched", 3 _

           , varArg = "Shadowed", 4 _

           , varArg = "Chiseled", 5)

  End With

End Sub

Note three important points about the builder code in the listing:

gstrEffect = Choose(Me!grpSpecialEffect + 1, "Flat", "Raised", "Sunken" _

  , "Etched", "Shadowed", "Chiseled")

Access also provides a special derivative of property builders and wizards that can be used to build or edit module code. These expression builders are installed on the Build... option on the shortcut menu in module design view, as shown in Figure 20.13.

Fig. 20.13

An expression builder is invoked from Build... on the module design shortcut menu.

The entry point function for such add-ins is a derivative of the standard property builder invocation function syntax:

Public Function name(modulename As String, _

   procedurename As String, currentvalue As String) As Variant

When invoking an expression builder or wizard, Access passes these arguments to your entry point:

  1. The first argument is the current module's name, as in the following examples. The first example is the name of a standard module, and the second is a form module:
lib_basFrm

Form_Form1

  1. The second argument passed to your expression builder is the name of the current procedure, based on the location of the cursor when the add-in was invoked. If the user was viewing the Declarations section of a module, this argument is empty.
  2. If the user has selected any text in the module editor before invoking the builder or wizard, the currently selected text is passed in to your entry point as the third argument.

The return value of an expression builder or wizard's invocation function should be a string of valid VBA code. If the user has selected text in the module editor before starting your invocation function, Access replaces the selected text with the function's return value. If there is no selection, the text is inserted at the cursor's current location when the builder or wizard ends.

Figure 20.14 shows a very simple form interface for an expression builder that creates an LNC-compatible procedure code shell in the current module.

Fig. 20.14

This example procedure builder screen collects information used to create a VBA code shell.

The Registry entries to support the procedure builder shown in the previous figure are in Figure 20.15. Note that these entries are similar to the entries from other property builders.

Fig. 20.15

These Windows Registry entries define how to launch an expression builder.

Listing 20.10 shows the invocation function that drives the sample expression builder. The function's only purposes are to display the form to collect user input, and to send the code shell back to Access as the function's return value. The builder code for creating the procedure text from the user's selections in the form is located in the form's module.

Listing 20.10 LCI_Wiz.Mda-The entry point or "invocation function"
for a simple procedure builder

' The entry point for the procedure builder

Public Function lci_PrcBldr_Entry(rstrModName As String, _

   rstrPrcName As String, rstrCurVal As String) As Variant

' Purpose:   Entry point for procedure builder

' Arguments: All expression builders have these arguments:

'              rstrModName:=Current module name

'              rstrProcName:=Current procedure name

'              rstrCurVal:=Currently selected text

  ' The form places the code shell in gstrPrcText

  DoCmd.OpenForm "lci_fwzmPrcBldr", , , , , acDialog

  lci_PrcBldr_Entry = gstrPrcText

End Function

' Form: lci_fwzmPrcBldr

' The code in the builder form's module that creates gstrPrcText

Private Sub cmdOK_Click()

' Purpose: Shell out the code

  Const cstrErrForm As String = _

    "Call lci_ErrMsgStd(Me.Name & ""."" & cstrProc, Err.Number," _

    & " Err.Description, True)"

  Const cstrErrMod  As String = _

    "Call lci_ErrMsgStd(mcstrMod & ""."" & cstrProc, Err.Number," _

    & " Err.Description, True)"

  Dim strErr As String

  Dim strTop As String

  strTop = Choose(Me!grpScope, "Public", "Private") & " " _

    & Choose(Me!grpType, "Function", "Sub") & " " _

    & Me!txtName & "() " _

    & Choose(Me!grpType, "As Variant", "") & vbCrLf _

    & "' Purpose: " & Me!txtPurpose & vbCrLf _

    & "' Arguments:" & vbCrLf _

    & Choose(Me!grpType, "' Returns:" & vbCrLf, "") _

    & "' Authors: " & Me!txtAuthor & " " & Format(Now, "MM/DD/YY HH:MM") & vbCrLf _

    & "' Calls:" & vbCrLf _

    & "' Example: " & Me!txtName & "()" & vbCrLf & vbCrLf

  strErr = "  On Error GoTo " & Me!txtName & "_Err" & vbCrLf _

    & "  Const cstrProc As String = """ & Me!txtName & """" & vbCrLf & vbCrLf _

    & Me!txtName & "_Exit:" & vbCrLf _

    & "  Exit " & Choose(Me!grpType, "Function", "Sub") & vbCrLf & vbCrLf _

    & Me!txtName & "_Err:" & vbCrLf _

    & "  " & Choose(Me!grpHandler, cstrErrForm, cstrErrMod) & vbCrLf _

    & "  Resume " & Me!txtName & "_Exit" & vbCrLf & vbCrLf _

    & "End " & Choose(Me!grpType, "Function", "Sub") & vbCrLf

  gstrPrcText = strTop & strErr

  DoCmd.Close acForm, Me.Name

End Sub

When the user clicks OK in the builder, the code in the previous listing runs and creates a text string that is the shell of a VBA procedure. The code shell is passed back to the invocation function in a global variable, which in turn passes the shell back to Access. Access pastes the code shell in at the current cursor position. Figure 20.16 shows the result of executing the builder shown in the listing.

Fig. 20.16

The output from a simple procedure builder is pasted to the Module window.

Using the programmable Module object introduced in Access 97, you can perform a wide variety of operations from an expression builder. See the previous section "Creating Development Utilities" for more information on the Module object.

Invoking Form and Report Wizards

Form and report wizards can be very complex to write. The big challenges in such an effort include the following:

Invoking your own object wizards, however, is a simple task. They are invoked by selecting Form or Report from the Insert menu, clicking the New button on the Form or Report tabs in the Database window, or clicking the New Object button on the toolbar.

Access manages the job of invoking these object wizards for you, by providing these services:

  1. Access places your wizard on the list shown in the New Form or New Report dialog. Access does this automatically if you install your wizard in the property Registry tree, as described in the earlier section "Installing Builders and Wizards."
  2. Access enforces the user's selection of a record source for the new object, if you opt to use such enforcement. (The enforcement is triggered by the Datasource Required Registry key, also described in the "Installing Builders and Wizards" section.)

The entry point for a form or report wizard has one required argument-the record source for the object. Access passes to this argument the table or query name selected by the user in the combo box labeled "Choose the table or query..." in the New Object dialog. If the Datasource Required Registry key for your wizard is set to 0, the user is not required to select a record source and Access passes an empty string to this argument. Here is the syntax for a form or report wizard entry point:

Public Function name(recordsource As String) As Integer

If you browse the Registry entries for the form and report wizards shipped with Access, you will notice that some entries have values named Argument1 and Argument2. These Registry settings cause Access to pass additional values from the Registry to the wizard's invocation function as arguments. This allows a form or report wizard to pass information about the type of the wizard to the entry point. The information passed in the additional argument slots does not come from the current Access instance, as is the case with other invocation arguments, but instead comes from the Registry arguments.

For example, the Access wizard called "AutoForm: Columnar" is installed with Registry data of 1 for the Argument1 value in its key, and Registry data of 2 for the Argument2 value. When this wizard calls its invocation function, these Registry values are passed in as the second and third arguments, as shown in the invocation function for this wizard:

Function auto_Entry(stRecSrc As String, iPaneContent As Integer, _

  iDocType As Integer) As Integer

The invocation function in this example uses the first argument value to determine the type of process to run (an iPaneContent argument of 1 causes the wizard code to create a columnar form). The second argument specifies the type of object to create, with the value 2 designating a new form.

Knowing about this hack in the Registry allows you to pass one or two arguments from the Registry to your form wizards as well. The arguments do not need to be of the same name or data type as shown in the example here.

You cannot create special Registry values Argument1 or Argument2 for any other types of builders and wizards-Access only supports the additional arguments for form and report wizards.

Form and report wizards are launched without regard to the current object in the Access interface. In other words, they are intended to create a new object and thus do not care if the current object is a form, report, table, the Database window, and so forth. Your wizard code must build the shell of the new object before placing controls on it or setting any properties. Use the CreateForm and CreateReport methods at the beginning of your wizard to achieve this result.

Creating Menu Add-ins

Menu add-ins are utilities that locate themselves on the Tools, Add-Ins menu. They usually provide tools and extensions that are not application-specific, but instead are appropriate to general database development or usage.

You cannot prescribe that a specific add-in expose itself on the Tools menu only within a particular context, because Access does not trigger user-interaction events that you can trap. For example, there is no way to detect when the user is working within a form in design view, and to enable a specific add-in only in this context. However, your add-in entry point code can test the environment using the CurrentObjectType property of the application to determine what kind of object currently has the focus.

When Access starts up, a menu add-in item is placed on the Tools menu for each key in the tree \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Menu Add-Ins. The key name in the Registry becomes the add-in's listing on the Add-Ins submenu. You can include an access key designation in the Registry key name to underline the access key on the menu. (An access key designation is an ampersand preceding the character to use as the Alt key shortcut for the menu item.)

Figure 20.17 shows the Registry entries for the add-ins shipped with Access, as well as the custom Last Update add-in that we will write in this section.

Fig. 20.17

These Windows Registry define how to launch menu add-ins.

As with builders and wizards, a menu add-in is launched by Access' call to an invocation function. When your add-in is selected from the menu, Access executes the invocation expression defined in the Registry for that add-in. These are the menu add-in Registry entries:

=functionname()

Menu add-ins are most useful for creating new database objects or performing bulk operations on a database. As an example, consider the code in Listing 20.11. This routine loops through the object containers in a database and finds the object with the most recent update date. Because this is a database-wide operation, it makes a perfect candidate for an add-in routine.

Listing 20.11 LCI_Wiz.Mda-A Simple Menu Add-in to Identify the Most
Recently Updated Object

Public Function lci_LastUpdateAddIn_Entry() As Variant

' Purpose: Entry point for last update add-in

' Returns: Nothing

  Dim con     As Container

  Dim dbs     As Database

  Dim dtmMax  As Date

  Dim edoc    As Document

  Dim iintCon As Integer

  Dim strCon  As String

  Dim strObj  As String

  Set dbs = CurrentDb

  ' Open each container and find the most recent date

  For iintCon = 1 To 5

    strCon = Choose(iintCon, "form", "module", "report" _

      , "script", "table")

    Set con = dbs.Containers(strCon & "s")

    ' Check each document in the container

    For Each edoc In con.Documents

      ' Identify the item with the most recent date/time

      If edoc.LastUpdated > dtmMax Then

        dtmMax = edoc.LastUpdated

        If strCon = "table" Then

          strCon = "table/query"

        End If

        strObj = strCon & " " & edoc.Name

      End If

    Next

  Next iintCon

  Beep

  MsgBox "The last updated object in the database " _

    & dbs.Name & " is the " & strObj _

    & ", updated " & Format(dtmMax, "mm/dd/yy hh:mm:ss") & "." _

    , vbOK, "Last Update"

End Function

The add-in shown in the listing displays only a message box with the result of its search. It could, however, easily be made to display one or more forms, create a new database object, or perform any other legitimate Access operation.

Figure 20.18 (previously) shows the Registry entries that cause the function in this listing to be invoked by an option on the Tools menu.

Installing Add-ins Via Self-Registration

Access 97 makes it easy to create the Registry keys required by your add-ins. Each add-in database should travel with a system table USysRegInfo that contains the information required for the Add-In Manager to create Registry keys for the add-in.

The Add-In Manager uses two information items to help users install add-ins. The first is the database properties of the add-in, and the second is the Registry key information in the USysRegInfo table.

There are three database properties that you should set in any add-in database. The Add-In Manager retrieves these properties from an add-in database and displays them in its dialog, as diagrammed in Figure 20.18.

Fig. 20.18

Custom database properties are retrieved from an add-in database and displayed in this dialog.

Each of the specified database properties is found on the Summary tab of the Database Properties dialog:

When the Add-In Manager is invoked, it scans all of the MDA files in the Access directory, and displays each in the list box of its dialog. (You can select the Add New... button in the dialog to add additional libraries to the list that are not located in the Access directory).

When you highlight an add-in's description in the dialog's list box and click the Install button, Access scans the USysRegInfo table in the add-in's file and builds Registry entries from it. Each add-in that will be installed from the Add-In Manager must have entries in this table.

Tables that begin with USys (or MSys) are not displayed in the Database window by default. Once you have created this table in your add-in database, you will need to select Show System Objects in the Options dialog in order to display and work with the table, or create a query that provides a datasheet with records from the hidden table.

Access provides a template that you can use to help you build the USysRegInfo table. Here are the steps to use it:

  1. Import the table named Sample USysRegInfo from the WZTOOL80.MDE database in your Access directory into your current library database. Open the table.
  2. Modify the sample records to install your add-in, or delete the samples and create new records with the required Registry entries. Required Registry entries for different add-in types are described in earlier sections of this chapter.
  3. Rename the Sample USysRegInfo table to USysRegInfo before deploying your add-in.

For each add-in that you want to install, there will be multiple records in the USysRegInfo table-a header record, then one record for each Registry key to create. If there are multiple add-ins in a single library database, there will be several records in this table with matching Subkey field values for each add-in to install. (For our purposes here, I will call a set of records with identical Subkeys a block. One block defines the keys for one add-in.)

Records in this table must match a pre-defined structure. The first field is called Subkey; it names the key to create in the Windows Registry for the add-in. It is the same for each record in the table that relates to a single add-in.

Regardless of the type of add-in being installed, the first record in each block will have only Subkey and Type field values. The Type value will be 0 to indicate that a new key must be created, and the ValName and Value fields are blank.

The Subkey field specifies the full name of the key to create, and starts with one of these two placeholder strings:

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\

To complete the Subkey field entries for a block, append to one of the two placeholders strings the remainder of the key name to be created, using these syntax templates:

HKEY_CURRENT_ACCESS_PROFILE\Wizards\wizardtype\itemtype\addinname

HKEY_LOCAL_MACHINE\Menu Add-Ins\addinname

The values for wizardtype and itemtype can be any of the combinations shown in Table 20.5.

Table 20.5 Values For Wizard Registry Keys

wizardtype value

itemtype value

Control Wizards

BoundObjectFrame

Control Wizards

CheckBox

Control Wizards

ComboBox*

Control Wizards

CommandButton*

Control Wizards

Image

Control Wizards

Label

Control Wizards

Line

Control Wizards

ListBox*

Control Wizards

OptionButton

Control Wizards

OptionGroup*

Control Wizards

PageBreak

Control Wizards

Rectangle

Control Wizards

SubformSubreport*

Control Wizards

TabControl

Control Wizards

TextBox

Control Wizards

ToggleButton

Control Wizards

UnboundObjectFrame

Control Wizards

OLEcontrolclassname

Form Wizards

wizardname

Query Wizards

wizardname

Property Wizards

BackColor*

Property Wizards

BorderColor*

Property Wizards

FieldName*

Property Wizards

ForeColor*

Property Wizards

InputMask*

Property Wizards

LinkChildFields*

Property Wizards

LinkMasterFields*

Property Wizards

Module

Property Wizards

ODBCConnectStr*

Property Wizards

Picture*

Property Wizards

ShortcutMenuBar*

Property Wizards

propertyname

Report Wizards

wizardname

Table Wizards

wizardname

The itemtype values in the table that are starred reflect Registry trees that will already exist to support the builders and wizards shipped with Access.

Figure 20.19 shows two USysRegInfo blocks from the sample database AES_WIZ.MDA on the CD. The first block shows entries for a menu add-in, and the second shows a control wizard (builder). The CD-ROM file contains all four sample add-ins built in this chapter.

Fig. 20.19

Data from this USysRegInfo table drives the creation of Registry entries for add-ins.

In addition to the Subkey field, USysRegInfo tables have the following three fields (refer to the previous figure for sample values):

You can use the placeholder "|AccDir\" in front of the name of a library database specified in the Value field. During creation of the Registry value, Access replaces this stub with the path providing the current location of Access (by default, C:\Program Files\Microsoft Office\Office\).

The actual combinations of ValName and Value entries for builder and wizard add-ins are specified in the section "Installing Builders and Wizards," and the entries for menu add-ins are found in "Creating Menu Add-ins." Both of these sections are located earlier in this chapter.

This table-driven, automatic registration process for add-ins is easy for the developer to construct and for the users to employ. Adding to the simplicity and usability is the fact that tools installed this way are available in the current session of Access immediately after closing the Add-In Manager, without the need to close and reopen either the current database or Access itself.

From Here...

This chapter detailed the nitty-gritty of writing and installing add-ins. Your development efforts can be more enjoyable and more productive if you have quality libraries and tools that you can rely on.


1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.