When designing your own builder, the design should be consistent with that of the builders included in Access. You therefore must learn about the standards for an Access builder. To design builders that are consistent with the built-in builders, keep a few guidelines in mind:

Now that you are familiar with some general design guidelines for builders, you are ready to design your first builder. What a builder does is completely up to your imagination. For illustration, this section begins with a simple builder that prompts users to select the special effect for a text box. Three overall steps are required to create the builder:

The following sections go over each of these steps in detail.

Writing a Builder Function

The builder function is the function Access calls each time the builder is launched. The function launches the builder form and then returns a value to the appropriate property. Listing 25.1 is an example of a builder function. It is located in CHAP25LIB.MDA in the basBuilders module on the accompanying CD-ROM.

Example 25.1. Creating a Builder Function

Function SpecialEffect(strObject As String, _
            strControl As String, _
            strCurrentValue As String)

   On Error GoTo SpecialEffect_Err

   'Open the special effect form, passing it the special
   'effect currently selected
   DoCmd.OpenForm FormName:="frmSpecialEffect", _
                  WindowMode:=acDialog, _
                  OpenArgs:=strCurrentValue

   'If the user selects a special effect and clicks OK, the
   'form remains open but hidden. Return a value based on
   'which special effect the user selected
   If SysCmd(acSysCmdGetObjectState, acForm, _
            "frmSpecialEffect") = acObjStateOpen Then
      Select Case Forms!frmSpecialEffect.optSpecialEffect.Value
         Case 1
            SpecialEffect = "Flat"
         Case 2
            SpecialEffect = "Raised"
         Case 3
            SpecialEffect = "Sunken"
         Case 4
            SpecialEffect = "Etched"
         Case 5
            SpecialEffect = "Shadowed"
         Case 6
            SpecialEffect = "Chiseled"
      End Select

      'Close the form when done
      DoCmd.Close acForm, "frmSpecialEffect"
   Else

      'If the user clicks cancel, return the original value
      'for the special effect
      SpecialEffect = strCurrentValue
   End If

SpecialEffect_Exit:
   Exit Function

SpecialEffect_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   Resume SpecialEffect_Exit
End Function

A builder function must receive three preset arguments and must return the value that will become the value for the property being set. The three preset arguments follow:

• strObject—The name of the table, query, form, report, or module on which the builder is operating

• strControl—The name of the control to which the property applies

• strCurrentValue—The current property value

Although the names of the arguments are arbitrary, their data types, positions, and content cannot be changed. Access automatically fills in the values for the three arguments.

The SpecialEffect function opens the form called frmSpecialEffect in Dialog mode, passing it the current value of the property as the OpenArgs value. Figure 25.1 shows the frmSpecialEffect form.

Figure 25.1. The Special Effect builder form.

The following code is located in the Click event of the cmdOkay command button on the form:

Private Sub cmdOK_Click()
   Me.Visible = False
End Sub

Notice that the code sets the Visible property of the form to False. The code placed behind the cmdCancel command button looks like this:

Private Sub cmdCancel_Click()
   DoCmd.Close
End Sub

This code closes the frmSpecialEffect form.

After the user clicks OK or Cancel, the code within the SpecialEffect function continues to execute. The function uses the SysCmd function to determine whether the frmSpecialEffect form is loaded. You also can use the user-defined IsLoaded function to accomplish this task. If the frmSpecialEffect form still is loaded, the user must have selected a special effect and clicked OK. Because the form still is open, the function can determine which option button the user selected.

The Case statement within the SpecialEffect function evaluates the value of the optSpecialEffect option group found on the frmSpecialEffect form. It sets the return value for the function equal to the appropriate string, depending on the option button that the user of the builder selects. If the user selects the second option button (with a value of 2), for example, the SpecialEffect function returns the string "Raised". After the option button value is evaluated and the return value is set, the frmSpecialEffect form no longer is needed, so it is closed.

If the user chooses Cancel from the frmSpecialEffect form, the SysCmd function returns False, and the return value of the SpecialEffect function is set equal to strCurrentValue, the original property value. The property value therefore is not changed.

Private Sub Form_Load()
   'Set the Value of the Option Group
   'To the Current Value of the Property
   Select Case Me.OpenArgs
         Case "Flat"
            Me.optSpecialEffect.Value = 1
         Case "Raised"
            Me.optSpecialEffect.Value = 2
         Case "Sunken"
            Me.optSpecialEffect.Value = 3
         Case "Etched"
            Me.optSpecialEffect.Value = 4
         Case "Shadowed"
            Me.optSpecialEffect.Value = 5
         Case "Chiseled"
            Me.optSpecialEffect.Value = 6
End Select
End Sub

Registering a Builder

Before you can use a builder, you must register it in one of two ways:

• Manually add the required entries to the Windows registry.

• Set up the library database so that the Add-in Manager can create the Windows registry entries for you.

Manually Adding Entries to the Windows Registry

Adding the required entries to the Windows registry involves four steps:

1. If no registry key exists for the property for which you are designing a builder, add the property as a subkey under Property Wizards.

2. Add an additional subkey for the builder.

3. Add four predefined registry values for the key.

4. Set the proper data value for each value name.

The four value names that must be created for the subkey are Can Edit, Description, Function, and Library. Table 25.1 describes these value names for the registry subkey.

Table 25.1. Values for the Registry Subkey

Value Name	Value Type	Purpose
Can Edit	DWORD	Allows the builder to operate on and modify an existing value
Description	String	Specifies a subkey description that appears in the dialog box, which is invoked automatically if more than one builder exists for a property
Function	String	Name of the builder function
Library	String	Name of the library containing the builder function

Now that you have an overview of the steps involved in the process, you are ready to walk through the steps in detail. The following steps set up the builder called SpecialEffect, which is contained in the library database CHAP25LIB.MDA in the folder c:My Libraries:

1. To invoke the Registry Editor, choose Start|Run from the task bar. Type regedit and click OK. This invokes the Registry Editor.

2. Locate the HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice10.0AccessWizardsProperty Wizards key, as shown in Figure 25.2.

   

   Figure 25.2. The Property Wizards Registry key.

3. Determine whether a subkey exists with the name of the property for which you are creating a builder (in this case, SpecialEffect). If so, skip to step 6.

4. Choose Edit|New|Key.

5. Type the property name as the name for the new key (in this case, SpecialEffect).

6. With the new key selected, choose Edit|New|Key again.

7. Type a descriptive name for your builder (in this case, SpecialEffectBuilder).

8. Choose Edit|New|DWORD Value.

9. Type Can Edit as the value name.

10. Choose Edit|New|String Value.

11. Type Description as the value name.

12. Choose Edit|New|String Value.

13. Type Function as the value name.

14. Choose Edit|New|String Value.

15. Type Library as the value name.

16. Double-click the Can Edit value name. The Edit DWORD Value dialog box appears, as shown in Figure 25.3.

    

    Figure 25.3. The Edit DWORD Value dialog box.

17. Enter 1 for the Value data and click OK.

18. Double-click the Description value name. The Edit String dialog box appears, as shown in Figure 25.4.

    

    Figure 25.4. The Edit String dialog box.

19. Enter the description you want the user of the builder to see if more than one builder is assigned to the property (in this case, Special Effect Builder). Click OK.

20. Double-click the Function value name. Enter the name of the builder function (in this case, SpecialEffect). Click OK.

21. Double-click the Library value name. Enter the name and location of the library database (in this case, C:My Librarieschap25lib.mda). You do not have to enter the path if the library is located in the Access folder.

Figure 25.5 shows the completed registry entries. The builder now should be ready to use. To test the builder, you need to exit and relaunch Access. If all the registry entries are created successfully, you can use the builder. To test the builder, open any database (other than the library database), create a new form, and add a text box. Select Special Effect from the Format tab of the Properties window. An ellipsis appears to the right of the Special Effect drop-down arrow, as shown in Figure 25.6. If you click the ellipsis, the builder form appears. Select a special effect and click OK. The special effect you selected now appears in the Special Effect property.

Figure 25.5. The completed registry entries required to add the builder.

Figure 25.6. Using the custom builder.

Note

If you do not exactly follow the format for the value names, the message Invalid add-in entry for 'SpecialEffectBuilder' appears, as shown in Figure 25.7. You must correct the registry entry.

Figure 25.7. This error message appears if the registry entry is invalid.

Wizard design guidelines are almost identical to builder design guidelines. The main difference is that wizards generally present the user with multiple modal dialog boxes, whereas a builder generally consists of a single modal dialog box. All the data requirements for the wizard must be met before the user can close the last dialog box.

Creating a wizard is more complex than creating a builder. A wizard generally requires a multipage form and code that creates database objects. Consider a wizard that creates a simple form. The wizard comprises two modal dialog boxes, shown in Figures 25.12 and 25.13. The first dialog box asks the user for a form caption, form name, and message to appear on the new form. The second dialog box enables the user to add OK and Cancel buttons to the form. The multipage form and all the code that enables it to work are in the CHAP25LIB.MDA database on the accompanying CD-ROM.

Figure 25.12. Step 1 of the custom Form Wizard.

Figure 25.13. Step 2 of the custom Form Wizard.

Each page of the wizard contains code to ensure that it operates successfully. The form is called frmGetFormInfo. The first page of this multipage form gives the user the opportunity to choose the next action: Cancel, Next, or Finish. The code for the Cancel button looks like this:

Private Sub cmdCancel1_Click()
   DoCmd.Close
End Sub

This code closes the wizard form. No other actions are taken because the process is being canceled. If the user clicks Next, this code executes:

Private Sub cmdNext1_Click()
   DoCmd.GoToPage 2
   Me.Caption = "My Form Wizard - Step 2"
End Sub

This code moves to the second page of the form and changes the caption of the form to indicate that the user is on step 2 of the wizard. The code under the Finish button looks like this:

Private Sub cmdFinish1_Click()
   If CreateCustomForm() Then
      MsgBox "Form Created Successfully"
      DoCmd.Close
   Else
      MsgBox "Unable to Create Form"
   End If
End Sub

This code calls a function called CreateCustomForm, which is responsible for building the actual form. The details of the CreateCustomForm function are discussed later in this section. If the function returns True, the wizard form is closed, and a message is displayed indicating that the process was successful. Otherwise, a message is displayed indicating that the form was not created successfully, and the user remains in the wizard. The second page of the form contains similar subroutines. The code under the Back button looks like this:

Private Sub cmdBack2_Click()
   DoCmd.GoToPage 1
   Me.Caption = "My Form Wizard - Step 1"
End Sub

This code moves back to the first page of the form. If the user chooses Cancel, this code executes:

Private Sub cmdCancel2_Click()
   DoCmd.Close
End Sub

This code closes the form, taking no further action. If the user clicks Finish, the Click event code of the cmdFinish2 command button executes:

Private Sub cmdFinish2_Click()
   Call cmdFinish1_Click
End Sub

This code calls the code under the Click event of the cmdFinish1 command button.

The CreateCustomForm function (located in the basWizards module of the library database), as seen in Listing 25.3, contains the code that actually builds the new form.

Function CreateCustomForm() As Boolean

   On Error GoTo CreateCustomForm_Err

   Dim frmNew As Form
   Dim ctlNew As Control

   'Create a New Form and Set Several of Its Properties
   Set frmNew = CreateForm()
   frmNew.Caption = Forms!frmGetFormInfo.txtFormCaption
   frmNew.RecordSelectors = False
   frmNew.NavigationButtons = False
   frmNew.AutoCenter = True

   'Create a Label Control on the New Form
   'Set Several of Its Properties
   Set ctlNew = CreateControl(frmNew.Name, acLabel)
   ctlNew.Caption = Forms!frmGetFormInfo.txtLabelCaption
   ctlNew.Width = 3000
   ctlNew.Height = 1000
   ctlNew.Top = 1000
   ctlNew.Left = 1000

   'Evaluate to See if the User Requested an OK Command Button
   'If They Did, Add the Command Button and Set Its Properties
   'Add Click Event Code for the Command Button
   If Forms!frmGetButtons.chkOK.Value = –1 Then
      Set ctlNew = CreateControl(frmNew.Name, acCommandButton)
      ctlNew.Caption = "OK"
      ctlNew.Width = 1000
      ctlNew.Height = 500
      ctlNew.Top = 1000
      ctlNew.Left = 5000
      ctlNew.Name = "cmdOK"
      ctlNew.Properties("OnClick") = "[Event Procedure]"
      frmNew.Module.InsertText "Sub cmdOK_Click()" & vbCrLf & _
         vbTab & "DoCmd.Close acForm, """ & _
         Forms!frmGetFormInfo.txtFormName & _
         """" & vbCrLf & "End Sub"
   End If

   'Evaluate to See if the User Requested a Cancel Command Button
   'If They Did, Add the Command Button and Set Its Properties
   'Add Click Event Code for the Command Button
   If Forms!frmGetButtons.chkCancel.Value = –1 Then
      Set ctlNew = CreateControl(frmNew.Name, acCommandButton)
      ctlNew.Caption = "Cancel"
      ctlNew.Width = 1000
      ctlNew.Height = 500
      ctlNew.Top = 2000
      ctlNew.Left = 5000
      ctlNew.Name = "cmdCancel"
      ctlNew.Properties("OnClick") = "[Event Procedure]"
      frmNew.Module.InsertText "Sub cmdCancel_Click()" & vbCrLf & _
         vbTab & "MsgBox(""You Canceled!!"")" & vbCrLf & "End Sub"
   End If

   'If the User Entered a Form Name, Save the Form
   If Not IsNull(Forms!frmGetFormInfo.txtFormName) Then
      DoCmd.Save , Forms!frmGetFormInfo.txtFormName
   End If

   'Return True If No Errors
   CreateCustomForm = True
   Exit Function

CreateCustomForm_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   CreateCustomForm = False
   Exit Function
End Function

The code begins by creating both form and control object variables. The form object variable is set to the return value from the CreateForm function. The CreateForm function creates a new form object. Several properties of the new form object are set: Caption, RecordSelectors, NavigationButtons, and AutoCenter. Next, the function uses the CreateControl function to create a new label. A reference to the new label is called ctlNew. The Caption, Width, Height, Top, and Left properties of the new label are set. If the user indicated that he or she wanted an OK button, a new command button is created. The Caption, Width, Height, Top, Left, Name, and Properties properties are all set. The InsertText method is used to insert code for the Click event of the command button. If the user requested a Cancel button, the same properties are set. Finally, if the user indicated a name for the new form, the Save method is used to save the new form object.

Like a builder, a wizard needs to be added to the Windows registry before it can be used. You can do this by modifying the registry directly or by adding entries to the USysRegInfo table. Figure 25.14 shows the completed registry entry for the custom Form Wizard.

Figure 25.14. Registry entries for the custom Form Wizard.

Notice that the function name is MyCustomForm. This is the entry point to the wizard. The Library key designates the name of the library add-in database containing the entry point function. The Description key specifies what appears in the New Object dialog box. Finally, the Index key designates the order in which the wizard is displayed in the list in the New Object dialog box. The MyCustomForm function, located in the basWizards module, simply calls the frmGetFormInfo form, initiating the wizard process:

Function MyCustomForm(strRecordSource As String) As Variant
   DoCmd.OpenForm FormName:="frmGetFormInfo", WindowMode:=acDialog
End Function

Menu add-ins are available to the user whenever the Tools menu is available. Menu add-ins are not context sensitive like wizards and builders. Therefore, they should in no way rely on what the user is doing at a particular moment.

Creating a menu add-in is just like creating a wizard. The difference is in how you install the add-in. The menu add-in must be registered under HKEY_LOCAL_MACHINESOFTWARE MicrosoftOffice10.0AccessMenu Add-Ins. You can accomplish the registration process by modifying the registry directly or by using the USysRegInfo table. Figure 25.15 shows the registry with the correct entries to run the Form Wizard, created earlier in this chapter, as an add-in. Figure 25.16 shows how you can automate the registration process by using the USysRegInfo table. Three entries are included in the USysRegInfo table. All three entries designate the proper place in the registry tree to add the new key. The first entry contains the subkey and a type of zero. The second entry contains the value name Expression and the name of the entry point function as the value. Notice that the expression name is preceded by an equal sign (=) and is followed by parentheses. The quotation marks within the parentheses are required because this particular entry-point function requires an argument. The third and final entry contains the value name Library and the name of the library as the value. This is all you need to do to turn a wizard into a menu add-in.

Figure 25.15. Registry entries for the menu add-in.

Figure 25.16. The USysRegInfo entries for the menu add-in.

Designing Your Own Add-Ins

The types of builders, wizards, and menu add-ins that you create depend on your specific needs. To reinforce what you have learned, this section includes the step-by-step process for creating a builder to help you add validation text messages. When you invoke the builder, the Choose Builder dialog box shown in Figure 25.17 appears. This dialog box appears because you will design two builders: one that enables the user to select from a list of polite messages and another that enables the user to select from rude messages. If the user selects Polite Validation Text Builder, the dialog box in Figure 25.18 appears. If the user selects Rude Validation Text builder, the dialog box in Figure 25.19 appears.

Figure 25.17. The Choose Builder dialog box.

Figure 25.18. The polite messages builder.

Figure 25.19. The rude messages builder.

The first entry-point function is located in basBuilders and is shown in Listing 25.4.

Example 25.4. The First Entry-Point Function

Function ValidTextPolite(strObject As String, _
            strControl As String, _
            strCurrentValue As String)

   On Error GoTo ValidTextPolite_Err

   'Open the Builder form
   DoCmd.OpenForm FormName:="frmPolite", _
                  WindowMode:=acDialog, _
                  OpenArgs:=strCurrentValue

   'If the user selected a message and clicked OK,
   'return the selected text to the caller
   If SysCmd(acSysCmdGetObjectState, acForm, _
            "frmPolite") = acObjStateOpen Then
      Select Case Forms!frmPolite.optPolite.Value
         Case 1
            ValidTextPolite = "The Incorrect Value Was Entered"
         Case 2
            ValidTextPolite = "The Computer Cannot Comprehend
 Your Entry"
         Case 3
            ValidTextPolite = "I'm Sorry, Could You Please Try Again"
         Case 4
            ValidTextPolite = "Please Make Another Selection"
         Case 5
            ValidTextPolite = "Amount Too High"
         Case 6
            ValidTextPolite = "Amount Too Low"
      End Select

      'Close the form
      DoCmd.Close acForm, "frmPolite"

   'If the user clicked cancel, return the original value
   Else
      ValidTextPolite = strCurrentValue
   End If

ValidTextPolite_Exit:
   Exit Function

ValidTextPolite_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   Resume ValidTextPolite_Exit
End Function

The ValidTextPolite function receives all the parameters required by a builder function. The function opens frmPolite modally, passing it the current validation text value of the selected control as the OpenArg. If the user selects a value from the frmPolite form and clicks OK, the selected value is evaluated, and the appropriate text is returned from the ValidTextPolite function. The return value becomes the validation text of the selected control. Listing 25.5 shows the Load event of frmPolite.

Example 25.5. The Load Event of frmPolite

Private Sub Form_Load()
   'Set the Value of the Option Group
   'To the Current Value of the Property
   Select Case Me.OpenArgs
         Case "The Incorrect Value Was Entered"
            Me.optPolite.Value = 1
         Case "The Computer Cannot Comprehend Your Entry"
            Me.optPolite.Value = 2

         Case "I'm Sorry, Could You Please Try Again"
            Me.optPolite.Value = 3
         Case "Please Make Another Selection"
            Me.optPolite.Value = 4
         Case "Amount Too High"
            Me.optPolite.Value = 5
         Case "Amount Too Low"
            Me.optPolite.Value = 6
      End Select

End Sub

This code ensures that the value of the option button on the frmPolite form reflects the text that currently is entered in the Validation Text property of the current control. The ValidTextRude entry-point function is similar to ValidTextPolite. Listing 25.6 shows the ValidTextRude entry-point text function; it is located in basBuilders.

Example 25.6. The ValidTextRude Entry-Point Function

Function ValidTextRude(strObject As String, _
            strControl As String, _
            strCurrentValue As String)

   On Error GoTo ValidTextRude_Err
   'Open the Builder form
   DoCmd.OpenForm FormName:="frmRude", _
                  WindowMode:=acDialog, _
                  OpenArgs:=strCurrentValue

   'If the user selected a message and clicked OK,
   'return the selected text to the caller
   If SysCmd(acSysCmdGetObjectState, acForm, _
            "frmRude") = acObjStateOpen Then
      Select Case Forms!frmRude!optRude.Value
         Case 1
            ValidTextRude = "Get a Clue Dude"
         Case 2
            ValidTextRude = "What the Heck do You Think You're
 Doing?"
         Case 3
            ValidTextRude = "Give Me a Break!!"
         Case 4
            ValidTextRude = "I'm a Computer, I'm not an Idiot!!"
         Case 5
            ValidTextRude = "Read the Manual Dude"
         Case 6
            ValidTextRude = "You Really Think I Believe That?"
      End Select

      'Close the form
      DoCmd.Close acForm, "frmRude"

   'If the user clicked cancel, return the original value
   Else
      ValidTextRude = strCurrentValue
   End If

ValidTextRude_Exit:
   Exit Function

ValidTextRude_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   Resume ValidTextRude_Exit
End Function

The Load event of frmRude is similar to the Load event of frmPolite, as Listing 25.7 shows.

Example 25.7. The Load Event of frmRude

Private Sub Form_Load()
   'Set the Value of the Option Group
   'To the Current Value of the Property
   Select Case Me.OpenArgs
         Case "Get a Clue Dude!"
            Me.optRude.Value = 1
         Case "What the Heck Do You Think You're Doing"
            Me.optRude.Value = 2
         Case "Give Me a Break!!"
            Me.optRude.Value = 3
         Case "I'm a Computer, I'm not an Idiot!!"
            Me.optRude.Value = 4
         Case "Read the Manual Dude"
            Me.optRude.Value = 5
         Case "You Really Think I Believe That?"
            Me.optRude.Value = 6
      End Select

End Sub

To create the builder, design both forms so that they look like the ones in Figures 25.18 and 25.19. Include code for the Load event of each form as listed previously. The code behind the OK button of each form sets the Visible property of the form to False. The code behind the Cancel button on each form closes the form. Make sure that you name the option groups optPolite and optRude so that the code runs properly for each form. You can place the two entry-point functions, ValidTextPolite and ValidTextRude, in any code module in the library database. The last step involves registering the two builders. The entries in USysRegInfo, shown in Figure 25.20, accomplish the task of registering the builder the first time that the add-in is selected through the Add-ins dialog box. This table is located in CHAP25LIB.MDA.

Figure 25.20. Registry entries for the polite and rude builders.