| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
Add-ins are tools that extend the functionality of Access. They enhance the Access environment by making difficult tasks easier, automating repetitive tasks, and adding enhanced functionality. You can design add-ins for yourself or for others within your organization to use. You might even want to distribute add-ins as part of your application so that your users can build their own database objects. If you are really ambitious, you might decide to build an add-in for sale in the Access third-party market.
Microsoft Access supports three different types of add-ins: builders, wizards, and menu add-ins. Each one has its own advantages and uses. When you begin the process of designing an add-in, you must decide whether it will be a builder, wizard, or menu add-in. This decision affects how you design the add-in as well as how you install it. This chapter defines and shows you how to design and install each type of add-in.
A builder is an add-in that assists the user in constructing an expression or another data element. Builders are most often used to assist with the process of filling in a property of a database object. They generally consist of a single dialog that appears when the user clicks on the ellipse to the right of the property in the Property sheet. An example of a builder is the Expression Builder that appears when setting the control source of a text box on a form. Access supports three types of builders:
A wizard is a series of dialog boxes that provides a step-by-step interface to creating a database object. The wizard shields the user from the complexities of the process. Examples of wizards that you are probably familiar with include the Form Wizard, the Report Wizard, and the Database Wizard. Access 95 supports the development of several types of custom wizards:
A menu add-in is a general-purpose tool that enables you to perform a task that generally affects multiple objects or Access itself. The Database Splitter and Database Documentor are examples of menu add-ins. Menu add-ins are accessed through the Add-ins submenu of the Tools menu.
When designing your own builder, you should be consistent with the builders that are part of Access. Therefore, you 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, let's begin with a simple builder that prompts the user to select the special effect for a text box. Three overall steps are required to create the builder:
Let's go over each of these steps in detail.
The builder function is the function that Access calls each time the builder is launched. It launches the builder form and then returns a value to the appropriate property. The following is an example of a builder function. It can be found in CHAP28LIB.MDA in the basBuilders module.
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 are as follows:
Although the names of the arguments are arbitrary, their data types, positions, and what they contain 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. The frmSpecialEffect form is shown in Figure 28.1. The following code is found in the Click event of the cmdOkay command button on the form:
Private Sub cmdOK_Click() Me.Visible = False End Sub
Figure 28.1. The Special Effect builder form.
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 has clicked on 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 could also use the user-defined IsLoaded function to accomplish this task. If the frmSpecialEffect form is still loaded, the user must have selected a special effect and clicked OK. Because the form is still 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 button found on frmSpecialEffect. 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. For example, if the user of the builder selects the second option button (with a value of 2), the SpecialEffect function returns the string "Raised". After the option button value has been evaluated and the return value is set, the frmSpecialEffect form is no longer needed, so it is closed.
If the user selects Cancel from the frmSpecialEffect form, the SysCmd function returns a False, and the return value of the SpecialEffect function is set equal to strCurrentValue, the original property value. Hence, the property value 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 allows the user 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 scrollbars 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 has been 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 has been set to Yes, ensuring that if the user presses the Escape key, the code behind the Cancel button is executed. The code behind the Click events of the OK and Cancel buttons is listed in the previous section. One more piece of code 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 that will 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:
Adding the required entries to the Windows Registry involves four steps:
The four value names that must be created for the subkey are Can Edit, Description, Function, and Library. The value names, value types, and purposes are listed in Table 28.1.
Value Name |
Value Type |
Purpose |
|
Can Edit |
DWORD |
Allows the builder to operate on and modify an existing value |
|
Description |
String |
Description that appears in the dialog that is automatically invoked if more than one builder exists for a property |
|
Function |
String |
The name of the builder function |
|
Library |
String |
The 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, contained within the library database CHAP28LIB.MDA found in the folder c:\AccessLibs.
Figure 28.2. The Property Wizards Registry key.
Figure 28.3. The Edit DWORD Value dialog.
Figure 28.4. The Edit String Value dialog.
The completed Registry entries appear in Figure 28.5. The builder should now be ready to use. To test the builder, you need to exit and relaunch Access. If all the Registry entries were successfully created, 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 Properties window. An ellipsis should appear (see Figure 28.6). If you click on the ellipsis, the builder form should appear. Select a special effect and click OK. The special effect you selected should now appear within the Special Effect property.
Figure 28.5. The completed Registry entries required to add the builder.
Figure 28.6. Using the custom builder.
If you do not follow exactly the format for the value names, the message Invalid add-in entry for 'SpecialEffectBuilder' appears (see Figure 28.7). You must correct the Registry entry.
Figure 28.7. This error message appears when the Registry entry is invalid.
The alternative to editing the Windows Registry manually is to set up the library database so that the Add-In Manager can create the Registry entries for you. This involves adding a table to the library database. The table must be called USysRegInfo. Here are the steps involved:
Figure 28.8. The Tables tab with system objects visible.
Figure 28.9. The Import Object dialog enables you to easily add the USysRegInfo table to your library database.
Figure 28.10. The completed table with entries for Registry.
Figure 28.11. The Add-In Manager dialog.
Field Name |
Description |
|
SubKey |
The name of the subkey value in the Registry where the value you are adding is located. |
|
Type |
The type of subkey value you are creating (String, Binary, or DWORD). |
|
ValName |
The value name for the entry. |
|
Value |
The value associated with the value name. |
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. All the data requirements for the wizard must be met before the user can close the last dialog.
The process of creating a wizard is more complex than creating a builder. This is due to the fact that it generally involves more than one form and is generally responsible for creating database objects. Consider a wizard that creates a simple form. The wizard comprises two modal dialog boxes, shown in Figures 28.12 and 28.13. The first dialog asks the user for a form caption, form name, and message to appear on the new form. The second dialog allows the user to add OK and Cancel buttons to the form. These forms, and all the code that allows them to work, are in the CHAP28LIB.MDA database.
Figure 28.12. Step 1 of the Custom Form Wizard.
Figure 28.13. Step 2 of the Custom Form Wizard.
Each form in the wizard contains code that ensures that it operates successfully. The first form is called frmGetText. The user has the opportunity to select Cancel, Next, or Finish from this form. The code for the Cancel button looks like this:
Private Sub cmdCancel_Click() DoCmd.Close acForm, "frmGetButtons" DoCmd.Close acForm, "frmGetText" End Sub
This code closes both the frmGetButtons and frmGetText forms. No other actions are taken because the process is being canceled. If the user clicks Next, the following code executes:
Private Sub cmdNext_Click() Me.Visible = False DoCmd.OpenForm FormName:="frmGetButtons", WindowMode:=acDialog End Sub
This code hides the frmGetText form and opens the frmGetButtons form. The frmGetText form must remain open because information that it contains is used to create the new form. By making it invisible, you can open a second form modally. The code under the Finish button looks like this:
Private Sub cmdFinish_Click()
If CreateCustomForm() Then
MsgBox "Form Created Successfully"
DoCmd.Close acForm, "frmGetButtons"
DoCmd.Close acForm, "frmGetText"
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 a True, both frmGetText and frmGetControls are 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 frmGetControls form contains similar subroutines. The code under the Back button looks like this:
Private Sub cmdBack_Click() Me.Visible = False Forms!frmGetText.Visible = True End Sub
This code hides the frmGetControls form and shows the frmGetText form. If the user selects Cancel, the following code executes:
Private Sub cmdCancel_Click() DoCmd.Close acForm, "frmGetText" DoCmd.Close acForm, "frmGetButtons" End Sub
This code closes both the frmGetText and frmGetButtons forms, taking no further action. If the user clicks Finish, the Click event code of the cmdFinish command button executes:
Private Sub cmdFinish_Click()
If CreateCustomForm() Then
MsgBox "Form Created Successfully"
DoCmd.Close acForm, "frmGetText"
DoCmd.Close acForm, "frmGetButtons"
Else
MsgBox "Unable to Create Form"
End If
End Sub
This code calls the CreateCustomForm function. If the function returns a True, a message indicating success displays and both wizard forms are unloaded. Otherwise, a message displays indicating that the process did not complete successfully and the user remains in the wizard.
There are actually three methods that you can use to implement the previously mentioned functionality. Instead of using two forms to gather information required by the wizard, the wizard could have been implemented using either a multipage form or a form with subforms.
The CreateCustomForm function contains the code that actually builds the new form. It looks like this:
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 It's Properties
Set frmNew = CreateForm()
frmNew.Caption = Forms!frmGetText.txtFormCaption
frmNew.RecordSelectors = False
frmNew.NavigationButtons = False
frmNew.AutoCenter = True
'Create a Label Control on the New Form
'Set Several of It's Properties
Set ctlNew = CreateControl(frmNew.Name, acLabel)
ctlNew.Caption = Forms!frmGetText.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 It's 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!frmGetText.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 It's 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!frmGetText.txtFormName) Then
DoCmd.Save , Forms!frmGetText.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.
Several functions exist to create and delete forms, reports, form controls, and report controls. DAO code can be used to create, modify, and delete tables and queries. Using the functions and DAO code, you can manipulate database objects any way you like.
Like a builder, a wizard needs to be added to the Windows Registry before it can be used. This can be accomplished by modifying the Registry directly or by adding entries to the USysRegInfo table. Figure 28.14 shows the completed Registry entry for the Custom Form Wizard.
Figure 28.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 is used to designate the name of the library add-in database containing the entry point function. The Description key is used to specify what appears in the New Object dialog. Finally, the Index key is used to designate the order in which the wizard is displayed in the list in the New Object dialog.
Function MyCustomForm(strRecordSource As String) As Variant DoCmd.OpenForm FormName:="frmGetText", WindowMode:=acDialog End Function
The MyCustomForm function simply calls the frmGetText form, initiating the wizard process.
Menu add-ins are available to the user whenever the Tools menu is available. This means that they are not context-sensitive like wizards and builders. They should therefore 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 lies in how you install the add-in. The menu add-in must be registered under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Menu Add-Ins. The registration process can be accomplished by modifying the Registry directly or by using the USysRegInfo table. Figure 28.15 shows the Registry with the correct entries to run the Form Wizard created earlier in this chapter. Figure 28.16 shows how the registration process can be automated 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 of "Expression" and the name of the entry point function as the value. Notice that the Expression name is proceeded 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 of "Library" and the name of the library as the value. This is all that needs to be done to turn a wizard into a menu add-in.
Figure 28.15. Registry entries for the menu add-in.
Figure 28.16. The USysRegInfo entries for the menu add-in.
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 that can assist you with adding validation text messages. When you invoke the builder, the Choose Builder dialog shown in Figure 28.17 appears. This dialog appears because you will design two builders, one that allows the user to select from a list of polite messages and another that allows the user to select from rude messages. If the user selects Polite Validation Text Builder, the dialog shown in Figure 28.18 appears. If the user selects Rude Validation Text builder, the dialog shown in Figure 28.19 appears.
Figure 28.17. The Choose Builder dialog.
Figure 28.18. The polite messages builder.
Figure 28.19. The rude messages builder.
The first entry-point function is found in basBuilders. It looks like this:
Function ValidTextPolite(strObject As String, _
strControl As String, _
strCurrentValue As String)
On Error GoTo ValidTextPolite_Err
DoCmd.OpenForm FormName:="frmPolite", _
WindowMode:=acDialog, _
OpenArgs:=strCurrentValue
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
DoCmd.Close acForm, "frmPolite"
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 value he or she selected is evaluated and the appropriate text is returned from the ValidTextPolite function. The return value is the value that becomes the validation text of the selected control. The Load event of frmPolite contains the following code:
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 is currently entered in the Validation Text property of the current control. The ValidTextRude entry point function is similar to ValidTextPolite. It looks like this and is contained within basBuilders:
Function ValidTextRude(strObject As String, _
strControl As String, _
strCurrentValue As String)
On Error GoTo ValidTextRude_Err
DoCmd.OpenForm FormName:="frmRude", _
WindowMode:=acDialog, _
OpenArgs:=strCurrentValue
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
DoCmd.Close acForm, "frmRude"
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. It looks like this:
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 Figures 28.18 and 28.19. Include code for the Load event of each form as previously listed. 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. The two entry point functions, ValidTextPolite and ValidTextRude, can be placed in any code module in the library database. The last step involves registering the two builders. The entries in USysRegInfo, shown in Figure 28.20, accomplish the task of registering the builder the first time the add-in is selected through the Add-Ins dialog. This table can be found in CHAP28LIB.MDA.
By creating builders, wizards, and add-ins, you can enhance the development environment for yourself and your users. You can even add wizards so that your users can build their own queries, forms, or reports on the fly without a full copy of Access. Your wizard simply needs to prompt the user for the appropriate information and then build the objects to your specifications. What you can do with wizards, builders, and add-ins is limited only by your imagination.