Writing Your Add-in

Now that you know the type of add-in you are creating and the functionality it will provide, you can begin to write your add-in. Writing the add-in is much like writing any other Microsoft Access application. You create a new database, create and modify objects, write the Visual Basic code to define the actions your add-in will perform, and implement error handling.

Creating the New Database

You build your add-in as its own database. If you plan on installing your add-in in the Office folder, make sure its name doesn’t duplicate the name of an existing add-in. If your add-in will be installed in another folder, naming is not an issue.

Creating the Add-in’s Objects

As you create the objects for your add-in, keep in mind that they will be used on computers that may be different from yours. To make sure that your add-in will run well in all possible situations, try to anticipate both the low-end and high-end computers that may be used to run your add-in.

For example, a form that barely fits on a screen with 1024 x 768 resolution will be difficult to work with on a screen with 640 x 480 resolution. Test your form in the lowest resolution you plan to support and make sure it fits easily within the Microsoft Access window. Design your form by using a 16-color palette and avoid using colors on buttons that don’t display well on monochrome monitors.

If your add-in requires tables, create the tables in the new database. Don’t link tables, because linking requires more files in the distribution disk set and can cause problems if the user moves the database files to different folders.

As with other Microsoft Access applications, the foundation of an add-in is the form. As you design your forms, remember the guidelines discussed earlier in this chapter. Try to implement as much of your Visual Basic code as possible in the form modules. If your add-in is complex, involving many sequential operations, you may find it useful to break down operations in modular units. Implement each unit in a separate form, designed as a dialog box, with execution started by the OnOpen or OnLoad events of the form. This makes a complex add-in easier to maintain and enhance.

Using Functions, Statements, and Methods to Create Objects

Most wizards create user-interface objects, such as forms, reports, and controls, or Data Access Objects (DAO), such as tables or queries. Visual Basic provides a variety of functions, statements, and methods you can use to create and work with objects and controls. You can use them from any kind of add-in, but in most cases they are only appropriate for wizards because they must be used in an object’s Design view.

The following table summarizes the functions you can use to create user-interface objects.

Function	Description

CreateForm	Creates a form, and then returns a Form object.
CreateControl	Creates a control on a specified form that is open in Design view, and then returns a Control object.
CreateReport	Creates a report, and then returns a Report object.
CreateReportControl	Creates a control on a specified report that is open in Design view, and then returns a Control object.
CreateGroupLevel	Creates a new group level for a specified report that is open in Design view, and then returns the new group’s index value (0–9). You specify how the group will be sorted or grouped, by specifying a field or expression.

Because these functions return an object, you can set an object variable when you create the new object. For example, the following line of code creates a new text box control on the current form:

Set ctlNewText = CreateControl(frmCurrentForm.Name,acTextBox)

You can then set the properties of the control, such as the Left, Top, Width, and Height properties, by using the ctrlNewText object variable in code.

The following table summarizes the statements you can use to delete controls.

Statement	Description

DeleteControl	Deletes a specified control on a form that is open in Design view.
DeleteReportControl	Deletes a specified control on a report that is open in Design view.

The following table summarizes the DAO methods you can use to create databases, tables, queries, and user and group security accounts.

Method	Description

CreateDatabase	Creates a new Database object.
CreateTableDef	Creates a new TableDef object that is used to represent a table or linked table. Use the Append method to add the new TableDef object to the TableDefs collection.
CreateField	Creates a new Field object. Use the Append method to add the Field object to an existing TableDef object.
CreateIndex	Creates a new Index object. Use the Append method to add the index to the Indexes collection of an existing TableDef object.
CreateQueryDef	Creates a new QueryDef object that is used to represent a query. The resulting QueryDef object is automatically appended (added) to the QueryDefs collection.
CreateRelation	Creates a new Relation object that is used to represent relationships between fields in tables or queries.
CreateWorkspace	Creates a new Workspace object that defines a session for the Microsoft Jet database engine. This is only used to create a new session in addition to the current (default) session.
CreateUser	Creates a new User object to add a new user account to the Users collection of a secured database. Use the Append method to add a new User object to the Users collection of the database.
CreateGroup	Creates a new Group object to add a new security group to the Groups collection of a secured database. Use the Append method to add the new Group object to the Groups collection of the database.
DeleteObject	A method of the DoCmd object used to delete a specified database object.

See Also For more information on these functions, statements, and methods, search the Help index for the name of the function, statement, or method.

Note You can create a wizard or builder that edits existing controls as well as creates new controls. The user can invoke your wizard or builder by right-clicking on a control of the appropriate type, and then clicking Build on the shortcut menu. You can work with new or existing controls by using the properties of the Control object. For example, you can determine the type of an existing control by checking the ControlType property. For more information on the Control object, search the Help index for “Control object.”

Implementing Error Handling

Error handling is very important in an add-in. Because add-ins are stored in their own database, run-time errors that occur trigger the Microsoft Access error message “A run-time error has occurred in database: <name>. You don’t have permission to view modules in this database.” This is confusing to the user, and it detracts from the usability of your add-in. Additionally, because your add-in may be distributed to users at sites other than your own, you may not be able to reproduce the user’s configuration on your system. This makes it very difficult to troubleshoot add-ins.

Microsoft Access has a number of tools you can use in your modules to recover from run-time errors. This section outlines strategies for effective error handling in add-ins.

See Also For more information on error handling, see Chapter 8, “Handling Run-Time Errors.”

The standard method of error handling involves creating error-handling code in each of your procedures. When a run-time error occurs, the error-handling code takes over. This allows the procedure to retry operations or otherwise recover from the error.

You can augment this error handling with public error-handling code. Public error-handling code is called whenever your add-in can’t recover from a run-time error. In public error handling, you define an error-handling procedure in a module and make its scope public. At a minimum, this procedure should contain the code to do the following:

Supply the user with information about the error. You can display the Microsoft Access error number and the value of the Description property of the Err object to give the user details about what went wrong.
Close all objects that your add-in opened. You want to leave the user’s copy of Microsoft Access in the same state it was in before your add-in started.
Delete any temporary files or objects your add-in created. It’s a good idea to clean up your add-in in the event of an unrecoverable run-time error.
Restore any user option settings your add-in has changed. For example, if your add-in changes the Show Status Bar setting, have the error-handling procedure restore the user’s Show Status Bar setting.

You may want to implement additional functionality in your public error handler. The following suggestions are optional, but they may make it easier for you to solve problems at a remote site:

Replace Microsoft Access error messages with custom error messages. The standard Microsoft Access error messages may be confusing to the users of your add-in. By anticipating areas in which errors may be encountered, you can display error messages that supply more meaningful information to the user.
Have your public error-handling code write pertinent information to a log file when it can’t recover. This information can include the current state of Microsoft Access (including the version number), information about the system’s environment, a list of objects that were open when the error occurred, and any other information to help you track down the source of the problem. This error log can be stored in a Microsoft Access table or in a separate text file. In many situations, you’ll find storing the error log in a text file to be the better approach. For example, if your public error-handling code was triggered by a serious error, you may not be able to open and write to tables in your code. Using a text file is usually more successful in these situations.
Display the name of the procedure that was running when the run-time error occurred. Although Microsoft Access doesn’t provide intrinsic capabilities for retrieving the current procedure name, you can implement this feature as described in the following section.

Implementing a Public Error Procedure Stack

If you want your public error-handling code to provide the user with the name of the procedure that was running when an error occurs, you can implement a procedure stack in Visual Basic and include it in your application. You implement the stack as a public array. In every procedure, you call a procedure that stores the name of the current procedure on this stack. When your public error-handling code is called, it can retrieve the procedure name from this stack and report it to the user. If you know the name of the procedure that was running when the error occurred, you can find the source of the error more quickly.

First, create a public array in your add-in’s Declarations section. For example:

Public Const conStackSize = 10
Public strProcNames (1 to conStackSize) As String

Second, create procedures to “push” procedure names on the stack and “pop” procedure names off the stack.

Sub PushDebugStack(strSubOrFunction)
	Dim intX As Integer

	For intX = conStackSize To 2 Step -1
		strProcNames(intX) = strProcNames(intX - 1)
	Next
	strProcNames(1) = strSubOrFunction
End Sub

Sub PopDebugStack()
	Dim intX As Integer

	For intX = 1 To (conStackSize - 1)
		strProcNames(intX) = strProcNames(intX + 1)
	Next intX
End Sub

In each of your add-in’s procedures, call the procedure that pushes the add-in procedure’s name on the stack. Using the previous example, you would call PushDebugStack with the name of the current procedure as the argument. At each of the procedure’s exit points, call the procedure that pops the name off the stack. Using the previous example, you would call PopDebugStack.

This method ensures that at any point in your program you have a list of up to 10 current procedures. The following example shows how to use these public error procedure stacks.

Function SearchForm ()
	PushDebugStack ("SearchForm")
		.
		. ' Main body of program.
		.
	PopDebugStack
End Function

In your public error-handling code, you can reference the procedure name stack and display the name of the current procedure, or display a list of up to 10 current procedures.

Dim strMsg As String
Dim intX As Integer

MsgBox "An error occurred in the following procedure: " & _
	strProcNames(1)
strMsg = "The following procedures were called: "

For intX = 2 To conStackSize
	strMsg = strMsg & strProcNames(intX) & " "
Next intX

MsgBox strMsg

Note The number (10) used in the preceding example for the array size is an example. You can increase or decrease this number depending on the complexity of your add-in, and the level of detail you want for your public error-handling code.