When designing your own builder, you should be consistent with the builders that are part of 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.

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

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

   On Error GoTo SpecialEffect_Err
   DoCmd.OpenForm FormName:="frmSpecialEffect", _
                  WindowMode:=acDialog, _
                  OpenArgs:=strCurrentValue
   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
      DoCmd.Close acForm, "frmSpecialEffect"
   Else
      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:

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. It opens the form in Dialog mode, passing it the current value of the property as the OpenArgs value. Figure 30.1 shows the frmSpecialEffect form.

Figure 30.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 in 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 in the SpecialEffect function evaluates the value of the optSpecialEffect option button 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 of the builder 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.

Although you have seen the code behind the Click event of the OK and Cancel buttons on the frmSpecialEffect form, you have not learned about the design of the form or the idea behind this builder. Ordinarily, when the Special Effect property is set from the Property window, no wizard exists to assist with the process. Although the process of setting the Special Effect property is quite simple, the main problem is that it is difficult to remember exactly what each special effect looks like. The custom special effect builder is designed with this potential problem in mind. It enables users of the builder to see what each special effect looks like before deciding which effect to select.

The properties of the form are quite simple. The Modal property of the form is set to Yes. The record selectors, navigation buttons, and scroll bars have been removed. The AutoCenter property of the form has been set to True. Six text boxes have been added to the form. The special effect of each text box has been set to a different style. An option group has been added to the form. This group has a different value, depending on which option button was selected. The Default property of the OK command button has been set to Yes, making the OK button the default choice. The Cancel property of the Cancel command button was set to Yes, ensuring that if the user presses Esc, the code behind the Cancel button executes. The code behind the Click events of the OK and Cancel buttons was shown in the preceding section. Listing 30.2 shows one more piece of code that enhances this builder.

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

This subroutine is placed in the Load event of the builder form. It sets the value of the option group to the current value of the property (passed in as an OpenArg).

Although the frmSpecialEffect form is not particularly exciting, it illustrates quite well that you can design a form of any level of complexity to facilitate the process of setting a property value. So far, though, you have not provided an entry point to the builder. If you select the Special Effect property, no ellipsis appears. You do not yet have access to the builder.

Before you can use a builder, you must register it. You can register a builder in two ways:

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 30.1 describes these value names for the Registry subkey.

Table 30.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 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 CHAP30LIB.MDA in the folder c:AccessLibs:

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_MACHINESOFTWAREMicrosoftOffice8.0AccessWizardsProperty Wizards key, as shown in Figure 30.2.

   

   Figure 30.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 30.3.

    

    Figure 30.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 30.4.

    

    Figure 30.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).

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

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

Figure 30.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 (not 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 list, as shown in Figure 30.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 30.5. The completed Registry entries required to add the builder.

Figure 30.6. Using the custom builder.

Note

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

Figure 30.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. Creating a wizard generally involves a multi-page form and is usually responsible for creating database objects. Consider a wizard that creates a simple form. The wizard consists of two modal dialog boxes, shown in Figures 30.12 and 30.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 multi-page form and all the code that enables it to work are in the CHAP30LIB.MDA database on the accompanying CD-ROM.

Figure 30.12. Step 1 of the custom Form Wizard.

Figure 30.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 multi-page form gives the user the opportunity to choose 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 IfEnd 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.CloseEnd 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_ClickEnd Sub

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

The CreateCustomForm function, as seen in Listing 30.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 30.14 shows the completed Registry entry for the custom Form Wizard.

Figure 30.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 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_MACHINESOFTWAREMicrosoftOffice8.0AccessMenu Add-Ins. You can accomplish the registration process by modifying the Registry directly or by using the USysRegInfo table. Figure 30.15 shows the Registry with the correct entries to run the Form Wizard created earlier in this chapter. Figure 30.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 equals (=) 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 30.15. Registry entries for the menu add-in.

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