CREATING YOUR OWN ADD-INS

I used to think that spending time to create your own add-in was pretty silly. However, that has changed, and I'm starting to believe that it would be very cool to create add-ins to perform a number of tasks. Hence, this chapter. Don't get me wrong—add-ins have always been cool, but I thought they belonged in the realm of third-party vendors such as FMS, who creates numerous add-ins for Access and Visual Basic. (You can see some FMS product demos on the CD-ROM in the 3rdPartyFMS folder.)

My thinking that creating add-ins is only for third-party vendors has changed because I've come up with a tool of my own that I find pretty useful, and I want a quick way to put it on most of my forms. I want it placed on a form when the form is created, without the hassle of my having to remember to paste the code behind the forms.

Working with the Bookmark Tracker Wizard

The tool created by the wizard in this chapter is the Bookmark Tracker created in the last chapter. The wizard that you will create in the following sections will place a Bookmark Tracker combo on the form you happen to be in. It will also copy all the objects used by the combo for managing the bookmarks into the current database.

Reviewing the Bookmark Tracker

Before going into detail on what the wizard does, let's review the Bookmark Tracker itself. The Bookmark Tracker enables users to manage multiple bookmarks on an open form (see Figure 17.3).

Here are some of the features of the Bookmark Tracker:

• You can add bookmarks just by moving to the record you want to bookmark and then selecting << Add a New Bookmark >>.

• To delete bookmarks, select << Remove Bookmark(s) >> from the combo box. You are then given the dialog to remove bookmarks.

• You can move to a stored bookmark by selecting one from the list.

The Bookmark Tracker consists of one form (not counting the form that the combo is placed on) and two class modules. These objects are as follows:

These objects, along with the code in Listing 17.1, are necessary for the Bookmark Tracker to do its job.

Listing 17.1. Managing Multiple Bookmarks on a Given Form

Option Compare Database

'-- Declare a clsBookmarkManagement variable.
Private bmtForm As clsBookmarkManagement

Private Sub cboBookmarkTracker_AfterUpdate()

   '-- Call the BookmarkAction method, for all actions.
   bmtForm.BookmarkAction

End Sub

Private Sub Form_Load()

   '-- Create an instance of the clsBookmarkManagement class
   Set bmtForm = New clsBookmarkManagement

   '-- Call the custom initialization routine, passing the
   '-- necessary items.
   bmtForm.InitBookmarks Me!cboBookmarkTracker, "LastName", _
      "FirstName"

End Sub

That's about it for the review of the Bookmark Tracker. If you need more information on Listing 17.1 or the Bookmark Tracker in general, reread Chapter 16, “Extending Your VBA Library Power with Class Modules and Collections.”

Features of the Bookmark Tracker Wizard

It's time now to look at the Bookmark Tracker Wizard and what it enables you to do. The Bookmark Tracker Wizard looks a lot like other wizards in Access 2000 in that it consists of multiple dialogs that take you logically from one step to another. You will see the wizard's dialogs in the next section.

Look at what the Bookmark Tracker Wizard accomplishes:

• It lets users select the target form, as well as the section of the form, on which to place the Bookmark Tracker combo box (see Figure 17.4).

  Figure 17.4. Selecting the form and section on the form where you want to place the combo box.

  

• It combines up to two fields to display in the combo box from those available based on the target form's record source (see Figure 17.5).

  Figure 17.5. The fields displayed in these combos change based on the record source of the target form.

  

• It creates a combo box with a user-specified name (see Figure 17.6).

  Figure 17.6. In addition to letting users specify the name of the combo box, the wizard checks to see whether a control with the same name already exists on the form.

  

In addition to the items displayed on the user interface portion of the Bookmark Tracker Wizard, the wizard generates code behind the target form, as displayed in Listing 17.1. It also does the following:

• It checks to see whether the objects listed in Table 17.1 are already in the target database.

• If objects don't exist in the target database, it copies them into the database and then marks them as hidden.

As you should be able to see by now, the wizard created here is full of features and will get you off and running to create add-ins of your own. But before pulling apart the wizard, look at what it takes to install the new add-in.

Installing Add-Ins in Access 2000

You can install your add-ins in Access in numerous ways. Here are two ways to install, the first of which will be discussed in greater detail in the next two sections:

• Manually register the add-in by using the Add-In Manager.

• Use the techniques discussed in Chapter 18, “Manipulating the Registry with VBA,” to set the values.

Looking at the USysRegInfo Table

Regardless of which method you decide to use to install the add-in, Access provides a means for you to specify the settings necessary to update the Windows Registry. These values are set within the add-in database itself in the USysRegInfo table.

Figure 17.7 shows the USysRegInfo table for the add-in BMTWiz.mda, created for the Bookmark Tracker Wizard in this chapter. This add-in database can be found in the folder ExamplesChap17 on the CD-ROM.

The fields necessary for Access to register the add-in are as follows:

Using the Add-In Manager to Install Your Wizard

To use the Add-In Manager, follow these steps while in the Chap17.mdb database:

You've installed the Bookmark Tracker Wizard and can see it in the list of available add-ins, with a × next to it (see Figure 17.8). The Add-In Manager has also added the necessary entries into the Registry as per the USysRegInfo table (see Figure 17.9).

Programming the Bookmark Tracking Wizard

By examining the Bookmark Tracking Wizard, you will see a number of different VBA commands:

• Populating combos based off current form sections and record source fields

• Creating controls

• Copying objects from the add-in database to an application database

• Locating lines of code from modules

• Adding lines of code to modules

You will see these techniques used as each page of the wizard is discussed. To start, look at the overall form itself in Design mode (see Figure 17.10).

The form used for the wizard, frmBookmarkTrackerWizard, is in the BMTWiz.mda database, located in the ExamplesChap17 folder on the CD-ROM. This form uses Access's tab control with no tabs visible. Note the pages listed in the Object drop-down list (names starting with pg) in Figure 17.10.

You can set the tabs to not be visible by setting the Style property on the tab control to None.

Initializing the Wizard Form

It's time to look at some routines connected to the main wizard form itself. The first subroutine is the form's Open event (shown in Listing 17.2).

Listing 17.2. BMTWiz.mdb: Setting Up the Wizard for Use

Private Sub Form_Open(Cancel As Integer)

    Dim strFormList As String
    Dim strCurrDoc As String
    Dim aobjForm As AccessObject

    '-- Look through the forms in the application database and
    '-- grab those that aren't hidden and not the same name as
    '-- this form.

    For Each aobjForm In CurrentProject.AllForms

        strCurrDoc = aobjForm.Name

        If Not GetHiddenAttribute(acForm, strCurrDoc) _
              And strCurrDoc <> Me.Name Then

            strFormList = strFormList & strCurrDoc & "; "

        End If
    Next aobjForm

    '-- Trim off the last semi-colon and assign the string to
    '-- the forms listbox.
    strFormList = Left$(strFormList, Len(strFormList) - 2)

    Me!lstForms.RowSource = strFormList

End Sub

The Open event routine first populates the lstForms list box with the available forms that aren't hidden and aren't named the same name as the wizard form. (The reason this form's name is important is that when you start creating your own wizard, you will probably be running the wizard from within itself.)

The last task is to create a row source for the cboSection combo box by going through the Sections array of the target form.

The Form_Open event makes good use of the new CurrentProject object and AllForms collection, both introduced in Access 2000. To read more about these objects, see Chapter 4, “Looking at the Access Collections.”

Looking at the cmdCancel, cmdPrevious, and cmdNext Command Buttons

The tasks of the cmdCancel, cmdPrevious, and cmdNext command buttons are fairly straightforward (see Listing 17.3).

Listing 17.3. BMTWiz.mdb: Command Buttons Doing a Lot of Work

Private Sub cmdCancel_Click()

    DoCmd.Close acForm, Me.Name

End Sub

Private Sub cmdNext_Click()

    '-- Increment the tab page

    If CheckFilledFields() Then
        Me!tabWizard = Me!tabWizard + 1
    End If
End Sub
Private Sub cmdBack_Click()

    '-- Decrement the tab page
    If CheckFilledFields() Then
        Me!tabWizard = Me!tabWizard - 1
    End If

End Sub

The cmdCancel_Click routine closes the wizard form if the user clicks the button. The cmdPrevious and cmdNext buttons call the CheckFilledFields function to make sure that the required fields are filled in, and then they increment and decrement the page value accordingly.

Listing 17.4. BMTWiz.mdb: Making Sure That Required Fields Are Filled In

Function CheckFilledFields() As Boolean

    CheckFilledFields = True

    '-- Check to make sure the needed fields are supplied.

    Select Case Me!tabWizard
        Case 0
            If IsNull(Me!lstForms) Then
                Me!lstForms.SetFocus
                MsgBox "A form must be selected to continue.", _
                        vbInformation, Me.Caption
                CheckFilledFields = False
            End If

        Case 1
            If IsNull(Me!cboField1) Then
                Me!cboField1.SetFocus
                MsgBox "You must select at least one field to include " & _
                       "in the description of the bookmark.", _
                        vbInformation, Me.Caption
                CheckFilledFields = False
            End If
    End Select

End Function

The code in CheckFilledFields checks for two fields to be filled in on the wizard. First, if leaving the first page of the wizard, a form must be picked from the lstForms listbox. Otherwise, if leaving the second page, the first combo box of the description (called cboField1) must be filled in.

Switching Pages of the Wizard's Tab Control

The next thing to examine is what occurs when you switch pages of the tab control, tabWizard, by clicking the cmdNext and cmdPrevious command buttons. When the tabWizard pages are switched, the Change event occurs (see Listing 17.5).

Listing 17.5. BMTWiz.mdb: Tasks Performed When Changing Pages on the Tab Control

Private Sub tabWizard_Change()


    Me!tabWizard.Pages(Me!tabWizard).SetFocus

    '-- If the combo box page, assign the chosen form's rowsource
    '-- to the fields combo boxes.

    If Me!tabWizard = 1 Then
        Me!cboField1.RowSource = Forms(Me!lstForms).RecordSource
        Me!cboField2.RowSource = Forms(Me!lstForms).RecordSource
    End If

    '-- Handle the cmdNext button based on the current page
    If Me!tabWizard = Me!tabWizard.Pages.Count - 1 Then
        Me!cmdNext.Enabled = False
    ElseIf Not Me!cmdNext.Enabled Then
        Me!cmdNext.Enabled = True
    End If

    '-- Handle the cmdBack button based on the current page
    If Me!tabWizard = 0 Then
        Me!cmdBack.Enabled = False
    ElseIf Not Me!cmdBack.Enabled Then
        Me!cmdBack.Enabled = True
    End If

End Sub

This event procedure performs the following steps:

The last command button to look at is the cmdFinish button. However, because the bulk of the work is performed by the Click event for this button, I want to save it for last. For now, look at the validation that goes on for each field on the wizard, because the validation performed will be things you need to watch out for creating your own wizards.

Validating Fields on the First Page of the Wizard Form

The first control to look at, lstForms, has two events programmed: BeforeUpdate and AfterUpdate. You can see the BeforeUpdate event here:

Private Sub lstForms_BeforeUpdate(Cancel As Integer)

    Dim mdlForm As Module

    DoCmd.OpenForm Me!lstForms, acDesign, WindowMode:=acHidden
    Set mdlForm = Forms(Me!lstForms).Module

    If CheckBMTAlreadyExists(mdlForm) Then
        MsgBox "This form already has a bookmark tracker combo on it!", _
           vbInformation, Me.Name
        Cancel = True
        Exit Sub
    End If

End Sub

This code performs three interesting tasks:

Listing 17.6 shows the code for the CheckBMTAlreadyExists function.

Listing 17.6. BMTWiz.mdb: Testing to See Whether a Bookmark Tracker Combo Box Already Exists on the Form

Function CheckBMTAlreadyExists(mdlForm As Module) As Boolean

    Dim lngStartLine As Long, lngStartCol As Long
    Dim lngEndLine As Long, lngEndCol As Long

    mdlForm.Find "Private bmtForm As clsBookmarkManagement", _
                lngStartLine, lngStartCol, lngEndLine, lngEndCol

    If lngStartLine <> 0 Then
        CheckBMTAlreadyExists = True
    End If

End Function

The routine finds whether the Bookmark Tracker already exists on the form by looking through the module's VBA code. By using the Find method of the Module object, it searches for the existence of the following statement:

"Private bmtForm as clsBookmarkManagement"

Finding this statement in the module code tells the wizard that a copy of the Bookmark Tracker exists. At this point, users can select another form to place the Bookmark Tracker on or cancel the wizard.

The module Find method is a cool command that uses the following syntax:

module.Find strTextToFind, lngStartLine, lngStartCol, lngEndLine, _
   lngEndCol

If the text is found in the module, the line number where the text is located will be placed in lngStartLine.

In the AfterUpdate event of the lstForms list box control, the code goes through the Sections array and builds the row source for the cboSection combo box. Listing 17.7 shows the code for lstForms_AfterUpdate.

Listing 17.7. BMTWiz.mdb: Building the Row Source for the cboSections Combo Box

Private Sub lstForms_AfterUpdate()

    Dim intCurrSec As Integer
    Dim strSecName  As String
    Dim frmTarget As Form
    Dim strSource As String

    On Error Resume Next

    '-- Build a list of possible sections if they exist.
    For intCurrSec = 0 To 4
        strSecName = Forms(Me!lstForms).Section(intCurrSec).Name
        If Err.Number = 0 Then
            strSource = strSource & "; " & intCurrSec & _
                 "; '" & strSecName & "'"
        Else
            Err.Clear
        End If
    Next
    strSource = Mid$(strSource, 2)

    Me!cboSection.RowSource = strSource

End Sub

The last event procedure to look at is the Enter event for the cboSection combo box. This event checks to make sure that a form is selected in the lstForms list box. Otherwise, a message is given to the effect that a form must be selected. Here's the code:

Private Sub cboSection_Enter()

    If IsNull(Me!lstForms) Then
        Me!lstForms.SetFocus
        MsgBox "A form must be selected to continue.", _
           vbInformation, Me.Caption
End If

End Sub

That's it for the wizard's first page.

Fields and Event Procedures on the Second Page of the Wizard Form

Page two of the wizard consists of two combo boxes: cboField1 and cboField2. Figure 17.11 shows page two of the wizard in Design view.

Only two event procedures are used on the second page of the wizard:

Private Sub cboField1_AfterUpdate()

    Me!cmdFinish.Enabled = Not IsNull(Me!cboField1)

End Sub

Private Sub cboField2_Enter()
    If IsNull(Me!cboField1) Then
        MsgBox "You need to fill in the field for the first part of " & _
               "description before filling in part two.", _
               vbInformation, Me.Caption
        Me!cboField1.SetFocus
    End If
End Sub

The first procedure, cboField1_AfterUpdate, enables the cmdFinished button if the cboField1 has been filled in with data.

The next procedure, cboField2_Enter, makes sure that you've filled in cboField1 before attempting to enter data in cboField2.

Fields and Event Procedures on the Third Page of the Wizard Form

Page three, the final page of the wizard, consists of the txtBMTName text box and chkDesign check box. Figure 17.12 shows page three of the wizard in Design view.

The chkDesign control enables users to specify whether they want to keep the target form, including the VBE, open in Design view when the wizard is complete. If this is not specified, the wizard closes the form and the VBE. (You'll see more about this in Listing 17.10 in the next section.)

The txtBMTName text box enables users to pick their own name for the combo box control to be placed on the target form. txtBMTName has two events programmed: BeforeUpdate and AfterUpdate. Listing 17.8 shows both events.

Listing 17.8. BMTWiz.mdb: Testing the Combo Box Name Entered

Private Sub txtBMTName_BeforeUpdate(Cancel As Integer)

    If IsNull(Me!txtBMTName) Then

        MsgBox "You must supply a name for the combo box control." & _
            vbCrLf & vbCrLf & "Please enter a name.", vbInformation, _
            Me.Caption

        Cancel = True

    End If

End Sub

Private Sub txtBMTName_AfterUpdate()

    If CheckBMTNameDupe() Then
       Exit Sub
    Else
       mfNameChecked = True
    End If

End Sub

The first routine, txtBMTName_BeforeUpdate, checks to make sure that the name of the combo box is blank.

TxtBMTName_AfterUpdates calls the CheckBMTNameDupe() function, which checks whether a control already exists on the target form with the name specified in txtBMTName. Listing 17.9 shows the code for CheckBMTNameDupe().

Listing 17.9. BMTWiz.mdb: Testing for an Already Existing Control Name

Function CheckBMTNameDupe() As Boolean

    Dim ctlCurrent As Control

    For Each ctlCurrent In Forms(Me!lstForms).Controls

        If ctlCurrent.Name = Me!txtBMTName Then

MsgBox "The control already exists with the name " & _
                    "specified for the combo box." & vbCrLf & vbCrLf & _
                    "Please enter a new name.", vbInformation, Me.Caption

            Me!txtBMTName.SetFocus
            CheckBMTNameDupe = True

            Exit Function

        End If

    Next ctlCurrent

End Function

This code goes through each control on the target form and compares the name to that entered in txtBMTName. If a match is found, the focus is moved to the txtBMTName text box and True is passed back.

It's now time to look at the meat and potatoes of this whole wizard—the cmdFinished_Click event.

Finishing Up with the Wizard

After the user enters all necessary data and clicks the cmdFinished button, the cmdFinished_Click event procedure is executed. This event procedure basically

• Builds the Bookmark Tracker combo box
  
  
• Copies the necessary support form and class modules to the target database and hides them
  
  
• Creates the code behind the form needed to use the Bookmark Tracker combo box
  
  

All these tasks, including creating the VBA code behind the target form, are done with VBA commands. Listing 17.10 shows the cmdFinished_Click event procedure.

Listing 17.10. BMTWiz.mdb: Finishing Up the Wizard

Private Sub cmdFinish_Click()

    If Not mfNameChecked Then
        If CheckBMTNameDupe() Then
            Exit Sub
        End If
    End If

    Dim frmTarget As Form
    Set frmTarget = Forms(Me!lstForms)
    Dim ctlCombo As Control
    Dim ctlLabel As Control
    Dim strTarget As String
    Dim blnDesign As Boolean
    Dim mdl As Module

    strTarget = Me!lstForms
    blnDesign = Me!chkDesign

    '-- First, test to see if a module even exists. If not, create it.
    If Not frmTarget.HasModule Then

        frmTarget.HasModule = True

    End If

    '-- Set up the combo box to handle manipulating the bookmarks
    Set ctlCombo = CreateControl(frmTarget.Name, acComboBox, _
                Me!cboSection, , , 1710, 100, 2880)

    With ctlCombo
        .Name = Me!txtBMTName
        .ColumnCount = 2
        .ColumnWidths = "0;2"
        .RowSourceType = "Value list"
        .RowSource = "-1; '<< Add a New Bookmark >>'"
    End With

    '-- Create the label for the bookmark combo.
    Set ctlLabel = CreateControl(strTarget, acLabel, Me!cboSection, _
                Me!txtBMTName, , 60, 70, 1600, 300)
    ctlLabel.Caption = "Bookmark(s):"
    '-- Get a reference to the target form's module
    Set mdl = frmTarget.Module

    '-- Copy the Bookmark Tracker objects into the current application
    CopyObjectsToDatabase

    CreateDeclarationsArea mdl

    CreateFormLoadEvent mdl

    CreateComboAfterUpdateEvent mdl

    DoCmd.Close acForm, Me!lstForms, acSaveYes
    DoCmd.Close acForm, Me.Name

    Set frmTarget = Nothing
    Set mdl = Nothing

    If blnDesign Then
        DoCmd.OpenForm strTarget, acDesign
    Else
        Application.VBE.MainWindow.Visible = False
    End If

End Sub

The routine cmdFinished_After does a lot, so I will go through it a step at a time:

If Not mfNameChecked Then
    If CheckBMTNameDupe() Then
        Exit Sub
    End If
End If

If Not frmTarget.HasModule Then

    frmTarget.HasModule = True

End If

'-- Set up the combo box to handle manipulating the bookmarks
Set ctlCombo = CreateControl(frmTarget.Name, acComboBox, _
            Me!cboSection, , , 1710, 100, 2880)

With ctlCombo
    .Name = Me!txtBMTName
    .ColumnCount = 2
    .ColumnWidths = "0;2"
    .RowSourceType = "Value list"
    .RowSource = "-1; '<< Add a New Bookmark >>'"
End With

'-- Create the label for the bookmark combo.
Set ctlLabel = CreateControl(strTarget, acLabel, Me!cboSection, _
            Me!txtBMTName, , 60, 70, 1600, 300)
ctlLabel.Caption = "Bookmark(s):"

Set mdl = frmTarget.Module

Sub CopyObjectsToDatabase()

    Dim strTest As String

    '-- If the first module is not already in the target db,
    '   copy them all.

    On Error Resume Next
    strTest = _
     CurrentDb.Containers!Modules.Documents("clsBookmarkManagement").Name

    If Err.Number > 0 Then

        DoCmd.CopyObject CurrentDb.Name, "clsBookmarkManagement", _
acModule, "clsBookmarkManagement"
        DoCmd.CopyObject CurrentDb.Name, "clsBookmarkItems", _
acModule, "clsBookmarkItems"
        DoCmd.CopyObject CurrentDb.Name, "bmtRemoveBookmarks", _
acForm, "bmtRemoveBookmarks"

        SetHiddenAttribute acModule, "clsBookmarkItems", True
        SetHiddenAttribute acModule, "clsBookmarkManagement", True
        SetHiddenAttribute acForm, "bmtRemoveBookmarks", True

    End If

End Sub

Sub CreateDeclarationsArea(mdl As Module)

     mdl.AddFromString vbCrLf & _
        "'-- Declare a clsBookmarkManagement variable." & _
        vbCrLf & "Private bmtForm As clsBookmarkManagement"

  End Sub

Sub CreateFormLoadEvent(mdl As Module)

    Dim lngStartLine As Long, lngStartCol As Long
    Dim lngEndLine As Long, lngEndCol As Long
    Dim intIncrement As Integer

    mdl.Find "Form_Load", lngStartLine, lngStartCol, _
                                lngEndLine, lngEndCol

    If lngStartLine = 0 Then
        lngStartLine = mdl.CreateEventProc("Load", "Form")
    End If

    intIncrement = 2

    mdl.InsertLines lngStartLine + intIncrement, _
            "    ' -- Create an instance of the clsBookmarkManagement class"

    intIncrement = intIncrement + 1

    mdl.InsertLines lngStartLine + intIncrement, _
            "    Set bmtForm = New clsBookmarkManagement" & _
            vbCrLf

    intIncrement = intIncrement + 2

    mdl.InsertLines lngStartLine + intIncrement, _
            "    '-- Call the custom initialization routine, passing the                  
             necessary items."

    intIncrement = intIncrement + 1

    mdl.InsertLines lngStartLine + intIncrement, _
            "    bmtForm.InitBookmarks Me!" & _
            Me!txtBMTName & ", " & conQuotes & Me!cboField1 & _
            conQuotes & IIf(IsNull(Me!cboField2), "", ", " & _
            conQuotes & Me!cboField2) & conQuotes & vbCrLf

End Sub

Sub CreateComboAfterUpdateEvent(mdl As Module)

   Dim lngStartLine As Long

   lngStartLine = mdl.CreateEventProc("AfterUpdate", Me!txtBMTName)

   mdl.InsertLines lngStartLine + 2, _
      "    '-- Call the BookmarkAction method, for all actions."
   mdl.InsertLines lngStartLine + 3, _
      "    bmtForm.BookmarkAction"

End Sub

DoCmd.Close acForm, Me!lstForms, acSaveYes
DoCmd.Close acForm, Me.Name

Set frmTarget = Nothing
Set mdl = Nothing

If blnDesign Then
    DoCmd.OpenForm strTarget, acDesign
Else
    Application.VBE.MainWindow.Visible = False
End If

That's it for the Bookmark Tracker Wizard. I hope this gives you enough to start creating your own wizards and add-ins. It's time now to look at issues regarding creating your own code libraries.