Copyright: Copyright 1997 FMS Inc.


Table of Contents

Introduction

Feature Highlights

What's New in Version 8

Suggesting New Code

Our Internet Home Page

Coding Tips and Techniques

Procedures, Dependencies, and Declarations

User-Created Procedures

Code Databases

Program Settings

Writing Better Code

VBA Code Style and Conventions

Running the Code Browser

The User Login Form

Specifying a Startup User Name

Errors when Starting Total Access SourceBook

Using the Browser

Procedure/Category Information Tab

Status Bar

Setting Options

Finding and Using Code

Topics in this Chapter

Getting Code into Your Modules

Export Strategy

Using the Export List

Avoiding Procedure Name Duplication

Printing Code

Running a Query

Using the Query Results

Adding and Customizing Code

Code Repository Overview

Adding New Procedures

Using Save As

Editing Existing Procedures

Deleting Procedures

Additional Topics

Making Backups

The Windows API Browser

Registry Settings

Uninstalling


Feature Highlights

Total Access SourceBook 8.0 provides over 500 ready-to-run Visual Basic for Applications (VBA) procedures for use with Access 95 and Access 97. Its browser program makes it easy to find the code you need to insert into your project.

But Total Access SourceBook 8.0 is more than just a library of quality code. The code management tools allow you to add and edit your own code, and to do it in a system that tracks additions, modifications, and the owner of each procedure you add.

Here are the highlights of the many powerful features of Total Access SourceBook 8.0:

•	Over 500 high quality ready-to-run VBA procedures.

•	Powerful, yet easy-to-use search facilities that make finding the code you need a snap.

•	Ability to add your own procedures in the code repository.

•	Ability to edit and delete existing procedures.

•	Ability to support user defined annotations for both the FMS and user procedures with helpful information.

•	Ability to track user defined procedures with owner, creation, and modification information.

•	The Windows API Browser makes it easy to find the Declare or Constant statements you need for calling Windows API functions from your code.

•	Import feature that lets you import existing procedures into the code repository.

•	Extensive on-line help shows you how to use the Browser program, and contains detailed reference material on the FMS procedures with example code.

•	Royalty-free distribution rights when incorporated in your applications subject to certain restrictions. (See the printed manual for more information.)


What's New in Version 8

The original version of Total Access SourceBook 8.0 shipped in October 1996 and was designed for Access 2.0. With the release of Access 97 (version 8), we are pleased to release version 8.0, a completely new version with many new features:

•	All source code is updated for the new VBA language in Access 95 and 97.

•	A new, improved 32-bit code browser is included.

•	The browser uses a tab control to display a procedure's code, help information, annotations, and system information all in one form.

•	Multiple procedures can be selected and exported to a single file or the clipboard.

•	User annotations can be included in the query criteria.

•	If you used Total Access SourceBook 8.0 version 2.0 to store procedures in the code repository, the Administrator program lets you import your custom procedures into version 8.0. See the help file for Total Access SourceBook Administrator for more information.


Suggesting New Code

Although Total Access SourceBook 8.0 contains over 500 procedures, there is always room for improvement. If you have an idea for a procedure you want to see in future versions and/or updates of the product, let us know.

Please do not send VBA code with your suggestion. Because of legal, copyright, and licensing issues, we cannot use non-FMS code. However, we value your ideas for procedures and look forward to hearing from you.

Submit ideas (not code) via email to techsup@fmsinc.com to the attention of "Total Access SourceBook 8.0 Product Manager". If possible, please include a brief description of why the procedure is useful, and how you would use it in Access.


Our Internet Home Page

To provide you with the most current information on our products, FMS can be reached on the World Wide Web. Our address is http://www.fmsinc.com. Our site is constantly being updated with news and features we think you will like. Drop by and take a look.

Free Total Access SourceBook 8.0 Updates

We will periodically offer source code updates on our web site. Just download the file and you can increase the number of fully tested procedures available in Total Access SourceBook 8.0. See the on-line help for Total Access SourceBook Administrator for more information.

Product Announcements and Press Releases

Read the latest information on new products, new versions, and future products. Press releases are available here the same day they are sent to the press. When you sign our guest book, you can even choose to have press releases automatically sent to you via E-mail.

Product Descriptions

Detailed descriptions on all our products are available. Each product has its own page with information on features and capabilities.

FAQ and Technical Support

View our lists of frequently asked questions (FAQs) about our products. You can also contact technical support and receive a reply via E-Mail by entering your questions on-line or sending them directly to our Internet address: techsup@fmsinc.com.

Product Registration

Register your copy of Total Access SourceBook 8.0 on-line.

Utilities and Free Software

Download free demos and utilities, including the FMS PowerPack, to use Microsoft Access more effectively.

Technical Papers

FMS personnel often speak at conferences, and write magazine articles, papers, and books. Copies and portions of this information are available to you on-line. Learn about our latest ideas and tricks for using Access more effectively.

Links to Other Microsoft Access Sites

Jump to other locations, including user group home pages, that offer Access news, files, related services, and other useful information.

Before you dive in and start using the code in Total Access SourceBook 8.0, please take some time to read this chapter. It contains important information that can help you understand and write better VBA code.

This chapter should help you use the code in Total Access SourceBook 8.0 more efficiently, and provide the groundwork for later material that explains how we have formatted and written the Total Access SourceBook 8.0 code.Important Concepts

There are several important concepts you should be familiar with before using Total Access SourceBook 8.0.


Procedures, Dependencies, and Declarations

The main goal of Total Access SourceBook 8.0 is to provide you with a library of high-quality pre-written code examples. These examples are in the form of VBA procedures or functions. It is beyond the scope of this manual to provide a full discussion of  programming, but a few definitions are in order:

Subroutine: A named series of code statements defined with a Sub...End Sub pair. A subroutine procedure may take arguments (parameters supplied when the procedure is called), but it does not return a value.

Function: Similar to a subroutine, except it is defined with Function...End Function, and returns a value to its caller.

Procedure: The generic name for either a subroutine or a Function.

VBA procedures are organized into containers known as Modules. A Module may contain one or more procedures, as well as variable declaration statements and references to external routines, known as Declarations. A VBA module may either be a distinct object in the database, or it may be attached "behind" a form or a report. Additionally, Access 97 supports the concept of class modules. For complete information on the different types of Access modules and their uses, refer to the Access documentation and online help.

Total Access SourceBook 8.0's code examples are stored at the procedure level. Most code examples work on their own. Before you can use one of Total Access SourceBook 8.0's procedures in your application you must place it into a module in your database. Once the procedure is added, you can use it as if it were a built-in command in the VBA language.

Microsoft Access Module with Function

A few of the procedures supplied with Total Access SourceBook 8.0 use other "helper" procedures. Procedures which rely on other procedures are said to be dependent on those procedures. Although you can include a procedure with dependents in your project, it will not work unless the dependent procedures are also included. If a procedure requires other procedures to work, it is indicated in the Total Access SourceBook 8.0 Export form which is used to copy the procedure into your application. The Export form is described in more detail  Exporting a Single Procedure.

In addition to dependent procedures, some of the procedures in Total Access SourceBook 8.0 require module-level variable declarations or Windows API function declarations. Although these sections of code are not true "procedures," they are required to support some of the Total Access SourceBook 8.0 functions. They are included as dependencies as if they were procedures.


User-Created Procedures

In addition to using Total Access SourceBook 8.0 as a resource for finding pre-written code examples, you can also store your own library of routines. Total Access SourceBook 8.0 includes a code repository feature that lets you add your own code. These may be items you created, collected from other sources, or modifications of the FMS code.

Just as with the FMS procedures, your code is stored at the procedure (Sub or Function) level. After adding your procedures to the Total Access SourceBook 8.0 code repository you may search, organize, and annotate these procedures just as you would the Total Access SourceBook 8.0 FMS procedures.


Code Databases

Total Access SourceBook 8.0 uses two Microsoft Access database files to store code. The FMS code database contains all of the code shipped with the product. The user code database contains all of the procedures you add to the system using either the [Add New Procedure] tool, or the [Save Current Procedure As] tool. These databases reside in the Data Directory assigned during installation.


Program Settings

The program stores its settings in the system registry maintained by the Windows operating system. These settings contain information such as:

•	Your user name

•	The data directory name

•	Any options set for the code browser

The first time you run Total Access SourceBook 8.0, it prompts you for your user name and creates the appropriate entries in the system registry. From then on, each time you start the program, this registry is read for this information. For more information on the registry entries for Total Access SourceBook 8.0, see  Registry Settings.


Writing Better Code

See the following topics for a list of useful guidelines and suggestions for writing better Access Basic code.

See Also

#1: Use Option Explicit

#2: Declare Variables Correctly

#3: Avoid Variants

#4: Don't Use Type Declaration Characters

#5: Use Narrow Variable Scoping

#6: Convert Data Types Explicitly

#7: Explicitly Type Cast Function Return Values

#8: Implement Robust Error Handling

#9: Pay Attention to Program Control

#10: Use Constants, Avoid Hard-coded Values

#11: Use Variable Naming Conventions

#12: Choose Meaningful Variable Names

#13: Add Comments

#14: Use Standard Indentation


#1: Use Option Explicit

This is not a tip, rather it is a requirement for writing VBA code. If you do not use the Option Explicit statement at the top of every module in your project, you are asking for trouble when you debug your application. By specifying Option Explicit, you tell VBA to force you to declare every variable used. This makes it easy to avoid program errors because of misspelled variable names. For example, the following procedure is in a module that doesn't have the Option Explicit statement. Can you find the bug?

Sub ShowBugs ()
  Dim intX As Integer
  Dim intY As Integer
  Dim intZ As Integer

  intX = 1
  imtY = 2
  intZ = 3

  Debug.Print intX + intY + intZ
End Sub

When you run this code, you would expect it to assign the values of 1, 2, and 3 to the intX, intY, and intZ variables, and then print the sum of the three which should be 6. However, this procedure prints the value of 4 because of a spelling error. Instead of setting intY to 2, the code is actually setting imtY (with an "m") to 2. Because Option Explicit is not declared, the first reference to the variable named imtY declares the variable automatically. This means that this code compiles just fine and doesn't become a problem until it is actually tested. On the other hand, if Option Explicit is specified, the above code would generate a compile error, making it easy to track down the error before you go any further in your development efforts.

Finally, consider this: if the bug in the example were simple to spot, what happens when you have the same bug buried in hundreds of lines of code?  Using Option Explicit is the single most important thing you can do to write better VBA code.

With Access 95 and later, you can have Option Explicit automatically inserted into every module you create. Search Access online help for "Options" for more information.


#2: Declare Variables Correctly

There are several guidelines to keep in mind when you declare your variables with the Dim statement. First, organize your Dim statements to keep them all together within a procedure, or with the declarations section of a module. By placing all Dim statements at the top of a procedure, you can get a quick inventory of variables that belong to a procedure. You an also perform quality-control on your variables by visually scanning the entire list at once—you don't have to hunt and peck through the entire module.

Next, keep each Dim statement on its own line. Consider the following code:

Dim intX As Integer, intY, intZ As Integer

A quick glance at the code may lead you to believe that three integer variables are being defined. However, a closer look reveals that intY is not being declared as an integer (it's actually a variant.) If each of these variables were declared on a separate line, it would be quickly apparent that intY was not type cast. By applying this rule to your code, you also avoid the simple Dim syndrome. Consider the following code:

Dim a, b, c As String

The intent of this code was to declare three variables as strings. But VBA doesn't work this way: only c is declared as a string—the others are declared as variants. VBA requires you to explicitly declare each variable. Otherwise, it is created as a variant which is less efficient.

You should also know that VBA lets you specify types for constants using this type of syntax:

Const strCustomer As String = "Customer"


#3: Avoid Variants

To make life easier for beginning developers, VBA offers the Variant data type. This data type is very flexible because it can represent almost any type of data, and VBA automatically handles all conversions from one type to another. But these strengths are also weaknesses. Because the variant type must be capable of holding any type of data, it adds storage overhead—it is always more efficient to store data using a specific data type than it is to use a variant. Also, since VBA does automatic conversions on variants, you are not in control—when conversions happen, you may see unexpected results.


#4: Don't Use Type Declaration Characters

Most of Microsoft's Basic variants offer the quaint (and archaic) type declaration character. By putting one of these pre-defined characters at the end of a variable or function name, you define the variable's type or the function's return value. For example, the following line of code:

Dim CName$

declares a variable called CName as a string. The dollar sign ($) character at the end is a type declaration character, and Access knows that a dollar sign signifies a string variable. The problem is that type declaration characters are old-fashioned. They still exist in VBA today primarily for backward compatibility. Good VBA code should avoid type declaration characters because they are considered obsolete. Also, there are only type declaration characters for a small subset of the data types that VBA supports.

You should also avoid the Def… statements, such as DefInt A-Z. These constructs are also obsolete, and can lead to code that is difficult to debug and maintain.


#5: Use Narrow Variable Scoping

All VBA variables and procedures are scoped to be available to different portions of the program. For example, a variable declared as Public in a standard Access module can be read or assigned from any procedure in any module in the database. This is the broadest possible scope. On the other hand, a variable declared as Private within a procedure or passed as a procedure parameter has the narrowest scope—it is only seen by the procedure. Between these two extremes, you also have module level scoping. When deciding on the scope of a variable, use the narrowest scope possible. If the variable only needs to be used by one procedure, make it private to that procedure. If the variable must be seen by many procedures in the same module, make it a module level variable. Only in rare occasions should you create Public variables. Code with narrow scoping throughout is much easier to debug and maintain. They are also much easier to move or copy.


#6: Convert Data Types Explicitly

There are times you need to convert data from one type to another. VBA makes this deceptively simple by often performing automatic data type conversions for you. But, this is not necessarily a good thing. Often, these automatic conversions can introduce changes in the data that you do not expect. This is especially true when converting numbers between integers and doubles. Also, the semantics and behavior of automatic data type conversion is often semi-documented or under-documented, and can change between versions of a language. For these reasons, you should use explicit VBA conversion functions in your code such as CInt, CDbl, CStr, etc. Relying on VBA to do automatic conversions can lead to bugs that are very hard to find.


#7: Explicitly Type Cast Function Return Values

One of the more important tips for variables is to explicitly declare a data type. The same rule holds true for functions. When you create a procedure as a function, you are implicitly telling VBA that the procedure returns a value. If you do not explicitly declare the return type of the function with the As keyword, VBA always casts the function's return value into a variant. As with variables, this can lead to subtle conversion and logic bugs in your code.

It also prevents the code compilation from detecting incorrect variable assignments. For instance, if you assign the function to a variable of type string, this is a compile error if the function explicitly returns a number, but not a problem if nothing is specified. Your application will then crash when that line of code is encountered. Every function should have its return value explicitly set.


#8: Implement Robust Error Handling

When an un-trapped error occurs in your VBA application, a number of interesting and unwanted things can happen. First, a dialog displays the error message, usually with OK, Cancel and End buttons. If your application's user is savvy enough to understand the cryptic message displayed, he or she is then unceremoniously dumped into the VBA source code at the line where the error occurred. Such behavior is hardly the hallmark of a professional application. To make matters worse, the source code may be secured, causing an even more cryptic error message to appear. Because of this, error handling is a crucial part of your application development efforts.

Every procedure, no matter how small or insignificant, should have some type of error handling. At the most rudimentary level, there should be an On Error Goto statement that points VBA to a label within the procedure. This label should contain code that, at a minimum, displays a meaningful error message.

You may also want to consider creating a procedure stack. A stack is simply a list of procedures, and the order they are executing in. This list is useful when your application encounters an unanticipated error. When such an error occurs, you can inspect the procedure stack, and see exactly which procedures were executing, and in which order. A stack is typically implemented with an array. Initialize the array when your application starts. Then, at the beginning of each procedure, place the procedure's name as the last element of the array, moving all other array elements up by one. Just before the procedure exits, it should remove its name from the array. Although this approach requires extra coding effort, it can be well worth it when trying to debug applications when the application and user are at a remote site.

Finally, Access supports the Erl function. This function returns the line number of the line where the last error occurred. You can use this function as part of your error handling routines to report exactly where the error occurred. Unfortunately, this powerful technique suffers from two rather large drawbacks: you must actually type physical line numbers in your code for Erl to return anything meaningful, and Access 97 does not correctly support Erl, often returning erroneous results. If you are using Access 2.0 or Access 95, you may want to consider using line numbers and Erl as a debugging tool.

Tip: Total Access SourceBook 8.0 provides an entire category of VBA code for implementing robust error handling in your applications.


#9: Pay Attention to Program Control

VBA provides many constructs for controlling the flow of your program. Unfortunately, some of these constructs are vestiges of the early BASIC days when programs were non-modular linear lists with required line numbers. These constructs, namely Goto, Gosub and Return, have little validity in modern VBA programs. They often lead to messy jumps and difficult to understand logic. If you want to write well structured code, you should never use the Gosub…Return statements, and only use the Goto statement for error handling.

Equally important, no procedure should ever have more than one exit point (Exit Sub or Exit Function commands.) If a procedure can exit from more than one place, logic errors, unclosed objects, and mishandled errors can occur. Finally, consider adopting the convention where every one of your procedures has a specific error label and a specific exit label.


#10: Use Constants, Avoid Hard-coded Values

Values that are hard-coded into program code make that program code difficult to understand and difficult to update. Consider the following code:

Dim intCost As Integer
Dim intQuantity As Integer
intCost = intQuantity * 1.712

What does this code do? That's obvious: it multiplies a quantity by 1.712 to get the cost. But what is 1.712? These types of magic numbers offer no information about what is being accomplished, and make the program difficult to decipher. If the number needs to be changed, it may also be very difficult to find. It is much better to declare a constant for the number.

Next, consider the following code:

If Err.Number = 2048 Then
  MsgBox "Error 2048 occurred", 16, "Acme Application"
Else
  MsgBox "Error 2048 didn't occur", 16, "Acmo Application"
Endif

In this example, hard-coded values cause two problems. The first is caused by the hard-coded number 16. What does 16 represent? What type of icon is displayed by the MsgBox function? Second, because "Acme Application" is hard-coded twice, it is easy to introduce typos, such as the one shown in the second MsgBox statement. Finally, by hard-coding literal strings throughout your program, you make it very hard to easily make global changes.

You can avoid these problems by replacing hard-coded values with centralized constants or variables, or by placing values in a table. When your application starts, it can read all text values and magic numbers from the table into memory, making them readily available to your program. When you remove all hard-coded values, you reduce the potential for errors, and make the program more maintainable. This last point can be crucial if you need to face the issue of internationalization. When you need to translate your application to another language, you will really appreciate code that has all literals and text stored in a central location.


#11: Use Variable Naming Conventions

Although the relative merits of different naming conventions can cause heated arguments among developers, the use of naming conventions is generally accepted as a good programming practice. Naming conventions add information to variable, object, and procedure names, typically using some type of prefix or suffix notation. This type of notation requires you to add several characters to the beginning or end of a variable name to identify its type. For example, each string variable you create would be prefixed with the characters "str", identifying those variables as strings. There are many different naming conventions for VBA, split mainly between Visual Basic and Access developers. Beyond code, people also use naming conventions for Access objects (e.g. tables begin with "tbl", queries "qry", forms "frm", command buttons "cmd", etc.).

The naming convention you choose is not as important as your commitment to use the convention consistently and constantly. Naming conventions don't work well unless you use them throughout your code, so pick a convention and stick to it.


#12: Choose Meaningful Variable Names

It may seem obvious that as a developer you should choose variable and procedure names that convey that object's purpose. But often, in the heat of programming, it is all to easy to resort to cryptic one-letter names like x and y. For readability, you should avoid such a temptation. Generally, one letter variable names are reserved for throwaway variables such as loop counters; beyond that, you should avoid informationless names.

Unfortunately, object naming is often at odds with the goal of making code compact—for documentation purposes, you want names that are long enough to adequately describe the variable or procedure. However, you don't want names that are so long that they make your code unreadable or difficult to type. Somewhere between the two extremes lies a happy medium: use names that are long enough to convey their purpose, but not so long that code becomes unmanageable.


#13: Add Comments

If you have ever tried to decipher another developer's code (or your old code), regardless of language, you are no doubt aware of the value of proper commenting. Many times, the purpose of a given piece of code is not readily apparent by reading the code itself. Comments go a long way to providing the information that makes the process of understanding, enhancing, and debugging code much easier.

There are various levels of commenting in VBA coding. These levels closely follow the levels of scoping:

Application (global) comments

Comments at this level explain the flow of the entire application and cover how different components and modules interact. Typically, these comments are kept outside of the application's project database, but can be included in a global module as comment text.

Module level comments

Module level comments explain the purpose and contents of a module. Information typically includes a list of the procedures in the module, their purpose and relationship, and revision comments for the module as a whole.

Procedure level comments

Comments for each procedure typically include a brief description of what the procedure does, a definition of its parameters and return value, revision information, and any special situations it requires.

Line level comments

Comments on or above a specific line of code explain the purpose of a variable, or a particular sequence of operations. Typically, it is best to include line level comments on the line immediately preceding the line of code. Comments on the same line as the code itself can be difficult to maintain.


#14: Use Standard Indentation

Control structures should be indented to improve readability and reduce programming errors. Paired structures such as If..End If, Do..Loop, For..Next, With..End With, While..Wend, Select Case .. End Select, Type..End Type, etc. should all use indenting to clearly show where they begin and end. Also, you should choose a tab width setting and stick with it—there is nothing worse than trying to work with an application that has several different tab width settings. By default, Access uses 4 spaces, but if you have lots of embedded loops, 2 spaces works better. This can be set in the Modules section of the Access Tools|Options menu selection.

Indentation can also aid the visual grouping of related operations, not just control structures. For example, consider using indentation to group AddNew…Update operations, Set..Close, and BeginTrans…CommitTrans operations. This makes it easier to see when the code enters and exits these "paired" operations.


VBA Code Style and Conventions

To get the most out of the VBA code in Total Access SourceBook 8.0, you should familiarize yourself with the conventions and standards we used. This section describes this aspect of the program.

See Also

Organization

Using the Code as a Learning Tool

Naming Conventions

Commenting

Indentation

Error Handling


Organization

Total Access SourceBook 8.0 code is organized into categories that closely follow the concept of Access modules. Related procedures are stored in a category, and each category is named accordingly. The program also allows you to export individual procedures or an entire category as a single module.

In several of the categories, there is an item called <Declarations>. Unlike the other items in a category, this item is not a procedure—rather, it is a collection of declarations used by one or more of the procedures in the category. The <Declarations> item corresponds to the General Declarations (top) section of an Access module. When you choose a procedure from a category that contains a <Declarations> item, Total Access SourceBook 8.0 automatically checks to see if that item is needed by the procedure. If so, it lets you know that there is a dependency between the procedure and the <Declarations> item.


Using the Code as a Learning Tool

Although the great majority of code in Total Access SourceBook 8.0 is designed to be plugged directly into your applications, there are several procedures that are designed to illustrate important concepts rather than perform a specific usable function. You can use the procedures in Total Access SourceBook 8.0 as a learning tool, because all of the code is commented, and online help with examples is included.

Because of this, you should feel free to borrow an algorithm or idea from Total Access SourceBook 8.0 and apply it to your own procedures. For many people, the best way to learn is to do. Don't be afraid to experiment with the included code. As with any programming language, VBA offers several different ways to accomplish a given task, and you may find that you can modify Total Access SourceBook 8.0 code to better suit your needs.


Naming Conventions

Total Access SourceBook 8.0 code uses a modified form of the Leszynski/Reddick naming conventions, first published in the Smart Access journal in 1994. The following table explains the naming conventions used in the built-in code:

Type	Prefix	Example
Boolean (flag)	f	Dim fIsDeleted As Boolean
Container	con	Dim conForms As Container
Control	ctl	Dim ctlListBox As Control
Currency	cur	Dim curAmountDue As Currency
Database	dbs	Dim dbsCurrent As Database
Date	dat	Dim datToday As Date
Document	doc	Dim docTemp As Document
Double	dbl	Dim dblRange As Double
Field	fld	Dim fldLastName As Field
Form	frm	Dim frmOrders As Form
Group	grp	Dim grpAdmin As Group
Index	idx	Dim idxPKey As Index
Integer	int	Dim intCounter As Integer
Long	lng	Dim lngValues As Long
Object	obj	Dim objExcel As Object
Parameter	prm	Dim prmEndDate As Parameter
Property	prp	Dim prpBackColor As Property
Querydef	qef	Dim qdfSelect As Querydef
Recordset	rst	Dim rstUpdate As Recordset
Relation	rel	Dim relTmp As Relation
Report	rpt	Dim rptSales As Report
Single	sng	Dim sngSmall As Single
Snapshot	snp	Dim snpReadOnly As Snapshot
String	str	Dim strLastName As String
Table	tbl	Dim tblCust As Table
Tabledef	tdf	Dim tdfSales As Tabledef
Type	typ	Dim typCust As CUST_RECORD
User	usr	Dim usrFrank As User
Variant	var	Dim varIsNull As Variant
Workspace	wrk	Dim wrkSecure As Workspace
API Declare Naming Conventions

To minimize the potential for conflicts, all Declare statements in the Total Access SourceBook 8.0 code are prefixed with the text "TSB_API_". This text eliminates the possibility of our Declare code conflicting with existing or new code.

The following line is an example of this naming convention:

Declare Function TSB_API_CloseClipboard _
Lib "USER32" Alias "CloseClipboard" () As Long


Commenting

All procedures contain comments that include a description of the procedure, a definition of the procedure's parameters, and an explanation of the procedure's return value. These comments are in the following form:

Procedure Declaration ()
  ' Comments  : a description of what the procedure does
  ' Parameters: Parameter1 - description of parameter1
  '             Parameter1 - description parameter2
  '             Uses *SET* whenever a procedure
  '             parameter is changed by the program
  ' Returns   : what the procedure returns

End Proc

These comments, in conjunction with the Total Access SourceBook 8.0 online help and example code, make it easy to understand and implement the included code.


Indentation

All Total Access SourceBook 8.0 code is indented using standard indentation. All control structures such as If, Do, Select Case, etc. are indented, as well as other "paired" operations such as AddNew…Update, Edit…Update, and BeginTrans…CommitTrans. A Tab Width of 2 spaces is used throughout. Note that you can change the Tab Width setting when you export a procedure. See Exporting a Single Procedure for more information on exporting code.


Error Handling

As mentioned earlier in this chapter, error handling is an important part of good application design. Most procedures in Total Access SourceBook 8.0 include rudimentary error handling. In many cases, the error handling performs only the most basic handling. This is because we cannot anticipate what your application's needs are, or how you are implementing error handling in your code. Because of this, we have kept our error-handling code purposefully simple. For Total Access SourceBook 8.0 procedures that you export to your application, you should check, and rewrite where necessary, any error handling code.

Total Access SourceBook 8.0 includes a large number of VBA procedures for you to use. A powerful code browser organizes the source code and makes it easy to find the code you want and add your own code to a user repository. This chapter shows you how to start the code browser, how to use it, and how to customize and save options. Starting the Program

To start Total Access SourceBook 8.0, open the Start Menu folder called Total Access Tools. From here, select Total Access SourceBook 8.0. Alternatively, you can execute the file named TASB80.EXE located in the program directory you specified during installation.


The User Login Form

If this is the first time you have run Total Access SourceBook 8.0, the User Login form appears:

Total Access SourceBook 8.0 Login Form

Use this form to identify yourself to the Total Access SourceBook 8.0 program. Your user name is very important because Total Access SourceBook 8.0 uses it to identify you as the owner of the procedures you add to its code repository.

The first time you start Total Access SourceBook 8.0, the User field in the Login form shows "User1". Type in the user name you wish to use as your identifier to the program. It is generally a good idea to use the same user name that your installation of Windows uses, or your network login name. This eliminates the confusion of having multiple user names. Once you enter your user name, press the [OK] button and the Browser program continues to load.

The next time you start the program, the user name you entered is automatically filled in, allowing you to simply press [Enter] to start the program. This reminder allows you to ensure that you are logged in under the correct user name. You can turn this dialog off by checking the "Don't Show This Dialog In the Future" checkbox.


Specifying a Startup User Name

You can specify a user name to be used every time Total Access SourceBook 8.0 is started by using a  command line parameter called /USER. This parameter allows you to specify your user name without having to see the login dialog each time the program starts. To specify the user name, you modify the Windows shortcut that you use to launch Total Access SourceBook 8.0. For example, let's assume your user name is "Fred" and you want this user name to be supplied to Total Access SourceBook 8.0 each time the program starts. To accomplish this, call the Total Access SourceBook 8.0 executable file (TASB80.EXE) with the /USER parameter, as follows:

c:\sourcebk\tasb80.exe /User Fred

For information on modifying Windows shortcut items, consult your Windows documentation.


Errors when Starting Total Access SourceBook
Errors when Starting Total Access SourceBook 8.0

Under certain circumstances Total Access SourceBook 8.0 may not open. This is usually caused by its inability to locate or open its code databases. These are some of the error messages you may receive:

Message: Cannot locate data files in (path)

Condition: Total Access SourceBook 8.0 was unable to find its data files in the location specified in the system registry.

Probable Causes:

1) 	You moved the code databases to a different location.

2) 	Your data files were on a network drive and the network is down or the directory is currently inaccessible.

Fix: Reinstall Total Access SourceBook 8.0 specifying the correct data locations.

Message: Unable to obtain exclusive access to (path) or Can't open Total Access SourceBook 8.0 data files (path)

Condition: Another user or program has the data files open in exclusive mode.

Probable Causes:

1) 	You have the single-user version and another user is already using Total Access SourceBook 8.0.

2) 	Someone is running the Total Access SourceBook 8.0 Administrator program.

3) 	Another program such as Microsoft Access or Visual Basic is using the data files.

Fix: Close all other applications which might be using the data files or be sure that no other users are attempting to use the files.

Message: Total Access SourceBook 8.0 cannot open the data files (path). The error returned was <Jet error message>

Condition: An unexpected error, other than the previously mentioned exclusive access errors, has occurred.

Probable Causes: A corrupt database or a database which is in the invalid format.

Fix:

1) 	Run the Total Access SourceBook 8.0 Administrator program's Compact and Repair routine. For more information on the Total Access SourceBook Administrator program see the manual or the on-line help for that program.

2) 	Run your operating system's disk check program (SCANDISK or CHKDSK) to be sure that your hard disk is not damaged.

3) 	Reinstall Total Access SourceBook 8.0 in case the program files are damaged.


Using the Browser

Assuming there were no problems, the Total Access SourceBook 8.0 Browser form appears. This is where you do most of your work. The Browser is used to find Total Access SourceBook 8.0's own procedures as well as to edit, annotate, copy, and create your own procedures.

The left side of the browser shows all the available functions grouped in a tree view of folders similar to Windows Explorer. This lets you see the topics and expand them for individual items. By clicking on a function, its source code is displayed on the right in the Code Window. Help is also available with additional information and examples.

Total Access SourceBook 8.0 Browser

Most of Total Access SourceBook 8.0's features are available from its toolbar and all of the toolbar buttons are available as menu choices as well. The following is a short explanation of the Total Access SourceBook 8.0 features available from the Browser in the order they appear on the toolbar. For detailed discussion of any of these options, see the corresponding section in this manual.

 Expand All

Available from the toolbar or from the View, Expand All menu. This feature displays all procedures in categories within the current browser view.

 Collapse All

Available from the toolbar or from the View, Collapse All menu. This feature collapses the browser tree so that only the categories within the current view are visible.

View Select

The Total Access SourceBook 8.0 Browser tree can show procedures from one of two sources or a combination of the two sources. You may select which browser view you wish to use by selecting the corresponding item from the drop-down listbox on the toolbar or from the View menu:

•	View FMS code – only procedures which ship with Total Access SourceBook 8.0 are displayed.

•	View User code – only user-written procedures stored in the code repository are displayed.

•	View FMS & User Code – both FMS procedures and user procedures are displayed.

 Query

Available from the toolbar or from the Tools, Query menu. This feature opens the Total Access SourceBook 8.0 Search form to begin a query. See Running a Query for more information on querying the code database.

 Query Results

Available from the toolbar or from the Tools, Query Results menu. This feature opens the Total Access SourceBook 8.0 Query Results form to display the results of a previous query. If you did not run a query in this session, the Total Access SourceBook 8.0 Search form is displayed instead. See Using the Query Results for more information.

 Previous in Query Results

Available from the toolbar or from the Tools, Query Previous menu. This feature locates the previous procedure which matches the search criteria specified in an earlier Query operation. If you did not run a query during this session, the Total Access SourceBook 8.0 Search form is displayed instead. See Using the Query Results for more information.

 Next In Query Results

Available from the toolbar or from the Tools, Query Next menu. This feature locates the next procedure which matches the search criteria specified in an earlier Query operation. If you did not run a query during this session, the Total Access SourceBook 8.0 Search form is displayed instead. See Using the Query Results for more information.

 Edit Current Procedure

Available from the toolbar or from the Edit, Edit Current Procedure menu. This feature opens the Total Access SourceBook 8.0 Edit form to edit the procedure which is currently displayed in the Browser tree. You may only edit your own procedures, not FMS code. For more information on Editing user-written procedures, see  Editing Existing Procedures.

 Annotate

Available from the toolbar or from the Edit, Annotate Current Procedure menu. This feature opens the Total Access SourceBook 8.0 Annotate Procedure form to allow you to make annotations to either an FMS procedure, or a user-written procedure.

 Print

Available from the toolbar or from the File, Print Current Procedure menu. This feature prints the procedure which is currently displayed in the Browser tree onto the default printer. For more information, see  Printing Code.

 Add

Available from the toolbar or from the Edit, Add New Procedure menu. This feature allows you to add a new user-written procedure to the Total Access SourceBook 8.0 code base. See Adding New Procedures for more information.

 Save As

Available from the toolbar or from the File, Save As menu. This feature allows you to save the procedure which is currently displayed in the Browser tree under a new name. You may save either FMS procedures or your own user-written procedures with this feature. For more information, see Using Save As

 Export Procedure

Available from the toolbar or from the File, Export Current Procedure menu. This feature allows you to save the procedure which is currently displayed in the Browser tree to a text file. For more information, see  Exporting a Single Procedure.

 Copy Procedure to Clipboard

Available from the toolbar or from the Edit, Copy Current Procedure menu. This feature allows you to copy the current procedure. For more information, see  Copy To Clipboard or Export To a File.

 Delete Current Procedure

Available from the toolbar or from the Edit, Delete Current Procedure menu. This feature allows you to delete user-written procedures. For more information, see Deleting Procedures .

 Add to Export List

Available from the toolbar or from the Edit, Add to Export List menu, as well as the browser tree right-click context menu. This feature allows you to mark procedures for later export into a VBA module. For more information, see  Selecting Procedures for the Export List.

 Delete from Export List

Available from the toolbar or from the Edit, Delete from Export List menu, as well as the browser tree right-click context menu. This feature allows you to remove procedures from the list of procedures to be exported into a VBA module. Use this button to remove procedures added with the Add to Export List button. For more information, see  Deleting Procedures from the Export List.

 Show Export List

Available from the toolbar or from the Edit, Show Export List menu, as well as the browser tree right-click context menu. This feature opens a form which allows you to view and edit the procedures which you have previously selected for the Export List. See  Viewing the Export List.

 Export from List

Available from the toolbar or from the Edit, Export from List menu, as well as the browser tree right-click context menu. This action displays the Export form where you can export the selected procedures on the Export List into a VBA module. See Export from the Export List for more information.

API Browser

Available from the Tools, Windows API menu. This item displays the Total Access SourceBook 8.0 Windows API Viewer form. Windows API calls are not integrated with the rest of the Total Access SourceBook 8.0 procedures due to their additional complexity. This form is a convenient place to search for Windows API function declarations. For more information on this feature, see  The Windows API Browser.

Options

Available from the Tools, Options menu. This feature displays the Total Access SourceBook 8.0 Options form. From here you can specify a variety of settings which control how the Browser works, and how exported functions are formatted. For more information, see Setting Options.

Browser Tree

The Total Access SourceBook 8.0 Browser tree view is a graphical, hierarchical display of both FMS and user-written procedures. Each procedure is placed in a single category, such as "Working with Microsoft Access Forms" or "Error Handling."

Browser Tree View

To display a list of procedures in a selected category, double-click on the category in the browser tree. To hide the list of procedures within this category, double-click again. You may also navigate through the list by using the arrow keys, and you may open or close the category folder by pressing the [Enter] key.

Total Access SourceBook 8.0 displays either FMS procedures, user-written procedures, or both, depending on the setting of the View Select combo box described previously.

When you select a particular procedure in the Browser Tree View, the code is displayed in the Code Window to the right. If you select a category rather than a procedure in the tree view, a short description of the category is displayed in the Code Window.

As a convenience, a context-sensitive popup menu is available by right clicking on the Browser Tree View. This menu duplicates many of the choices on the toolbar and menu.


Procedure/Category Information Tab

When you select a procedure or a category from the Browser Tree View on the left of the main Total Access SourceBook 8.0 browser form, a corresponding item of information is displayed in the Procedure/Category Information tab on the right of the form.

The tab displays one of four types of information:

•	code for the procedure

•	reference information for the procedure or category

•	user annotations

•	procedure details

See Also

Code Window Tab

Reference Tab

Annotations Tab

Details Tab


Code Window Tab

When the Procedure/Category Information tab is set to "Code", the text of the procedure which is currently selected in the Total Access SourceBook 8.0 Browser tree is displayed.

Code Tab

If the browser is currently on a category rather than on a procedure, the Code Window is empty.

Although you cannot edit code displayed in this window, you can select and copy it to the Windows clipboard. This is a convenient way to quickly copy a selected procedure without going through the File, Export Current Procedure process.

Tip: Adjust the size of the browser tree window relative to the procedure/category information tab by using the mouse to drag the bar separating them.


Reference Tab

The Reference tab provides descriptions, comments, and examples of using the selected procedure:

Reference Tab

The information on the reference tab for each procedure consists of the following items:

Name – name of the procedure

Description – a short description of the procedure

Declaration – the first line of the procedure, including any arguments or return values

Arguments – descriptions for each arguments of the procedure. These entries include the name and a description of the purpose of the parameter

Returns – if the procedure is a function, this section describes its return value

Comments – general descriptive text for how the procedure is used and any other requirements

Example – an actual code example that shows the procedure in action


Annotations Tab

The annotations tab of the Procedure/Category Information tab lets you quickly view any annotations you made for a procedure:

Annotations Tab

The annotation displayed in this view is read-only. To add or edit annotations use the Edit, Annotate Current Procedure menu choice or press the [image] button on the toolbar.


Details Tab

This tab of the Procedure/Category Information tab displays detail information on the selected procedure:

Details Tab


Status Bar

At the bottom of the Total Access SourceBook 8.0 browser, a status bar indicates the user who created the procedure, and the user who last modified it, if any:

Status Bar

An icon of a padlock indicates whether or not the procedure is modifiable. If the padlock is closed, then the creator of the procedure has specified that it is a read-only procedure, and the current user is not the user who created the procedure. If the padlock is open, this indicates the current user is its creator and can modify it, but other users cannot. If there is no padlock icon at all, then it is either an FMS procedure, or the creator of the procedure has allowed other users to modify it (i.e. it is not read-only.) See Code Repository Overview for more information on code ownership and editing options.

If you added comments (annotations) on the procedure, the Annotations icon is displayed in the status bar:


Setting Options

You may specify a variety of settings to control how the Total Access SourceBook 8.0 Browser behaves, and how exported functions are formatted. Access this function from the Toolbar or from the Tools, Options menu.

Setting Options

See Also

Export / Save As Options

Fonts

Treeview Options

Other Options

Saving or Canceling Your Changes


Export / Save As Options

These options control how code is formatted when it is exported to a file or the clipboard for use in your application.

Include Dependencies When Applicable – When a procedure is dependent on other procedures, check this option to include not only the selected procedure, but all dependent procedures when exporting the procedure.

Include Comments – Check this option to include the comments after the procedure declaration in the exported code. Note: these are not the descriptive comments which are displayed above the Code Window in the main browser window, but are the actual comments embedded within the source code of the procedure.

Tab Width – Enter the number of spaces for each indentation level.

Default Suffix for User Code – Enter the suggested extension used for exported procedures. The FMS Total Access SourceBook 8.0 procedures all end with the suffix "_TSB" to minimize the chance it would conflict with your existing code. If you would like the procedures to have a different suffix or no suffix at all, specify the default suffix here. When you actually export the procedure you may still choose a different procedure extension. This is simply the default procedure extension.


Fonts

For ease of readability on a wide variety of monitors and screen resolutions, Total Access SourceBook 8.0 lets you customize various forms to select the font name and size of your choice. The fonts which can be customized in Total Access SourceBook 8.0 are:

•	Treeview – Main Browser Tree

•	Code – Code Window and Edit Window

•	Annotations – Annotation Window

•	Query Results – the Query Results form

The fonts for Code and Annotations are also used for printing.


Treeview Options

You may specify how the category and procedure information is displayed within the Total Access SourceBook 8.0 Browser tree. You may select among the following settings:

•	Text Only

•	Picture and Text

•	Plus/Minus and Text

•	Plus/Minus, Picture, and Text

•	Tree lines And Text

•	Tree lines, Picture, and Text

•	Tree lines, Plus/Minus And Text

•	Tree lines, Plus/Minus, Picture, and Text


Other Options

Other miscellaneous options which can be set on the Total Access SourceBook 8.0 Options form are:

Save Browser Window Size on Close – Check this option to have Total Access SourceBook 8.0 remember for your next session the size and position of the browser window when you exit a session.

Save Procedure Position on Close – Check this option to have Total Access SourceBook 8.0 remember the last procedure that was displayed, and the view of the View Select combo box when you exit a session.


Saving or Canceling Your Changes

Press the [OK] button to save your changes and return to the Total Access SourceBook 8.0 Browser. Press [Cancel] to discard your changes and return to the previous settings.


Topics in this Chapter

Total Access SourceBook 8.0 includes many VBA procedures and procedures for managing Windows API calls. In order to easily find the code you want, an powerful, yet easy to use, set of tools are available. This chapter shows you how to find, retrieve, and add code to your database.

There are several ways to find the available source code:

•	Use the Browser Tree View to locate the category and procedure desired. Double click on the group to reveal the procedures contained within it, then move to a procedure to see its detail in the code window before selecting it.

•	Use the API Browser to find and use Windows API resources.

•	Use the query feature to perform sophisticated searches.


Getting Code into Your Modules

Total Access SourceBook 8.0 contains FMS code that is designed to work with Microsoft Access 95 (version 7) and Microsoft Access 97 (version 8). To make the process of finding and using code as simple as possible, Total Access SourceBook 8.0 uses the Windows clipboard as its primary mechanism for getting code into Access. This section outlines some key strategies for working with Access modules.


Export Strategy

Total Access SourceBook 8.0 allows you to export procedures individually or collect a number of procedures to export as a group. Usually, you simply want to find a procedure to solve a particular problem. In this case, you would use the Export Procedure function described below. Other times you may want to select multiple procedures to be exported together. Use the Export List feature to collect the procedures of interest to you. The following section describes how to use the Export List.

See Also

Exporting a Single Procedure

Copy To Clipboard or Export To a File

Getting the code into Access

Quick Clipboard Copy


Exporting a Single Procedure

Once you find the procedure you want, you need to export it to get it into your Access database. To do this, use the Export tool from the toolbar [image] or select File, Export Current Procedure from the main Total Access SourceBook 8.0 Browser menu.

Tip: As you use Total Access SourceBook 8.0, keep Microsoft Access open. This allows you to quickly switch between the two programs, and copy and paste code. Since Total Access SourceBook 8.0 is a separate program, you do not need to close Access to use it. Use the [Alt] [Tab] keystroke to switch between Total Access SourceBook 8.0 and Microsoft Access.

With the desired procedure selected, press the [image] export button on the toolbar or select File, Export Procedure from the menu. The Export form appears:

Export Form

The name of the procedure is shown at the top of the form. If the procedure requires other procedures or declarations in order to work, they are listed in the "Dependencies" list box. See Procedures, Dependencies, and Declarations for more information. You can modify the way the exported procedures are generated by setting the following options:

Include Dependencies – export all dependent procedures as well as the selected procedure.

Include Comments – include procedure comments in the exported procedure.

Tab Width – specify the number of spaces to indent each line of code.

You can set the default values for these options under  Export / Save As Options.


Copy To Clipboard or Export To a File

The best method for getting code into Access depends on what you are copying from the Total Access SourceBook 8.0 Browser.

If you are copying a single procedure, the [Copy to Clipboard] button is the best choice. Pressing this button causes the browser to copy the selected code to the Windows Clipboard object. You can then use standard Windows Paste operations to place the code into Access.

If you are copying a procedure with dependencies, the [Export to File] button is the easiest. You can check the "Open with Notepad" option to automatically open the exported file with the Windows Notepad editor. This makes it easier to copy and paste the code into your modules.

You can also view the code before exporting it by pressing the [Preview] button.


Getting the code into Access

Next, switch back to your open copy of Microsoft Access using the [Alt] [Tab] key sequence, or by using the Windows Task Manager.

If You Used [Copy To Clipboard]

With the [Copy To Clipboard] option, all you need to do is paste the clipboard contents into an open Access module. Just place the cursor at the end of an existing procedure definition (the [Ctrl] [End] key combination is handy for this—it moves you to the end of the module) and press [Ctrl] [V] or select Edit, Paste from the menu. The code from Total Access SourceBook 8.0 appears in your module.

If You Used [Export to File]

Using the [Export To File] method without the "Open with Notepad" option, the exported code is stored in the text file you specified which can be imported into a new module. To do this, create a new module and choose Insert, File from the Access menu, and point to the file you exported from Total Access SourceBook 8.0. Press the [OK] button in the Access Insert File, and the entire contents of the code you selected is placed in the new module.

If You Used [Export to File] With NotePad

With the [Export to File] method and the "Open with Notepad" option, you can use the contents of the Notepad document as a temporary work area. Highlight the code in Notepad and use [Ctrl] [C] to copy it, then move to your Access module at the appropriate place and use [Ctrl] [V] to paste. This is particularly useful if you have code (global variables, constants, user defined types, and declare statements) that needs to be added to the declarations section of the module.

Note: If you use one of the above techniques to create a new module, be sure to place the Option Explicit command in the declarations section. As a developer, you no doubt use Option Explicit in your modules, but when you create a new module in Access, this is not automatically added unless you have the appropriate setting in the Options, Modules section of Access.


Quick Clipboard Copy

The "Copy to Clipboard" toolbar button [image] is available to quickly copy the current procedure to the clipboard without using the Export dialog.

This feature has been integrated with the [Ctrl] [C] key combination for copying data to the Windows clipboard. When a procedure is displayed in the Code Window, you can press [Ctrl] [C] to copy the entire procedure to the clipboard without having to highlight the procedure's text. If the procedure contains dependencies, a dialog appears allowing you to either copy just the procedure, or open the Export dialog to see the dependencies.

Note that if you have highlighted a portion of the procedure in the Code Window, and press [Ctrl] [C], Total Access SourceBook 8.0 does not check for dependencies, and only copies the highlighted portion of the text to the Windows clipboard.

For more information on this topic, see the on line help topic entitled "Quick Clipboard Copy."


Using the Export List

Rather than exporting procedures one at a time, you can combine multiple procedures into one export with the Export List feature. Simply add the procedures you want onto a list that is maintained during your session of Total Access SourceBook 8.0, then export it.

See Also

Selecting Procedures for the Export List

Deleting Procedures from the Export List

Viewing the Export List

Export from the Export List

Export List Lifetime


Selecting Procedures for the Export List

To add a procedure to the Export List, select it in the browser Tree View and then right-click to show the context-menu:

Export List Context Menu – Add Procedure

Select "Add to Export List" to add the currently-selected procedure to the Export List. Alternatively you could use the toolbar button [image] or the Edit, Add to Export List menu option.

The icon on the Browser Tree View changes to indicate that the current procedure is on the Export List:

Procedure Added to Export List

If you select the Add to Export option while a category is selected rather than a specific procedure…

Export List Context Menu – Add Category

you have the option of adding all the procedures in the selected category to the Export List:

Add Procedures in Category Confirmation

Note: if you select this option, both FMS procedures and your own custom user functions that are in the selected category are added to the Export List.


Deleting Procedures from the Export List

If you decide not to include a previously selected procedure in the Export List, you can remove it in the same way you added it by using the right-click context menu on the procedure name in the Browser Tree View:

Export List Context Menu - Delete Procedure

Alternatively you may select the Edit, Delete from Export List menu option or use the corresponding toolbar button [image].

As with the Add to Export List option, you can choose to remove from the list all the procedures in the selected category:

Delete Procedures in Category Confirmation

Note: selecting this option only removes the selected procedures from the Export List if they are present. It does not affect the actual procedures stored in Total Access SourceBook 8.0.


Viewing the Export List

Select "Show Export List" from the right-click context menu on the Browser Tree View to view a list of the procedures added to the Export List. Alternatively, you may click on the [image] button on the toolbar or choose the Edit, Show Export List menu option. The Export List form appears:

Export List Form

This list is similar to the Query Results list discussed  Using the Query Results.

The icon on each row is checked if the procedure is a user written procedure. If it is unchecked it is an FMS procedure. You may sort the display by Category, Procedure Name, or Description by pressing column button at the top of each column:

Sorting the Export List

You may remove one or more procedures from the Export List by selecting them with the mouse and pressing the [Del] key or the [Delete] button. The Export List allows multiple selections so you can select more than one procedure by using Ctrl-Click (to select individual items) or Shift-Click (to select a range of items.) To remove all items from the Export List press the [Clear] button.


Export from the Export List

Once you gather all the procedures you wish to export, press the [Export] button from the Export List form, or select "Export from List" from the Browser Tree View right-click context menu. You may also use the Edit, Export from List menu option or the Export from List toolbar button:

After selecting the Export from List option, the Export form appears. Notice that this similar to the form for exporting a single procedure, but with a few changes:

Export Dialog – Export List

The Preview, Copy to Clipboard, and Export to File options work the same as in the Export Single Procedure dialog discussed  Exporting a Single Procedure. If any of the procedures on the Export List have dependencies, the dependencies are not listed, but an indication is made that "one or more procedures to be exported contains dependent procedures." If you uncheck the "Include Dependencies" check box only the procedures on the Export List are exported. If you check the box, the selected procedures and any of their dependent procedures are exported.

If more than one of the selected procedures on the Export List has the same dependent procedure, only a single copy of the dependent procedure is exported if you choose the "Include Dependencies" option.


Export List Lifetime

Total Access SourceBook 8.0 does not clear the Export List when you export the procedures or copy them to the clipboard. This allows you to create slightly different procedure selections for multiple exports without having to recreate the list from scratch.

The items you add to the Export List are not saved when you leave Total Access SourceBook 8.0. If there are any items on the list when you attempt to exit Total Access SourceBook 8.0, you are given a chance to cancel exiting.


Avoiding Procedure Name Duplication

You cannot duplicate a procedure name within a single Microsoft Access module. Although you can use the same procedure name in more than one module, this can lead to confusion since Access attempts to determine for itself which procedure you are referencing. To select the correct procedure, you need to qualify the procedure name with the name of the module. For these reasons it is not recommended that you duplicate a procedure name in more than one module.

To be sure that a new procedure name copied from Total Access SourceBook 8.0 does not duplicate a procedure already in your project you should check your module before pasting the new code. You can use [Ctrl] [F] or Edit, Find from the Microsoft Access module menu to search for any string. You can also press [F2] to open the object browser.

If you paste a duplicate procedure into a module, your code will not compile. To check for this condition after pasting in the code, do a Debug, Compile Loaded Modules command in a Microsoft Access module.


Printing Code

To print the current procedure, press the [image] button on the toolbar or select from the menu File, Print Current Procedure. This feature prints the procedure which is currently displayed in the Browser Tree View to your default printer.

Print Dialog

The Print Procedure dialog allows you to specify any of three items to print:

•	Print Procedure
Print the body of the procedure

•	Print Annotation
Print any annotations

•	List Dependencies
Print a list of dependent procedures, if any


Running a Query

Total Access SourceBook 8.0 provides a sophisticated query engine to help you locate code by specifying criteria. Press the [image] Query button on the toolbar or select Tools, Query from the Total Access SourceBook 8.0 Browser menu. This form appears:

Query Search Form – Basic Options

Specify a Search Phrase

In the drop down combo box at the top of the form, enter the word or phrase to find. This can be a single word, such as "table" or it can be a phrase such as "days in a month". The search value does not have to be a complete word. You could search for "records" for example, and you would find procedures containing "recordset" and "recordsetclone". The capitalization of your search words does not matter. The query engine performs case insensitive searches.

Total Access SourceBook 8.0 maintains a history of the words you searched for in this session. You can choose from a list of previously searched criteria by pressing the down-arrow on the combo box.

If you do not specify a search word, then all procedures matching the remainder of your selection criteria are displayed.

Where You Came From

The View Select setting on the toolbar controls whether or not the browser displays FMS code, user-written code, or both. The Query tool respects the Browser's current view.

This means that the query only searches for the procedures in the currently selected browser view. If you have "View FMS Code" selected in the browser, only the FMS procedures are searched.

Narrow the Scope of the Search

In addition to searching for procedures containing a certain word in the current browser view, you can enter additional criteria to narrow the scope of the search.

You can select from among procedures that belong to a certain category, such as "Error Handling" or "Access Forms" by selecting an option in the option group labeled "Select Category(s) to Search In". The three choices are:

All Categories – do not use category as a selection criterion.

Current category only – use the category that is currently active in the Total Access SourceBook 8.0 Browser.

Specific category – select a specific category from a list in the drop down combo box to the right of the option button.

It is also possible to limit the search only to the actual code in the procedure, to the comments about the procedure, or to annotations you have made about the procedure. Check the boxes marked "Code", "Description", or "Annotation" to indicate which locations to search. You can select one or more of these options.

Advanced Options

Press the [Advanced] button to set criteria on fields related to procedures stored in the code repository. These fields are primarily of use in the Team Version of Total Access SourceBook 8.0. They identify the name of the user who created or modified a procedure as well as the creation and modification dates.

Query Search Form – Advanced Options

Note: Since the [Advanced] button is for searching user procedures, it is disabled (grayed out) when you open the Query form while the Browser has "View FMS Code" selected. If you want to use the Advanced Options, close the Query form and select either "View User Code" or "View FMS & User Code" on the Browser toolbar.

The options you can specify are:

Created by – the name of the user who created the procedure. Select the name from the drop down list box.

Modified by – the name of the last user who modified the procedure. Select the name from the drop down list box.

Creation date – the date that the user-written procedure was created. Enter the value in MM/DD/YY or MM/DD/YYYY format. For example, "10/1/1997".

Modification date - the date that the user-written procedure was last modified. Enter the value in MM/DD/YY or MM/DD/YYYY format.

For the date range fields, if you leave the first text box blank, then no beginning date range is assumed. If you leave the second text box blank, then no ending date range is assumed. You can use this feature to find all procedures that were modified before or after a certain date without specifying a beginning or ending range date.

Execute the Query

Press the [Query] button to execute the query. Total Access SourceBook 8.0 searches for matches. If any are found, the Query Results form appears. If no matches are found, a dialog appears letting you know that no procedures matched your criteria.


Using the Query Results

The Query Results form displays all the procedures matching the criteria you entered in the Query form. The total number of procedures found is displayed in the title bar of the form, and the criteria you used is displayed in a text box at the top of the form.

Query Results Form

See Also

Navigation and Display


Navigation and Display

Use the arrow keys or scroll bars to navigate through the results. You may use the mouse to adjust the column widths of the displayed information. Double-click on the divider line between two columns in the column header, and the display automatically resizes to fit the widest value in the column.

The columns displayed are:

User – A check box indicating whether this is an FMS or user-written procedure

CategoryName – category to which the procedure belongs

ProcName – the name of the procedure

Description – a short description of the procedure

 Go To Current Procedure

Press this toolbar button to highlight the current procedure in the browser.

  Next/Previous Match

Use these toolbar buttons to move to the next or previous procedure in the query results.

 Pinning the Query Results Form

If you prefer to keep the Query Results form open as you move among procedures, press the Pin button on the toolbar. This keeps the Query Results form open until you explicitly close it, or press the Query toolbar button. If you do not have this option selected, the Query Results form closes each time you go to a procedure using the Go To Current Procedure, or Next/Previous Match toolbar buttons.

 Returning to the Query Form

If you want to return to the Query form to modify or fine-tune your query, press the Query toolbar button. This closes the Query Results form and opens the Query form.

Total Access SourceBook 8.0 is not just a static collection of pre-written code. Through the browser, you can easily add your own comments and code, edit existing code, or delete the code you added.  Additionally, each procedure is associated with the user who created it, allowing the program to restrict who can edit and keep system-maintained information such as creation and modification dates.

This chapter teaches you how to customize Total Access SourceBook 8.0, and add and manage your own code in it. The multi-user features of the Team Version are also covered.Annotating Procedures

You can add comments, instructions, or other useful notations on any procedure (FMS or user code) by using the Annotation feature. To make an annotation, highlight the desired procedure in the Total Access SourceBook 8.0 browser tree, then select [image] from the toolbar or Edit, Annotate Current Procedure from the menu.

Annotation Form

In addition to the annotations you enter, Total Access SourceBook 8.0 can automatically insert annotations when a procedure is created or modified. See Using Save As for more information.


Code Repository Overview

The code repository lets you store your own source code in Total Access SourceBook 8.0. The code can be from your own work, information you find, or copies and modifications of the built-in code in Total Access SourceBook 8.0. By keeping your module code in the repository, you can easily find and use code across multiple databases.

In the Team Version, the person who created the new code can also designate whether other users have rights to modify it or just view it.

System-Maintained Information

To assist with managing your source code, Total Access SourceBook 8.0 automatically tracks who and when a procedure is created and modified. This is based on how you identified yourself to the program when you logged in. See The User Login Form for more information.

With the Created By, Modified By, Created Date, and Modified Date of your procedures, you can easily use the query feature to locate your procedures (see Running a Query).

Allowing Others to Edit Procedures

As the creator of a new procedure, you are the owner and have the right to decide whether or not other users can modify the procedure. When you perform a Save As or an Add action, you can specify that "Other Users Can Modify this Procedure." If you do not use this option, you can still change the status later by using the Edit Procedure form. For more information on procedure ownership and changing ownership, the on-line help for the Total Access SourceBook Administrator.

You can tell if a procedure is marked as read-only by looking at the padlock icon on the status bar underneath the Code Window on the main Total Access SourceBook 8.0 Browser form:

Status Bar

 Locked – another user created the procedure, and designated it as read-only.

 Unlocked – you created the procedure and designated it as a read-only procedure. You have full rights to modify and delete it.

None – the procedure is not marked as read-only and can be edited by anyone (except for FMS procedures).

Importance of Backups

If you are going to use Total Access SourceBook 8.0 to manage your code, you should plan to make regular backups of the Total Access SourceBook 8.0 User Database. For more information on making backups, see Making Backups


Adding New Procedures

Because Total Access SourceBook 8.0 provides powerful repository and code-sharing capabilities, you will no doubt want to add your own procedures to the system. Using the Add New Procedure tool, you can accomplish this quickly and easily.

Opening the Add New Procedure Form

The first step in adding code is to open the Add New Procedure form. Do this by pressing the [image] [Add New Procedure] button on the Browser toolbar or by selecting Edit, Add New Procedure from the menu. After a moment, this form appears:

Add New Procedure Form

Selecting a Procedure Name

Type in the name of the new procedure. Keep the following in mind when you create new procedures:

•	All procedure names must be unique. If you enter a procedure name that conflicts with an existing procedure, Total Access SourceBook 8.0 displays a dialog informing you of the conflict. If this happens, choose a different name.

Procedure Name Conflict Form

•	Although Total Access SourceBook 8.0 does not enforce rules regarding procedure naming, most languages (including VBA) have specific rules regarding the length of procedure names, and valid characters. When creating a procedure name, be sure to follow the naming convention requirements for Microsoft Access.

•	The procedure name you assign to a new procedure is not tied to the actual code you type into the Code Window. For example, you could create a procedure named "CloseFile" and then type in the procedure in the Code Window with a name of "PurgeFile." Because Total Access SourceBook 8.0 does not interpret the code you enter in the Code Window, it has no way of knowing if the procedure name you entered into the Procedure Name field matches the definition of the procedure in the Code Window. The name is used exclusively for displaying in the Browser Tree View.

Procedure Description

Type a description for the new procedure. This description should be brief enough to be quickly understood when viewed in the browser. Since you may search on this field, enter key words or other phrases to make it easier to find with the Query tool. You may type up to 255 characters into this field.

Category

Assign the new procedure to a category in the drop down list box. Your procedure will be displayed in the Browser Tree View under the category you specify. There are a many built-in categories available. Additionally, you may create up to 99 of your own categories. See the Total Access SourceBook Administrator on-line help or the user manual for information on creating your own categories.

Marking the Procedure as Editable

Check the "Others Can Edit This Procedure" checkbox if you want to allow your colleagues to edit and/or delete this procedure. If you want to mark this procedure as read-only, un-check this box.

Total Access SourceBook 8.0 does not allow a user to edit or delete a procedure that is designated read-only if the user is not the owner of the procedure. The user who created the procedure is the owner. So, in the context of adding a new procedure, enabling the read-only setting allows only you (the owner of the procedure) to make changes or delete it.

Supplying the Code

Total Access SourceBook 8.0 needs one final piece of information about the procedure: the code itself. You have two options. You can either enter (type or paste) the code for the new procedure directly into the Code Window, or press the [Import From File] button at the bottom of the form. The code you add can actually include multiple procedures. Unlike the FMS procedures, dependent procedures are not managed separately for your code.

To import code from an existing file, press the [Import From File] button. You are prompted to select the file.

Import from File Dialog

Once you have found the appropriate file, select it and press the [OK] button. The text is imported directly into the Code Window.

Saving Your New Procedure

Once you have supplied all the information about your new procedure, press [OK] to save or [Cancel] to discard your changes.


Using Save As

Use this tool to save an existing procedure under a new name. You can then edit and change the code to suit your needs. Save As can be used on any procedure, whether it is FMS or user-written, or even if it is marked as read-only by its creator.

Select the procedure in the Total Access SourceBook 8.0 Browser Tree View. Press the [image] [Save As] toolbar button or select File, Save As from the Total Access SourceBook 8.0 browser menu. The Save Procedure As form appears:

Saving a Procedure with a New Name

Procedure Name

As a reminder, the name of the procedure you are copying is displayed at the top of the form. Beneath this, enter the new procedure name.

By default the name that is suggested is the same as the original name with a trailing suffix consisting of the value specified on the Options form. For more information on setting options see  Setting Options.

For example, if the original procedure name is "YearsBetweenDates_TSB", and you set "Acme" as your suffix, then the suggested name would be "YearsBetweenDates_Acme".

The same rules for names and conflicts that applied to creating a new procedure also apply when you use the Save As function. See Adding New Procedures for more information.

Setting Options

When you save a procedure under a new name, you can specify several options:

Include Comments – include or strip off the internal comments in the original code.

Others Can Edit This Procedure – designate whether or not other users can make changes to the new procedure.

Tab Width – the number of spaces to insert for each tab stop in the new procedure.

Include Annotations From the Existing Procedure – if you previously made annotations to the original procedure, include those annotations when you save the procedure to a new name.

Include Administrative Information in the New Procedure's Annotation – automatically add an entry to the procedure's annotation indicating the user, date, and time the procedure was saved. Here is an example of an automatically-generated annotation:

Administrative Annotation

Saving or Canceling your Changes

Press [OK] to save the procedure or [Cancel] to return to the Total Access SourceBook 8.0 browser form without adding the new procedure.

Note: If you are using the Team Version of Total Access SourceBook 8.0, it is possible that another user added a procedure with the same name as your new procedure between the time you opened the Save As form and the time you pressed [OK]. For this reason, Total Access SourceBook 8.0 re-checks to ensure that the new name is unique before saving the procedure. If there is a conflict you can choose another name for the procedure or cancel the action.


Editing Existing Procedures

You can change user code in the code repository if you created the procedure, or if the creator designated that others can modify it. If you need to change the ownership of a procedure after it is created, use the Total Access SourceBook 8.0 Administrator program.

Select the procedure you wish to edit from the Total Access SourceBook 8.0 browser tree. Press the [image] [Edit] toolbar button or select Edit, Edit Current Procedure from the menu. This form appears:

Edit Procedure Form

You cannot edit the built-in FMS procedures, but you can annotate them. To modify an FMS procedure, use the Save As function to save the FMS procedure under another name (see Using Save As).

Editing Items

The name of the procedure cannot be edited. To change the name of a user-written procedure, use Save As to save the procedure under a new name, then delete the old procedure.

In addition to changing the actual code for the procedure, you may change the Description, the Category, or whether or not other users can edit the procedure. These options are the same as those for the Save As operation. See Using Save As for more information.

You may also specify that an administrative annotation be added to the procedure which records the user and the time the procedure was modified. If you would like to view any existing annotations, or if you would like to make a manual annotation at this time, press the [Annotations] button on the Edit form. This displays the Annotation form.

Saving or Canceling your Changes

Press [OK] to save the changes to the procedure, or [Cancel] to return to the Total Access SourceBook 8.0 browser form without saving your changes.

Note: if you are using the Team Version of Total Access SourceBook 8.0, it is possible that another user modified or deleted this procedure between the time you opened the Edit form and the time you press [OK]. For this reason, Total Access SourceBook 8.0 re-checks to ensure that the procedure has not been modified since you began editing it.


Deleting Procedures

You cannot delete the FMS procedures, but you can delete user-written procedures provided you are the creator, or the creator has designated that other users may modify the procedure.

From the Total Access SourceBook 8.0 browser tree, select the procedure you wish to delete and press the [image] [Delete] button on the toolbar or select Edit, Delete Current Procedure from the Browser menu. You may also simply press the [Del] key when the selected procedure is highlighted.

You are asked to confirm the deletion. If you confirm the deletion, the procedure and any annotations you added are permanently removed. The code cannot be recovered.

This chapter provides information on some miscellaneous and advanced items such as making backups of the code repository, using the Windows API Browser, and the Total Access SourceBook 8.0 entries in the Windows system registry, and removing the program.


Making Backups

The first time you add new code to Total Access SourceBook 8.0, you are relying on the program to store and manage your code. As you add more code, your investment of time increases. You should therefore make regular backups of the file containing your work.

Total Access SourceBook 8.0 stores all of the code you add in a separate database file. This includes:

•	Code you add using the Add New Procedure function

•	Saved procedures created using the Save As function

•	Annotations of both FMS code and user code

Backing up the User Code Database

The file containing this data is an Access 95 database named TASBUC80.MDB. It is located in the directory you specified for "data files" during installation. To backup this file, follow these steps:

1.	Locate the file named TASBUC80.MDB

2.	Ensure no other users are running the Total Access SourceBook 8.0 browser or Administrator program

3.	Use your backup software to archive the file to tape or disk


The Windows API Browser

In addition to the Access code available in the Browser Tree View, a whole set of code for the Windows Application Programming Interface (API) is available through the API Browser. There are hundreds of references to functions, types, constants, etc. Due to their number and distinct difference (not VBA procedures), they are not part of the Browser Tree View.

The most common uses of Windows API commands in Access are already included in the browser tree (look under Common Dialogs and Help, System Information, and Windows API). These procedures let you easily call the Windows functions using an VBA procedure call with additions to your module's declarations section.

The API Browser is for more advanced situations and lets you search all the Windows API commands. This section shows you how to invoke the API Browser and use it to quickly find the information you need.

Note: These are very advanced commands and are included as references. You must be familiar with Windows API calls before using them. FMS does not provide technical support for using these commands. Please reference your Access and Windows help files for more information.

Why the Windows API Is Important

As a developer, you probably faced situations where VBA does not supply the functionality your application needs. One of the common solutions to this problem is to make calls to routines that the Windows operating system provides. These calls are made through the Windows Application Programming Interface (API).

The Windows API provides hundreds of procedures for working with the operating system, including graphic routines, system information, messaging and user-interface controls. Unfortunately, there are no tools built into Access to find the name of the procedure you want, or the parameters and return value of that procedure. The Windows API browser in Total Access SourceBook 8.0 solves this problem by providing the full definition of all functions, subroutines, constants and structures for the Windows 32 (32-bit) application programming interface.

See Also

Using the API Browser


Using the API Browser

To start the API Browser, select Tools, API Browser from the menu. After a moment, the following form appears:

Windows API Viewer

Selecting the Type

First, select the type of information you are looking for. The following types are available:

Declares

Displays a list of all functions and subroutines in the selected API.

Types

Displays a list of all types (user-defined types) in the selected API.

Constants

Displays a list of all constants in the selected API.

Searching the Available Items

After selecting the appropriate type, a list of available items appears. Use the scrollbar to navigate through the list to find the one you are interested in. You can also type the first letter of the item to locate it. Once you have found your match, highlight the item. The full information on the item appears in the Current Item text box.

Copying the Value to the Clipboard

Press the [Copy] button to copy the contents of the Current Item text box to the Windows clipboard. You can then paste the value into an Access module using the [Ctrl] [V] key sequence or by selecting Edit, Paste from the menu.


Registry Settings

Total Access SourceBook 8.0 keeps its settings in the Windows system registry. These settings control how Total Access SourceBook 8.0 displays information, saves default values, and remembers prior settings.

The Total Access SourceBook 8.0 registry settings are located in the Windows system registry in the following key location:

HKEY_LOCAL_MACHINE\SOFTWARE\Fms\
Total Access SourceBook 8.0\8.0\Settings

Ordinarily there should be no need for you to modify these settings directly. Any settings can be changed from the Option form, or by ordinary use of the program.


Uninstalling

Total Access SourceBook 8.0 conforms to the Windows 95/NT convention of installation and removal. To uninstall Total Access SourceBook 8.0, simply start your Windows Control Panel and run Add/Remove Programs. Choose Total Access SourceBook 8.0 8.0 from the list and press [Remove]. All the files except for your code repository database (TASBUC80.MDB) are removed.