Copyright: ©1995-97, FMS Inc.


Help Contents
Contents

Introduction

Using the Help File

Product Highlights

New Features in Version 8.0

Our Internet Home Page

Installation and Startup

Installation Overview

System Requirements

Upgrading from Version 2.0

Installing

General guidelines

Running

The Toolbar

Troubleshooting Problems

Technical Support

VBA Coding Techniques

Writing Better Code

Summary

Builders

Builders Overview

Placing Results in your Modules

Procedure Builder

Property Procedure Builder

SQL Builder

Long Text Builder

Message Box Builder

Select Case Builder

Database Tools

Overview

Code Cleanup

Introduction to Code Cleanup

Running Code Cleanup

Setting Options

Finishing Up

Other Tools

Overview of Other Tools

Constants Generator

List Fill Function Builder

Access Information

Fix Screen

Bookmarker

Additional Topics

Program Files

Uninstalling


Using the Help File

This Help File is designed to give you all the information you need to use Total Access CodeTools 8. It is assumed that you are familiar with using Microsoft Windows and Microsoft Access. If not, please refer to the documentation accompanying those products. This help file is organized into the following sections:

Introduction

Introduces you to Total Access CodeTools 8, previews product highlights, and shows what's new in this version.

VBA Coding Techniques and Tips

Discusses techniques you can use to write better VBA code.

Builders

Explains the Builders included in Total Access Code Tools 8:

•	Procedure builder

•	Property Statement builder

•	Message Box builder

•	SQL String builder

•	Long Text builder

•	Select Case builder

Database Tools

Explains the database tools for opening, designing, and copying database objects.

Code Cleanup

Explains the Code Cleanup tool used to standardize existing code with a variety of options.

Other Tools

Explains how to use the remainder of the tools in Total Access Code Tools 8, including:

•	Database Utilities

•	Constants Generator

•	List Fill Builder

•	Access Information

•	Fix Screen Utility

•	Bookmarker Utility

Additional Topics

A list of the files installed by Total Access Code Tools 8 and how to uninstall the program.


Product Highlights

Total Access Code Tools 8 is a collection of tools and utilities that make it easier to:

•	Write solid code

•	Create maintainable code

•	Clean up inherited code and standardize existing code

•	Reduce the drudgery of common coding tasks

To understand how Total Access Code Tools 8 works, it is important to look at the program as a collection of tools to use during various phases of writing code.

Total Access Code Tools 8 provides the features you need to create robust applications from beginning to end. The tools are presented in a tabbed toolbar that appears on top of your Access workspace. Here are some of the product's highlights:

•	Code Cleanup makes code adhere to your standards by adding error handling, standardizing indentation and comments, applying variable naming conventions, and more. Use this tool on prototype code and inherited projects to quickly apply consistent standards.

•	Procedure builder makes it easy to quickly create new procedures that adhere to your standards for comments, error-handling, and naming conventions.

•	Property builder simplifies the process of creating Property Let/Get/Set statements.

•	The Select Case, SQL, and Long Text builders make working with common code objects a snap.

•	The Message Box builder lets you visually create a message box and paste the resulting code into your modules.

•	The Bookmarker utility makes it easy to keep development notes on specific objects in your database.

•	The Access Information tool quickly displays all the settings and environment options for the current installation of Access.

•	The Fix Screen utility resets your workspace while you are debugging your code.

•	Database Tools give you a convenient launch pad for opening, designing, and copying database objects without having to hunt for the Access database window.


New Features in Version 8.0

The most obvious change in Total Access Code Tools 8 version 8.0 is its support for Microsoft Access 97. All of the new language elements, such as VBA and class modules are supported, and the product runs as a native Access 97 add-in. Additionally, many new features have been added to make Total Access Code Tools 8 even more valuable:

•	The New Property builder makes it easy to create Property Let/Get/Set statements for class modules.

•	BookMarker is a new feature that makes it easy to store development notes and tasks lists for each object in your database.

•	The SQL and Long Text builders support the VBA line continuation character.

•	The Message Box builder supports the new intrinsic constants and allows system-modal message boxes.

•	New Database Tools make it easy to get object names, copy objects and rename objects without using the Access database window.

•	The new Access Information function supplies detailed information about your system and Access database.

•	The Fix Screen utility allows you to quickly recover from application errors in your database by turning the hourglass cursor off, turning screen painting on, and refreshing the Access database window.

•	Support for new data types such as Boolean, byte and date is available throughout the product.

More:

Code Cleanup Enhancements

What Happened to Code Delivery?


Code Cleanup Enhancements

•	Support for the new 32-bit Visual Basic for Applications (VBA) syntax including class modules.

•	Variable naming convention for new data types.

•	Optionally apply naming conventions to user defined type members.

•	Ability to sort procedures within a module or class module.

•	Option to split grouped Dim statements into multiple lines.

•	Customized comments for class modules.


What Happened to Code Delivery?

Total Access Code Tools 8 2.0 provided a feature called Code Delivery. This tool created a "delivered" version of your code by removing whitespace, renaming variables to short names, and adding line numbers to aid in troubleshooting code. The result was a version of your database that was smaller, faster, and more secure. This feature is not in version 8.0.

Access 97 can now create MDE files that address many of the reasons Total Access Code Tools 8 2.0 delivered databases.

MDE Files

In Access 97, the "Make MDE File" option from the Access Tools|Database Utilities menu lets you achieve a result similar to the Code Delivery feature in Total Access Code Tools 8 2.0. An MDE database also has several advantages over the CodeTools "delivered" version. First, the MDE version has no source code at all, so you never need to worry about code security—the code is not there, so it can not be broken into.

The second advantage is that MDE databases are read-only when it comes to designing forms, reports, and modules. If you open an MDE, you cannot modify, add, or delete these objects. This provides your application with an additional level of security.

Since the MDE capability is built into Access, there is no need for CodeTools to have the Code Delivery feature. For more information on MDE files, search the Access online help for "MDE".

However, the ability to add line numbers for debugging purposes is still supported. See the section on "Code Cleanup" for more information.


Our Internet Home Page

To provide you with the most current information about our products, FMS can be reached on the World Wide Web at www.fmsinc.com. Our site is constantly being updated with news and features for the Access community. Please drop by and take a look.

Product Announcements and Press Releases

Read the latest information on new products, new versions, and future products. Press releases are available the same day they are sent to the press. Sign up in our Feedback section to have press releases automatically sent to you via E-mail.

Product Descriptions and Demos

Detailed descriptions of all our products are available. Each product has its own page with information about features and capabilities. Demos are also available.

Product Registration

Register your copy of Total Access Code Tools 8 on-line.

Utilities and Free Software

Download free demos and utilities, including the FMS PowerPack, Text FX, Information Class, and DAO Explorer, 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 web sites that offer Access news, utilities, and other useful information.


Installation Overview

Since Total Access Code Tools 8 is implemented as a Microsoft Access add-in, it is unlike a typical executable program that you run directly. Its tools are stored in a Microsoft Access library database that is used directly from within Access.

There are two basic steps to installing an Access add-in. The first step copies the necessary files to your computer's hard disk. The second step updates the system registry that Access uses to identify available add-ins. Total Access Code Tools 8 includes a complete setup program to automate these tasks for you.


System Requirements

Total Access Code Tools 8 is implemented as a Microsoft Access library database and runs within Access. As such, it has the same system requirements as Microsoft Access 97:

•	486 processor or better (Pentium recommended)

•	16 MB RAM (32 MB recommended)

•	10 MB of free disk space to install the product. Additional disk space may be necessary depending on the complexity of your modules.

•	Windows 95 or Windows NT 3.51 or 4.0. If you are using NT version 4, you should have service pack 2 (or later) installed. If you are using Windows NT 3.51, you should have service pack 5 (or later) installed.

•	Microsoft Access 97 (Access 8.0)
Total Access Code Tools 8 8.0 does not support earlier versions of Microsoft Access. It also does not work under the Microsoft Access run-time environment.

Total Access Code Tools 8 version 2.0 is also available for your Access 2.0 databases. Contact FMS for information about pricing and availability.


Upgrading from Version 2.0

Just as Access 2.0 and 97 can reside on the same machine, Total Access Code Tools 8 2.0 and 8.0 can be on the same machine provided they are installed in separate directories. Version 8.0 is a completely separate program and does not use any files from version 2.0. If you are no longer using Access 2.0, you may want to delete Total Access Code Tools 8 2.0 from your machine. Please follow the instructions in your 2.0 manual for uninstalling the program.


Installing

The Total Access Code Tools 8 diskettes are not copy-protected, but do contain compressed files that need to be expanded. A Setup program automates the process of copying the files to your system.

Start the Setup Program

A standard Windows setup program is used:

•	Place Disk 1 in your computer's disk drive.

•	From Windows 95 or NT, press the Start button and select RUN.

•	Type in A:SETUP.EXE (assuming A: is the letter for the floppy disk drive containing the disk) and press [Enter].

Enter Registration Information

The Setup program prompts you for your name, organization, and serial number. The serial number is on the first disk.

Specify Destination Folder

Specify the folder in which Total Access Code Tools 8 will be installed. If the folder does not exist, it is automatically created. For best performance, Total Access Code Tools 8 should be installed on a local drive.

If your local hard disk space is limited, you may want to install the program in a network folder with plenty of disk space. Regardless of where you install the program, it can work on databases stored locally or on the network.

Installing Total Access Code Tools 8 on a network drive allows you to save space on your local system and allows multiple network users to run Total Access Code Tools 8 (but not at the same time). You must purchase a copy of Total Access Code Tools 8 for each developer using the program. FMS offers quantity discounts to economically add developer counts.

Backup Replaced Files

Total Access Code Tools 8 installs files that may replace your existing files. You can specify the folder in which to store files replaced by the setup program in case you need to restore them at some point in the future.

Confirm Installation

The setup program asks for confirmation to begin installation. If you confirm, no additional input is required. Files are copied to your system and the Windows registry is updated. When finished, the Setup displays the README.TXT file. It contains the latest information about Total Access Code Tools 8. Please make sure to read it.

Total Access Code Tools 8 is now installed and available in Access 97 under the Tools|Add-Ins menu.


General guidelines

Before using Total Access Code Tools 8 on your database, read this for some basic guidelines on using the product.

Make Regular Backups

Your databases represent a far greater amount of time and effort than most other computer files. It is not unusual to spend many months of development on one database file. By contrast, the typical word processing document, spreadsheet or presentation takes far less work. Because of this, you should make frequent backups of you database files.

Additionally, if you plan to use Code Cleanup on your database, you absolutely MUST make a backup of your database before running the program.

Access databases represent a lot of work—protect them accordingly.

Permissions and Security

For Total Access Code Tools 8 to interact and/or modify your database objects, you need to have adequate permissions. If you are using Access security on your database, be sure to log in as the owner of the database. Also, the account you have logged in under needs full permissions to objects for Total Access Code Tools 8 to modify them.

Avoid Using Shared Databases

The Code Cleanup feature of Total Access Code Tools 8 makes extensive changes to your forms, reports, and modules. You should never run this feature on a shared database.

Development of Access database applications should typically be done on a local, exclusive copy of the database. This prevents other users from changing objects that you may be working on, and prevents your changes from interfering with a production application.

Make Sure Your Database Is Not Corrupt

Database corruption is a fact of life—it happens. Before using Total Access Code Tools 8 (or any add-in program for that matter) on your database, be sure that your database is in its most efficient form. Do this by performing a Repair operation, and then a Compact operation. Both of these tools are available from the Access Tools|Database Utilities menu.

Ensure Adequate Local Disk Space for Code Cleanup

When you run the Code Cleanup tool, Total Access Code Tools 8 runs through a complex set of operations to read and parse your module code. This can take a fair amount of temporary disk space. If you plan to use Code Cleanup, verify that there is enough free disk space on your local drive. A good rule of thumb is to have free space equal to between 2-3 times the size of the database you are running Code Cleanup on.

Issues with Visual SourceSafe and Code Cleanup

Microsoft Access 97 integrates with Microsoft Visual SourceSafe. This tool (misnamed "Source Code Control" by Microsoft) allows multiple developers to work on an Access project with version control and check in/out facilities. You cannot save changes to objects that are under SourceSafe control unless you have the object checked out. Because Code Cleanup modifies the module code in every module in your database, you need all the forms, reports, and class and standard modules checked out.

See the Code Cleanup section for more information on Code Cleanup and SourceSafe integration.


Running

Total Access Code Tools 8 runs from within Microsoft Access. If you are familiar with Microsoft Access, you should be very comfortable using Total Access Code Tools 8.

Running from the Tools | Add-Ins Menu

Total Access Code Tools 8 is invoked from the Access menu: Tools|Add-Ins Menu|Total Access Code Tools 8. If Access was open during installation, you need to close and re-start Access to have Total Access Code Tools 8 appear on your menu.

To run the program, follow these steps:

1.	Start Microsoft Access 97.

2.	Open the database you want to work on. Note that if you want the Code Cleanup tool, you should first make a backup of your database and open it in Exclusive mode. See Running Code Cleanup  for more information.

3.	From the Tools|Add-Ins menu, select Total Access Code Tools 8.

After a moment, the Total Access Code Tools 8 toolbar appears. This tabbed dialog floats over the Access workspace, giving you easy access to the tools included in the program.

Context Sensitive Help

Throughout Total Access Code Tools 8, you can press the [Help] button or [F1] to bring up context-sensitive help. The help system is fully indexed with many jumps between topics.


The Toolbar

When you launch Total Access Code Tools 8, a toolbar appears:

Total Access Code Tools 8 Toolbar

If this doesn't happen, see the Startup Errors section for possible remedies.

All of the features of Total Access Code Tools 8 are accessed through the Total Access Code Tools 8 Toolbar which floats above your workspace.

There are two views of the toolbar available: Full and Small. When you first start Total Access Code Tools 8, the Full toolbar appears.

More:

Full Toolbar

Small Toolbar

Using the Toolbar


Full Toolbar

The Full toolbar shows all available tools organized into tabs. This view makes it easy to see the tools organized by category, and shows graphic bitmap buttons for each tool. This toolbar is the larger of the two toolbars. Use it when you are working at a higher resolution (for example, 1024x768 or higher) where screen space is not a constraint.

The following images show the tools available on each tab of the Full toolbar:

Full Toolbar, Builders Tab

Full Toolbar, Database Tab

Full Toolbar, Cleanup Tab

Full Toolbar, Other Tab


Small Toolbar

The Small toolbar shows all tools except the Database tools. Instead of tabs and buttons with bitmaps, tools are invoked through simple text buttons. This view is better suited for development work on systems with lower resolution screens, such as 640x480.

The Small Toolbar

See Also:

New Procedure Builder

New Property Procedure Builder

SQL Builder

MsgBox Builder

LongText Builder

Select Case Builder

Code Cleanup

ListFill Builder

FixScreen Utility

Bookmarker Utility

Info Utility

Constants Generator

Full Toolbar


Using the Toolbar

The following tips show how to use the toolbar effectively:

•	To change from the Full Toolbar view to the Small Toolbar view, select the [Other] tab and press the [Small] button. Conversely, to change from Small Toolbar view, press the [Full Toolbar] button.

•	Occasionally, you may want to minimize the toolbar to get more space on the screen to work with your Access objects. Press the minimize button on the toolbar's upper right border to do this.

If you close the toolbar, you must select Total Access Code Tools 8 from the Tools, Add-Ins menu to restart it.


Troubleshooting Problems

This section includes information that can help you troubleshoot common problems with Microsoft Acces 97 and Total Access CodeTools.

More:

Startup Errors

Solving Other Problems


Startup Errors

Most errors that occur with Total Access Code Tools 8 happen when you first start it. This explains the most common problems and gives suggestions on how to fix them.

Microsoft Access cannot find Wizard, or there is a syntax error in the Declarations section of a Visual Basic module.

This error is caused by incorrect information in your Windows registry. Microsoft Access uses the registry to store information about the add-ins available. When you install Total Access Code Tools 8, it places several entries in the Windows Registry to identify the location, the name of the Total Access Code Tools 8 library database, and the name of the main Total Access Code Tools 8 function. If these entries are incorrect, this error message appears. Another cause may be corruption in the Total Access Code Tools 8 program. Re-install Total Access Code Tools 8 to eliminate this problem.

The current database is not updateable.

You have opened your database in read-only mode, or another condition has forced Microsoft Access to open the database in read-only mode (for example, a database on a CD-ROM).

For Total Access Code Tools 8 to work, it must be able to update your database. Re-open your database in normal mode (with valid permissions), or correct the condition that forced Microsoft Access to open the database as read-only.

This copy of Total Access Code Tools 8 is in use.

Total Access Code Tools 8 can only be run by one user at a time. If more that one user attempts to start Total Access Code Tools 8 at the same time, the second user sees a dialog box saying the copy of Total Access Code Tools 8 is already in use.

This message also appears if you attempt to run Total Access Code Tools 8 in two instances of Access on the same computer. Press [OK] to return to Microsoft Access and try again later.

Total Access Code Tools 8 ensures that only one user is using it by exclusively locking itself. If Microsoft Access exits abnormally while Total Access Code Tools 8 is running, an LDB lock file may be left in place that erroneously reports Total Access Code Tools 8 being in use, even though no one is using the program. If this happens, you need to manually delete the lock file.

To do this, make sure no other users are running Total Access Code Tools 8. This step is very important. If you manually delete the lock file while a user is running Total Access Code Tools 8, you may cause that user's system to crash.

Exit Microsoft Access. From the DOS prompt or from Windows Explorer, locate the Total Access Code Tools 8 folder, delete the lock file (TACTL8_C.LDB), restart Microsoft Access, and run Total Access Code Tools 8 again.


Solving Other Problems

This section covers information that can help you diagnose and solve other problems that may occur while running Total Access Code Tools 8.

Error 9: "Subscript Out Of Range"

This occurs when the Code Cleanup program encounters module code that in cannot correctly parse. This error should not occur—if it does, please contact FMS technical support for assistance. In most cases, we will need to see your module code in order to replicate and fix the problem.

Error 2004: "Out Of Memory"

Out of memory errors while running Total Access Code Tools 8 are almost always caused by a lack of disk space on the drive used for the Windows swap file. Freeing up disk space on that drive (a good target is 100 MB free) will prevent this.

Error 3075: "Function isn't available in expressions in query expression"

This error is due to a bug in Access 97. When you add References to your database using the Tools, References dialog while a module is open, Access 97 adds additional information to the database. This information may cause queries to stop working. When this happens, queries needed by Total Access Code Tools 8 fail with this error message. To solve this problem, you must delete all References in your database and recreate them manually. To do this, open any module in your database and select Tools, References from the Access menu. Every item in this list that has a check next to it is an installed Reference. Click on each installed Reference and write down the name of the reference, and the name of the file containing the referenced object. After this is done, delete each reference and recreate each reference using the information you wrote down. When this is done, close your database and close and re-start Access. Open any module and select Compile and Save All Modules from the Debug menu. Once this is done, the error 3075 problem should be solved.

Using Other Programs while Running Total Access Code Tools 8.

When using the Code Cleanup feature of Total Access Code Tools 8, do not minimize the Microsoft Access window or the program may stop working. Because Total Access Code Tools 8 runs within Microsoft Access and is written in VBA, it does not work correctly if Microsoft Access is minimized. If you are using the other features of Total Access Code Tools 8, there is no problem with minimizing Access.

Access Locks Up While Running Code Cleanup

Code Cleanup rewrites all the module code in your database. To do this it must open every form, report, class and standard module in your database. It then reads the contents of the modules and stores the text in a table. Then it modifies the code according to the options you specified in the Code Cleanup Options form. When the modification is complete, it re-opens each of your modules and writes the code back.

Because of the intricacies of how Access works with VBA, several problems can occur with this process. If you have problems while running Code Cleanup, follow these steps.

1.	Determine which module is causing the problem. As Code Cleanup runs, it displays a status form letting you know what it is doing. Keep an eye on this form and write down the name of the module that is being worked on when Code Cleanup stops working. Close and re-start Access and re-open your database. Try to open the problem module in design view. Does Access generate any errors? If so, correct the problem reported by Access and try Code Cleanup again.

2.	Ensure there are no database corruption issues. To do this, follow these steps. First, make a copy of your database. Then repair the database using the Tools, Database Utilities, Repair Database option from the Access menu. Next, compact the database using the Tools, Database Utilities, Compact Database option from the Access Menu. Finally, create a new blank database and import all objects from copy of your database. Do this using the File, Get External Data, Import option from the Access menu and select all objects. Note that you may need to do additional work to recreate security, startup and configuration options that exist in the original database. Once this process is complete, run Code Cleanup on the new database to see if the problem is solved.

3.	Ensure that the VBA compiled state is not corrupt. VBA in Access 97 stores two versions of your module code. The "source" version is the actual text you see in the module window. The "compiled" version is the compact tokenized version that is visible only to VBA. Compiled code is created from Source Code when you choose the "Compile and Save All Modules" option from the Access Debug menu. In many cases, Access 97 contains problems where the two versions of code no longer match. This problem is very hard to identify because the database's code will compile and run fine. Its only when CodeTools tries to programmatically change your module code that this issue comes to light. To solve this issue, you must completely "decompile" your database code. To do this, you must start Access with the special /DECOMPILE command line argument. The easiest way to do this is to select Run from the Windows Start Menu. In the Command box, type in: MSACCESS.EXE /DECOMPILE and press [Enter]. Access 97 will start in the Decompile mode. Once Access is open, open the database you are having problems with. Access will display several messages about VBA conversions. Once the process is complete, close your database and exit and re-start Access. Finally, re-open your problem database, open a module, and select the Compile and Save All Modules option from the Access Run menu. Your database code should now be free of corruption.

Code Doesn't Compile After Running Code Cleanup

Code Cleanup makes many changes in your module code. In most cases, the resulting module code will compile without problems. However, there are cases where code will not compile after it is run through Code Cleanup. To solve this, you must identify each place in the modified code where syntax errors occur. Do this by opening a module in the modified database and selecting the Compile and Save All Modules option from the Access Run menu. If syntax errors occur, work through each one.

You may find it helpful to have another copy of Access running with the original, unmodified database open. This way, you can see the original source code and use it as a reference point while fixing syntax errors in the modified version.

If you find that there are too many errors to fix, discard the modified version of the database and go back to your original database. Fix any problems there, make a backup and run Code Cleanup again.


Technical Support

When you encounter a problem or error, please consult the help file and printed user manual for information on Troubleshooting.

Click Here for Troubleshooting Help

If after working with the troubleshooting information, you still cannot solve the problem, please contact our technical support department. We are very interested in making sure you are satisfied with our product and will work with you to solve the problem.

The Error Log File

When Total Access Code Tools 8 encounters an error that it cannot recover from, it stops, closes all open objects, and displays the Error Form:

This form lets you know that an error has occurred and what the error message is. Use the error message and the Troubleshooting section of this help file to try and resolve the error. Whenever an error of this type occurs, the program also creates a text file called TAC_ERR.TXT. This file is stored in the directory where you installed Total Access Code Tools 8 and contains information that can help FMS solve your problems. To view the contents of the error file, press the [View Error File] button. This option opens the error file using the Windows Notepad application.

For Fast and Accurate Technical Support

The best way to contact us is via fax or email. With a written explanation of your problem, we have a much better chance of solving your problem, and can respond more accurately and promptly. If an error file was generated, please include it in your email or fax.

•	E-Mail:	support@fmsinc.com

•	Fax:	703-448-3861

•	Internet:	www.fmsinc.com


Writing Better Code

Before you dive in and start using the code in Total Access Code Tools 8, please take some time to read these topics. They contain important information that can help you understand and write better VBA code.

More:

#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 to Avoid Hard-coded Values

#11: Use Variable Naming Conventions

#12: Choose Meaningful Variable Names

#13: Add Comments

#14: Use Standard Indentation

#15: Avoid Single-Line If Constructs

#16: Use Select Case Correctly


#1: Use Option Explicit

This is not a tip, but 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.

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 get an inventory of variables belonging to the procedure. You can 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 is 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 that 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 (text, numbers, dates, etc.), 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. Type declaration characters were used in Basic before explicit syntax for type casting. They still exist in VBA today for backward compatibility, but good VBA code avoid type declaration characters because they are considered obsolete and hard to understand. Also, there are only type declaration characters for a small subset of the data types that VBA supports. If you want a variable to be a string, dim it "As String".

You should also avoid using 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 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 on 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.

You should also use "Option Private Module" if you are developing library databases for use by other databases. Add "Option Private Module" to modules that should not be visible to the referencing database.


#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. If it doesn't return a value, it should be a sub.


#8: Implement Robust Error Handling

When an untrapped error occurs in your VBA application, a number of interesting and unwanted things can happen. First, a dialog displays the error message, usually with Debug, 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 has an 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 a rather large drawback: you must actually type physical line numbers in your code for Erl to return anything meaningful. Fortunately, the Code Cleanup Tool in Total Access CodeTools has an option to add line numbers for you.


#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. With the exception of On Error commands, these constructs (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 (create a procedure instead).

Equally important, no procedure should ever have more than one exit point. Procedures should always exit at the end of the procedure. Avoid using commands such as "Exit Sub" or "Exit Function" within the body of a procedure. These commands make it very difficult to understand how the procedure finishes and by-passes any cleanup code you may need at the end of the procedure. It is a very common cause for bugs and very hard to detect since the Exit commands are usually triggered by specific conditions that are hard to replicate.


#10: Use Constants to 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 dblCost As Double
Dim intQuantity As Integer
dblCost = 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"
End If

In this example, hard-coded values cause two problems. The first is caused by the 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 difficult to later make global changes using the Access search/replace feature.

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 their purpose. But often, in the heat of programming, it is all too 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, 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 own 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:

More:

Application (global) comments

Module level comments

Procedure level comments

Line level comments


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 and make code harder to read.


#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. 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 your code has lots of nesting, 2 spaces may work better for you. This can be set in the Modules section of the Access Tools|Options menu selection.

Indentation also aids 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. It also reveals if the closing statement is missing.


#15: Avoid Single-Line If Constructs

Placing an If condition and its action on the same line leads to code that is difficult to read and to maintain. There is always the chance that your If statement will need to be modified with an additional line of code, at which point you'll have to break the code out into multiple lines anyway. Also, without a new line for the action part of the If statement, the temptation is to skip commenting the action at all. So instead of this:

If fInitialized Then DoCmd.OpenForm "frmOpen"

Do this:

If fInitialized Then
  DoCmd.OpenForm "frmOpen"
End If


#16: Use Select Case Correctly

The Select Case construct makes it easy for you program to branch based on multiple possible values for a variable or expression. There are two things to be aware of when using Select Case. The first is a matter of style. Avoid putting an action on the same line as a Case statement. The following code samples illustrate this:

Select Case intType
  Case acTable: RunTableProc
                strType = "Table"
  Case acQuery: strType = "Query"
End Select

Bad Style: Difficult to read and maintain

Select Case intType
  Case acTable
    RunTableProc
    strType = "Table"
  Case acQuery
    strType = "Query"
End Select

A Better Way: Easier to read and expand in the future

In the first example, it is difficult to quickly see which is the condition and which is the action. Also, when you come back later to add code to the action clause, you end up having to format it correctly anyway—the multiple actions won't fit on a single line anymore! Save yourself future problems by formatting Select Case blocks correctly at the outset.

Here's another tip that can save hours of frustration as you try to debug a complex application. Unless you always have a Case Else clause in your Select Case blocks, unanticipated values will not be acted upon.

For example, assume your application allows the user to add new employee categories. Your application is also has VBA code that gives each employee a raise at the end of the year based on their job type. Lets assume that the user adds "Computer Programmer" as a category and a few people are hired that year as Computer Programmers. Finally, this is what your Select Case statement to handle raises looks like:

Select Case intEmployeeType
  Case EmpType_Manager
    intRaise =10
  Case EmpType_Clerical
    intRaise = 5
  Case EmpType_Driver
    intRaise = 2
End Select

Guess who isn't getting a raise this year? If you follow the practice of always adding a Case Else clause, problems like the one shown above are easier to handle. For example, the above code could be re-written to prompt the user for the raise amount in the case of new employee types:

Select Case intEmployeeType
  Case EmpType_Manager
    intRaise =10
  Case EmpType_Clerical
    intRaise = 5
  Case EmpType_Driver
    intRaise = 2
  Case Else
    Beep
    intRaise = InputBox ("Please enter raise amount.")
End Select


Summary

Writing error-free and maintainable code is hard work—it requires a full time commitment to covering all the details. By following the advice in this chapter, and using the tools available in Total Access Code Tools 8, you should be on your way to achieving better code.


Builders Overview

The following builders are available in Total Access Code Tools 8 Builders Tab.

Full Toolbar, Builders Tab

Procedure Builder

This builder allows you to quickly create new procedures that adhere to a set of standards that you define. It allows you to specify comment headers, error handling, indentation, naming conventions, and global procedure identifiers.

More Information

Property Builder

You can write property procedures in VBA that allow you to define properties of the objects you define in class modules. The process of creating such procedures can be difficult. You can use the Total Access CodeTools Property builder to quickly build the property procedures necessary to implement a property in a class module.

More Information

SQL Builder

Adding a long SQL string into a module and easily viewing it usually requires breaking it into a multi-line statement. The SQL Builder automates this by allowing you to choose a saved query in your database and automatically convert it to a series of VBA lines that assign the string to a variable.

More Information

Long Text Builder

Like SQL text, you may need to assign a long text string to a variable in your code. The Long Text Builder takes a string and automatically generates the multi-line code necessary to assign the text to a variable.

More Information

Message Box Builder

Message boxes are a common feature in Access applications. Unfortunately, Access does not provide tools for visually creating message boxes. Even experienced developers need to refer to the manual or help system to determine the values of icons and button options. The Message Box builder lets you visually create a message box statement or function.

More Information

Select Case Builder

Typing Select...Case statements can be a tedious process, especially when there are many conditions. The Select Case Builder allows you to quickly create such statements.

More Information


Placing Results in your Modules

After using the builders to generate your code, you can copy the results to the clipboard by pressing the [Copy to Clipboard] button, or insert it directly in your current module code by pressing the [Paste to Module] button. Note that the [Paste to Module] button is only available in the Procedure Builder and Property Builder. This is because Access 97 only allows add-ins to paste entire procedures in under programmatic control. All other tools require that you use the Windows clipboard to move code from Total Access Code Tools 8 to your modules.

More:

Copy to Clipboard

Copy Body To Clipboard

Paste to Module


Copy to Clipboard

This is useful if you start the builder when you are not editing a module at the exact location where you want to add the new code. After sending the results to the clipboard, you can open a module, move to the appropriate location, and paste the results via Edit|Paste or Ctrl+V. You can also paste the code in multiple locations or into other programs such as NotePad.


Copy Body To Clipboard

This button is available in the Procedure Builder and performs the same operation as the [Copy To Clipboard] button with one exception. It does not copy the procedure declaration or the End Sub/Function line. The result is that the clipboard contains only the body of the procedure. This operation is useful when you want to use the error handling and comment text generated by the procedure builder in an existing procedure.

To best utilize this feature in conjunction with Event Procedure creation, follow these steps:

1.	Position the cursor in the event in the form or report design view property sheet you want to create a procedure for.

2.	Press the Builder button to the right of the property text box. Select "Code Builder" from the list of available builders.

3.	Microsoft Access creates an empty event procedure and places you in the form or report's module.

4.	Use the mouse to highlight the new procedure name. Then copy the highlighted text to the clipboard. Note that you should only highlight the procedure name, not any of the parameters, or the word "Function" or "Sub".

5.	From the Total Access CodeTools Toolbar, select the Procedure Builder. Once the Procedure Builder appears, paste the procedure name into the "Procedure Name" text box.

6.	Be sure to set the Procedure Builder to Sub or Function as appropriate. Note that all event procedures are Subroutines.

7.	Press the [Tab] key to view the generated code. Make any changes to the options you may need.

8.	When the generated code is complete, press the [Copy Body to Clipboard] button.

9.	You are now back in your form or report's module. Position the cursor in the event procedure where you want to paste the new procedure body text. Press [Control][V], or select Edit|Paste from the menu to insert the new procedure code.

Note that the Procedure Builder is designed to create *new* procedures, not to edit existing ones. For this reason, we have not integrated the Procedure Builder into the Microsoft Access Builder button.


Paste to Module

The [Paste to Module] button is disabled if there are no modules open on the Access workspace when you start the Procedure Builder or Property Builder.

To use the [Paste to Module] feature, you must have your cursor in the module window prior to starting a builder. Total Access Code Tools 8 pastes the builder's results at your cursor's current position. The code pasted to your module is also placed on your clipboard so you can manually paste it in multiple locations. Note that this operation overwrites any data currently in the clipboard.

Depending on the builder selected, the code may not be inserted with the optimum indentation. To adjust your code, use the Access block indentation feature. Highlight the block of text to adjust, then use the [Tab] and [Shift][Tab] keys to move the code one indentation width to the right or left.


Procedure Builder

The Procedure Builder allows you to create new procedures that conform to a set of standards that you define. You can easily control the following aspects of the new procedure:

•	Procedure name

•	Procedure type: Sub or Function

•	Procedure scope: Private, Public, or Default

•	Return type for functions

•	Comment structure

•	Error handling code

•	Naming conventions for function names

The following is an example of a procedure created by the Procedure Builder. This code was created by typing the procedure name "GetTable"—the builder took care of the rest:

Function strGetTable () As String
  ' Comments   :
  ' Parameters :
  ' Created    : 9/26/97 10:19:53 AM Admin
  ' Modified   :
  '
  ' --------------------------------------------------------

  On Error Goto PROC_ERR:

  Exit #X#

PROC_ERR:
  MsgBox "The following error occured: " & Error$
  Resume Next

End Function

More:

Invoking the Procedure Builder

Options

Using the Generated Code


Invoking the Procedure Builder

To invoke the Procedure Builder, follow these steps:

•	Put your cursor anywhere in the module window where you want the new procedure to be added (this step is not necessary if you are sending the results to the clipboard).

•	From the "Builders" tab of the Total Access Code Tools 8
toolbar, press the [New Proc] button.

The Procedure Builder form appears:

Procedure Builder Form

Enter the name of your new procedure. When you move from the Name field, the form updates the Generated Code text box. As you select and change options, the Generated Code text box automatically reflects your settings. These options are available:

Name

Type in the name of the new procedure. Do not enter naming conventions as these are automatically applied for you (if set under Options).

Sub or Function

Specify the procedure type by selecting Sub or Function.

Returns

If a function is being created, specify the variable type of its return value by selecting a variable type from the Returns combo box. If you are creating a Sub, this option is disabled.

Scope

Select the scope of the procedure. It can be Public, Private, or Default. The "Default" option causes the procedure code to be generated without a Public or Private keyword. For regular modules, a default procedure is Public. For class modules (including forms and reports), the procedure is Private.

Static

If you want the procedure to be static (its local variables are preserved between calls), check the Static check box.


Builders - Procedure Builder - Options
Options

Press the [Options] button to open the Procedure Builder Options form. This form contains options that are modified less frequently.

Procedure Builder Options Form

More:

Insert Comment Header

Insert Error Handling Code

Use Procedure Naming Conventions


Insert Comment Header

You can specify the comment header or structure you want in each new procedure. Check this check box to activate this option. The Indent Code check box lets you control whether the comment block should be indented by one tab stop. The Tab Width text box specifies the number of spaces to use when indenting the comment header.

Press the [Edit] button to specify the comment:

Edit Procedure Comment Text Form

You can modify this text to suit your particular documentation and coding styles. The lines can be just comments or actual commands. If you add commands, ensure that the syntax is valid.

In addition to your normal text, you can also designate values to be filled in by Total Access Code Tools 8. There are several character sequences with special meaning. The following table lists the special character sequences that are available in the Edit Procedure Comment Text form:

Characters	Meaning
#D#	Inserts the current date.
#P#	Inserts the name of the current procedure.
#R#	Insert the data type of the parameter return value.
#T#	Inserts the current time.
#U#	Inserts the name of the current user. The name of the current user comes from the VBA built-in CurrentUser() function which is based on Microsoft Access security. If you do not have user-level security implemented in your database, this function always returns the value "admin". You may want to substitute your initials rather than using #U#.
The form above is similar to the to the Edit Procedure Comments form in Code Cleanup. The only difference is that in Code Cleanup, the procedure parameters can be automatically listed. See the Procedure Comments section for more information.

You can use the #D# token to insert the current date. Control the date format by using the Date Format combo box. The available date formats use the Access Format function and respect whatever your date settings are as specified in the Windows Control Panel.

The default settings are what we use at FMS to manage our Access development efforts. For each procedure, there is a brief comment of its purpose, a list of parameters and description, the return value, and when and who created it. Additionally, we keep a log of every revision with the date, person, and modification made. This is an example:

Function GetID (strFirst As String, strLast As String) As Integer
  ' Comments  : Get customer ID based on the customer name
  ' Parameters: strFirst - First name
  '             strLast  - Last name
  ' Returns   : Customer ID
  ' Created   : 05/01/96 LC
  ' Modified  :
  ' 06/03/96 DH  Added support for duplicate names
  ' 05/07/96 LC  Used secondary key
  '

Press the [OK] button to save your settings, or [Cancel] to discard your changes.


Insert Error Handling Code

You can specify the error handling code to be added in every new procedure. This is included by checking this check box, and customized by pressing the [Edit] button:

Edit Error Handler Text Form

The default error handler provides a simple way to provide the user with a message when an error occurs. The error handling default settings shown here are automatically added to every new procedure:

Function NewProc()
  On Error Goto PROC_ERR

PROC_ERR:
  MsgBox "The following error occurred: " & Error$
  Resume Next
End Function

The following table lists the special character sequences that are available in the Edit Error Handler Text form:

Characters	Meaning
#P#	Inserts the name of the current procedure.
#X#	Inserts the word "Sub" or "Function" as appropriate.
#M#	Inserts the name of the module. If the module is a form or report, it is prefixed with the object type (e.g. "Form.FormName", where FormName is the name of the object.)
These settings are identical to those used in the Code Cleanup tool that is available from the Code Cleanup Options form.


Use Procedure Naming Conventions

For functions, you can add a prefix or suffix to the function name to indicate the variable type it returns. This makes it easy to identify the value returned by functions from their names. If you are using a naming convention for variables, you can easily verify that the variable datatypes and the function return types match.

To enable this option, check the check box. To edit the identifiers used, press the [Edit] button:

Procedure Naming Conventions Form

Use this form to enter the characters you want to use to identify each variable type that a function can return. If you want the characters placed before the procedure name, select the Prefix option. To place the characters after the procedure name, select the Suffix option. The values you type in are checked to ensure that they conform to VBA procedure name rules. For example, if you specify a naming convention of _int and use the Prefix option, Total Access Code Tools 8 does not accept the entry since a procedure name cannot start with an underscore.


Builders - Procedure Builder - Using the Generated Code
Using the Generated Code

Once the Procedure Builder generates the code you need, use the [Copy to Clipboard] or [Paste to Module] buttons to process the results. For more information, see the Placing Results in Your Modules section.


Property Procedure Builder

Access 97 introduced the concept of class modules. Access 95's form and report modules were also considered class modules, but they lacked the ability to create true objects that didn't rely on an Access form. With class modules, you can create Property Statements that expose your class properties. Creating a property requires anywhere from one to three separate VBA procedures. The Total Access Code Tools 8 Property Procedure Builder makes it easy to create property statements with a variety of options.

The following items are configurable in the Property Procedure Builder:

•	Property name

•	Property data type

•	Option to create the module level variable to support the property

•	Options to select the Let/Set/Get procedures

•	Comment structure

•	Error handling code

The following is an example of a procedure created by the Property Procedure Builder. This code was created by typing the procedure name "TableName"—the builder took care of the rest:

Dim m_InputTable As String

Public Property Get InputTable() As String
  ' Comments   :
  ' Parameters :
  ' Created    : 9/26/97 10:21:42 AM Admin
  ' Modified   :
  '
  ' --------------------------------------------------------
  On Error Goto PROC_ERR:

  InputTable = m_InputTable
  Exit Property

PROC_ERR:
  MsgBox "The following error occured: " & Error$
  Resume Next

End Property

Public Property Let InputTable(NewValue As String)
  ' Comments   :
  ' Parameters :
  ' Created    : 9/26/97 10:21:42 AM Admin
  ' Modified   :
  '
  ' --------------------------------------------------------

  On Error Goto PROC_ERR:

  m_InputTable = NewValue

  Exit Property

PROC_ERR:
  MsgBox "The following error occured: " & Error$
  Resume Next

End Property

Public Property Set InputTable(Obj As String)
  ' Comments   :
  ' Parameters :
  ' Created    : 9/26/97 10:21:42 AM Admin
  ' Modified   :
  '
  ' --------------------------------------------------------
  On Error Goto PROC_ERR:

  Exit Property

PROC_ERR:
  MsgBox "The following error occured: " & Error$
  Resume Next

End Property

You can see from the above code that the property procedure statements for the Let, Get and Set operations are automatically generated, along with the declaration of the module-level variable to maintain the state of the property value.

More:

Invoking the Property Procedure Builder

Options

Using the Generated Code


Invoking the Property Procedure Builder

To invoke the Property Procedure Builder, follow these steps:

1.	Put your cursor anywhere in the module window where you want the new code to be added (this step is not necessary if you are sending the results to the clipboard).

2.	From the "Builders" tab of the Total Access Code Tools 8 toolbar, press the [New Prop] button.

The Property Procedure Builder form appears:

Property Procedure Builder Form

The first step is to enter the name of your new procedure. After you move from the Property Name field you will notice that the form updates the Generated Code text box. As you select and change options, the Generated Code text box automatically reflects your settings. These options are available:

Name

Type in the name of the new property. Remember that procedure naming conventions are typically not used in property procedure names.

Data Type

Select the data type of the property you are creating. If you select Variant, Object or Other, the option for creating the Property Set statement is enabled. This is because a Property Set procedure is only valid for variable types you can use the Set operation on. If you select Other, the text box to the right of the Data Type combo box is enabled. Type in the specific name of the other data type you want to use. For example, the data type for a DAO recordset is not in the list of Data Types. To specify a recordset, set the Data Type combo box to "Other" and type the word "recordset" in the text box.

Selecting Which Procedures to Build

You can specify any combination of the Let/Set/Get property procedures to building by clicking the appropriate checkbox.

Create Class Property Variable

The convention for using property procedures in a class module is to create a module-level variable that always contains the value of the property. Check this option to have the Procedure Property Builder create the line of code to declare a module-level variable to support the property.

When you select this option, the appropriate code is placed into each property procedure to read or set the value of the module-level variable. If you turn this option off, the code is not inserted.


Builders - Property Procedure Builder - Options
Options

Press the [Options] button to open the Property Procedure Builder Options form. This form contains options that are modified less frequently.

Property Builder Options Form

These options are the same as for the Procedure Builder, except that there are no options for naming conventions. This is because you typically don't use naming conventions on the name of a property procedure. See the Procedure Builder Options section for more information on these options.

Procedure Builder Options


Builders - Property Procedure Builder - Using the Generated Code
Using the Generated Code

Once the Property Procedure Builder generates the code you need, use the [Copy To Clipboard] or [Paste to Module] buttons to process the results. If you select the [Paste to Module] button, and selected the Create Class Property Variable option, the property variable is placed in the declarations section of the class module.

Note that Total Access Code Tools 8 has no way of knowing if you invoked the Property Procedure Builder from a standard module or a class module. Because of this, be sure that you are coming from a class module before using the [Paste to Module] button. If you paste property procedure code into a non-class module, the code will not compile.


SQL Builder

Depending on your programming style, you may embed queries in your code or programmatically change SQL strings. The SQL Builder allows you to select an existing query in your database and easily convert its SQL string into one or more lines of VBA code.

You can, of course, just assign the SQL string to a variable in one line, but it is much easier to maintain and understand when broken into several lines and is completely visible on the screen. Additionally, some SQL strings contain double quotes that cause problems when you just surround the entire string with double quotes. There is an option that automatically converts double quotes to single quotes.

For example, assume your database contains a query with the following SQL string:

SELECT DISTINCTROW Categories.[Category Name], Products.[Product Name], Categories.Description, Categories.Picture, Products.[Product ID], Products.[Quantity Per Unit], Products.[Unit Price], Products.Discontinued FROM Categories INNER JOIN Products ON Categories.[Category ID] = Products.[Category ID] WHERE ((Categories="BOOKS")) ORDER BY Categories.[Category Name], Products.[Product Name];

The SQL Builder turns this to the following VBA code:

strSQL = "SELECT DISTINCTROW Categories.[Category "
strSQL = strSQL & "Name], Products.[Product Name], "
strSQL = strSQL & "Categories.Description, Categories."
strSQL = strSQL & "Picture, Products.[Product ID], Products."
strSQL = strSQL & "[Quantity Per Unit], Products.[Unit "
strSQL = strSQL & "Price], Products.Discontinued  FROM "
strSQL = strSQL & "Categories INNER JOIN Products ON "
strSQL = strSQL & "Categories.[Category ID] = Products."
strSQL = strSQL & "[Category ID]  WHERE ((Categories="
strSQL = strSQL & "'BOOKS'))  ORDER BY Categories.[Category "
strSQL = strSQL & "Name], Products.[Product Name];"

If you use the Line Continuation option, the above code looks like this:

strSQL = "SELECT DISTINCTROW Categories.[Category " & _
         "Name], Products.[Product Name], " & _
         "Categories.Description, Categories." & _
         "Picture, Products.[Product ID], Products." & _
         "[Quantity Per Unit], Products.[Unit " & _
         "Price], Products.Discontinued  FROM " & _
         "Categories INNER JOIN Products ON " & _
         "Categories.[Category ID] = Products." & _
         "[Category ID]  WHERE ((Categories=" & _
         "'BOOKS'))  ORDER BY Categories.[Category " & _
         "Name], Products.[Product Name];"

More:

Invoking the SQL Builder

Options

Using the Generated Code


Invoking the SQL Builder

From the Builders tab of the Total Access Code Tools 8 toolbar, press the SQL Builder button. The SQL Builder form appears:

SQL Builder Form

The first step is to select a query in the current database with the Queries in Current Database combo box. Next, specify the name of the variable to hold the SQL string. If the default strSQL is acceptable, you do not need to change this field. If you want a different variable type the name into the Variable Name text box. When you update this field, the SQL Builder automatically displays the code in the Generated Code text box for your review.

If you want to use line continuation characters to make the code more readable, select the Use Line Continuation option.


Builders - SQL Builder - Options
Options

You can set several options that control how the SQL Builder generates code. Press the [Options] button to do this:

SQL Builder Options Form

Convert Embedded Double-Quotes to Single Quotes

Since the SQL Builder is generating code to assign a variable, it needs to use double quotes ( " ) to delimit the string. A query containing double quotes can cause problems. To avoid this, select this option to replace all double quotes in the SQL string with single quotes.

There are situations where this conversion is not desirable. For example, your query's WHERE clause may contain a string that contains a double quote. In this case, do not enable this option, and all your double quotes are converted to doubled double quotes to delimit them properly.

Indent Lines

Select this option if you want the generated code to be indented one tab width as specified by the Tab Width setting. Of course, after inserting the code in your module, you can use the Access indentation features to highlight a block of text and [Tab] or [Shift][Tab] to indent the code to the right or left.

Tab Width

This value controls how many spaces are used to indent the generated code. This value is only used if the Indent Lines option is selected.

Wrap Lines Longer Than __ Characters

This value controls the maximum length of lines in the generated code. Type in a number that defines the maximum line length you want.


Builders - SQL Builder - Using the Generated Code
Using the Generated Code

Once the SQL Builder generates the code you need, press the [Copy To Clipboard] button to copy the generated code to the Windows clipboard. Then position the cursor in the procedure to contain the code and paste it in.


Long Text Builder

There are many situations when you need to assign long text strings to variables. This usually occurs when you are coding error messages or prompts. However, splitting the variable assignment into multiple lines can be tedious.

The Long Text Builder allows you to enter a string and have it automatically word wrapped and converted to code that stores it in a variable. For example, the following text could be typed or pasted into the Long Text Builder:

As you change data in a database, the database file becomes
 fragmented and uses more disk space than necessary.
 Periodically, you can compact your database to defragment
the database file:  The compacted database is usually smaller.
 You can also choose to change the collating order, the
encryption, or the version of the data format while you
copy and compact the database.

The result is the following code:

strText = "As you change data in a database, the "
strText = strText & "database file becomes fragmented and "
strText = strText & "uses more disk space than necessary. "
strText = strText & "Periodically, you can compact your "
strText = strText & "database to defragment the database "
strText = strText & "file:  The compacted database is "
strText = strText & "usually smaller.  You can also choose "
strText = strText & "to change the collating order, the "
strText = strText & "encryption, or the version of the data "
strText = strText & "format while you copy and compact the "
strText = strText & "the database."

If you use the Line Continuation option, the same text results in this code:

strText = "As you change data in a database, the " & _
          "database file becomes fragmented and " & _
          "uses more disk space than necessary. "  & _
          "Periodically, you can compact your " & _
          "database to defragment the database " & _
          "file:  The compacted database is " & _
          "usually smaller.  You can also choose " & _
          "to change the collating order, the " & _
          "encryption, or the version of the data " & _
          "format while you copy and compact the " & _
          "the database."

More:

Invoking the Long Text Builder

Options

Using the Generated Code


Invoking the Long Text Builder

From the Builders tab of the Total Access Code Tools 8 toolbar, press the Long Text button. The Long Text Builder form appears:

Long Text Builder Form

Enter the name of the variable you want to assign in the Variable Name text box. Type your string into the Text box. When you move off the field, the builder generates the code and displays it in the Generated Code section.

If you want to use a line continuation character at the end of each line, select the Use Line Continuation option.


Builders - Long Text Builder - Options
Options

Press the [Options] button to display the Long Text Builder Options form.

Long Text Builder Options form

This form shows the available options for this builder and is identical to the options for the SQL Builder.


Builders - Long Text Builder - Using the Generated Code
Using the Generated Code

Once the Long Text Builder generates the code you need, press the [Copy to Clipboard] to copy the generated code to the Windows clipboard. Then, move to the module where you want to paste the text and use the appropriate Windows Paste command.


Message Box Builder

Message boxes are simple user-interface elements that probably occur in several places in your application. Unfortunately, the syntax for coding one is difficult to remember. You could dig through the help file and add up constant values, or you can use the Message Box Builder.

The Message Box builder provides a visual tool for designing message boxes. As you use the mouse and keyboard to "draw" your message box, the builder lets you see the results and generates the corresponding VBA code.

More:

Invoking the Message Box Builder

Setting Options

Previewing the Message Box

Using the Generated Code


Invoking the Message Box Builder

From the Builders tab of the Total Access Code Tools 8 toolbar, press the MsgBox button. The Message Box Builder form appears:

Message Box Builder Form


Builders - Message Box Builder - Setting Options
Setting Options

To define a message box, set the options on this form. As you change the options, their effects are displayed on the message box.

Buttons

Use this combo box to select the buttons to use in the message box. Available choices are:

•	OK

•	OK/Cancel

•	Abort/Retry/Ignore

•	Yes/No/Cancel

•	Yes/No

•	Retry/Cancel

Default Button

Use this option group to select which of the buttons is the default button. The numbers correspond to buttons 1 through 3 from left to right.

Icon

Select the icon to display. Available choices are:

•	None

•	Critical Stop

•	Question

•	Exclamation

•	Information

Variable Name

Enter the variable name for the message box function to set. This option is only available if your message box is a function rather than a statement. This is the case if you select a button combination other than [OK].

Message Box Title

Click the mouse in the title area of the message box that appears within the Message Box builder. Type in the title for your message box.

Message Box Text

Click the mouse in the message area of the message box that appears within the Message Box builder. Type in the text for your message box.

Use VB Message Box Constants

Unlike Access 2.0, Access 97 has built-in constants for use with the message box command. Check the "Use VB Message Box Constants" option if you want to use these constants instead of hard-coded numbers.

By using constants rather than numbers, the generated code is easier to understand. If you select this option, the following built-in constants are used:

Constant Value	Constant Name
0	VbOKOnly
0	VbDefaultButton1
1	vbOKCancel
2	VbAbortRetryIgnore
3	VbYesNoCancel
4	VbYesNo
5	VbRetryCancel
16	VbCritical
32	VbQuestion
48	VbExclamation
64	VbInformation
256	VbDefaultButton2
512	VbDefaultButton3
4096	VbSystemModal
Include Case Block:

Select this option to generate a Select Case block after the message box to handle the button pressed. This feature makes it easy to add your code in response to the user's selection.

System Modal

Select this option to make the generated code include an option for making the message box "system modal". Such a message box disallows interaction with other programs or Access itself until the message box is closed.


Previewing the Message Box

Once you have selected your options, press the [Preview] button to preview the actual message box that will be created by the generated code.


Builders - Message Box Builder - Using the Generated Code
Using the Generated Code

Once the Message Box Builder generates the code you need, press the [Copy to Clipboard] button to copy the generated code to the clipboard.


Select Case Builder

Building Select Case statements with more than a few values can be a hassle. Especially when you consider how many times you do it in an application with a lot of code. The Select Case Builder allows you to specify values and a variable name, and then quickly generate the VBA code for the Select Case statement.

More:

Invoking the Select Case Builder

Options

Using the Generated Code


Invoking the Select Case Builder

From the Builders tab of the Total Access Code Tools 8 toolbar, press the Select Case button. The Select Case Builder form appears:

Select Case Builder Form

Enter the expression or variable name you want to evaluate in the Text to Evaluate text box. Then type the case values into the boxes labeled Value 1 through Value 12.

You can decide whether you want a Case Else statement with the Add Case Else check box, and whether you want to add quotes around the values specified in the Value fields with the Add Quotes to Values check box.

You can also add blank lines between each Case statement with the Add Lines Between Case option, and add colons to the end of each case statement with the Add Colons option.

The builder generates the code with the specified options and displays it in the Generated Code text box.

For example, if you type strTable into the Text to Evaluate, Customers into Value1, Orders into Value2, and select the four check boxes, the following code is generated:

Select Case strTable
  Case "Customers":

  Case "Orders":

  Case Else:

End Select


Builders - Select Case Builder - Options
Options

Press the [Options] button to display the Select Case Builder Options form:

Select Case Builder Options Form

Indent Lines

Select this option if you want the generated code to be indented by the number of spaces defined by the Tab Width setting. Of course, after adding the code to your module, you can manually indent it further by highlighting the area and pressing [Tab] to move it to the right, or [Shift][Tab] to move it to the left.

Tab Width

This value controls how many spaces are used to indent the generated code. This value is only used if the Indent Lines option is selected and is the same value as Tab Width settings in the other areas of Total Access Code Tools 8.


Builders - Select Case Builder - Using the Generated Code
Using the Generated Code

Once the Select Case Builder generates the code you need, press the [Copy to Clipboard] button to copy the generated code to the Windows clipboard.


Overview

Full Toolbar, Database Tab

Imagine the scenario where you are deep in the process of writing code. You have several modules open, a few forms in design view, and the database window is obscured by all the open objects. As you are writing code, you realize you need the name of an object. So you hunt around for the database window, click on tabs and scroll through to find the object. You then open the object in design view to find the object's definition. A lot of steps for a seemingly simple task.

The Database Tools feature of Total Access Code Tools 8 simplifies these operations by always keeping the toolbar above your workspace and giving you an easy way to select objects. These tools are provided:

•	Open - opens the selected object

•	Design - opens the selected object in design view

•	Copy - makes a copy of the selected object with an automatic default for the copy's name

The Object list is filled when you start Total Access Code Tools 8. If you add or delete objects, press the [Refresh] button to update the Object list.

More:

Open

Design

Copy


Open

Press the open button to open the selected object. Tables, Queries, Forms, and Reports are opened in browse mode. For reports, this means Preview mode.

Notice that when you set the Object Type to "Macro", the [Open] button changes to [Run]. Similarly, when you set the Object Type to "Module", the [Run] button is disabled. This behavior follows the Access database window behavior.


Design

Press the [Design] button to open the currently selected object in design view.


Copy

The [Copy] button invokes the Total Access Code Tools 8 Object Copy dialog. This dialog simplifies copying objects in your database by providing a reasonable default name for the new object—just like Access 2.0 used to do. Of course, Access makes it easy to copy objects also, but it doesn't provide a default name for the new object.

From the Database tab of the Total Access Code Tools 8 toolbar, press the [Copy] button. This form appears:

Object Copy Dialog

As you can see, a default value is supplied for the new object's name. This value is derived by adding numbers to the original object's name until a unique name is generated.

If the object you are copying is a table, the Include option group allows you to copy only the structure or the structure and data. If you are copying a linked table, the new table is also created as a linked table.


Introduction to Code Cleanup

Full Toolbar, Cleanup Tab

As a software developer, chances are you have old code or occasionally inherit code from someone else that does not meet your coding standards. Given tight deadlines and time constraints, even your own code may have inconsistent indentation, naming conventions, and comment structures. The Code Cleanup tool addresses these problems by applying consistent formatting and other features to all your modules. Take your existing modules and apply these transformations:

Add Option Explicit

Insert "Option Explicit" into the declarations section of every module that lacks it. One of the cardinal rules of writing reliable and maintainable code is to explicitly declare all of your variables. By adding "Option Explicit" to your module, you are telling Access to require variable declaration.

Insert Error Handling

Error handling is one of the key attributes of robust applications. This feature inserts error handling code into your procedures and allows several user-defined formatting options.

Apply Standardized Code Indentations

Automatically indent code according to the tab stop width you specify. Every loop and branching structure (If..End If, For..Next, Do..Loop, Select Case, etc.) is indented. This makes your code more maintainable and easier to read.

Add Module Comments

Add a standard module header to the top of each module such as a project name, creation dates, copyright notices, and ownership rights. A complete list of all procedure names and procedure definitions can also be added.

Add Procedure Comments

Add comment headers to each procedure. Although the program cannot determine what the code is actually doing and write the comments for you, it can take much of the drudgery out of the process of creating consistent comment headers for each procedure.

Apply Variable Naming Conventions

Rename your existing variables to conform to naming conventions based on the variable type (Integer, String, etc.). Every variable is given a prefix or suffix identifying its type with additional options for global and module level variables.

Sort Module Procedures

In Access 2.0, when you entered a procedure into a module, it was automatically sorted alphabetically by procedure name. In Access 97, this feature is gone allowing you to insert procedures in any order. This may be good, but it can also create a mess. Code Cleanup makes it easy to sort the procedures in your modules including property statements in class modules.

Adding Line Numbers

Code Cleanup can add line numbers to your code to support the VBA Erl() function. This function returns the number of the source code line where a runtime error occurred. This number can help in debugging the program code.

Other Options

You can also use Code Cleanup to:

•	Convert multiple Dim statements on one line to multiple lines for readability and robustness.

•	Specify the number of blank lines between procedures in your module code.


Running Code Cleanup

Now that you are familiar with the general concepts of the Code Cleanup tool, follow the steps in this section to clean up your database.

Make a Backup

This is the single most important step when running Code Cleanup. If you do not make a backup, and Total Access Code Tools 8 finds a problem with your database that it cannot resolve, your objects may be left in an inconsistent state. This can be a disaster if you do not have a backup to restore.

Indeed, you should have regular backups of your database regardless of whether you use Total Access Code Tools 8 or not. Remember that a database is not like word processing document or a spreadsheet. It typically represents a far greater amount of development effort, and typically holds more important information.

Note: Total Access Agent, also from FMS, can help you with the process of creating and maintaining regular backup sets of Access databases. For more information, call us or visit our website at http://www.fmsinc.com.

Important: You must create a backup of your database before you run Code Cleanup. FMS cannot provide technical support if you do not have a backup of your database.

The key point is to have a valid backup of your database as it exists before you run Code Cleanup. After Code Cleanup modifies your database, it cannot convert it back to its previous state. The only way to do this is to restore your backup.

Open Your Database in Exclusive Mode

Start Microsoft Access and open the database that you want to modify. For Code Cleanup, Total Access Code Tools 8 must have exclusive access to your database. Opening the database in Exclusive mode accomplishes two things:

1.	Ensures that when you open the database, no other users are using it. Microsoft Access does not let you open a database in Exclusive mode if other users have it open. If you cannot open the database in exclusive mode because other users have it open, get the other users to close the database and try again.

2.	Ensures that other users do not open the database while you are running Total Access Code Tools 8 on it.

Follow these steps to open the database in exclusive mode:

1.	Start Microsoft Access.

2.	Select File|Open from the menu.

3.	Select the database from the Open Database dialog.

4.	Make sure the Exclusive check box is checked.

5.	Press the [Open] button.

Compile All Modules

To modify your modules, Total Access Code Tools 8 parses your code. It steps through each line of code and breaks it into its constituent parts: reserved words, variables, expressions, comments, etc. If your code has syntax problems and does not compile in Microsoft Access, Total Access Code Tools 8 may fail to parse your code properly, and may stop running or create erroneous modified code.

To ensure this does not happen, compile all your modules. To do this, open any module and select Debug | Compile and Save All Modules from the menu. Fix any syntax problems that are shown.

Once your database is free of syntax errors and compilation problems, it is ready for Code Cleanup. If you made any changes to your code to correct syntax errors, you should make another backup of your database at this point to ensure your changes are safe.

Start the Code Cleanup Tool

Once the preparatory steps are completed, you are ready to run Code Cleanup. From the Total Access CodeTools Toolbar, press the [Code Cleanup] button.

SourceSafe Integration Issues

Code Cleanup needs the right to change all modules in your database. This includes form, report, class and standard modules. If your database is under SourceSafe control (using the SourceSafe integration tool that ships with the Microsoft Office Developers Edition), and you do not have all modules checked out, Code Cleanup cannot run.

If your database is under SourceSafe control, and you start Code Cleanup, a dialog appears telling you that you must have all form, report, class and standard modules checked out for Code Cleanup to continue. When this dialog appears, you have two options. Press [Yes] to continue. At this point, Code Cleanup checks to ensure that you have every module checked out. If one or more modules is not checked out, a dialog appears, letting you know the problem exists.

At this point, Code Cleanup cancels any further operations. To continue, use the SourceSafe tools to check out all forms, reports, class and standard modules and re-start Code Cleanup.

Backup Required!

Next, a form appears warning you that you need to have a current backup of your database before proceeding. If you have a backup, press the [OK] button. If not, press the [Cancel] button, close your database and make a backup. Remember, the Code Cleanup utility cannot recreate your original database after making changes to it.


Code Cleanup - Setting Options
Setting Options

Once you have made a backup and continue with Code Cleanup, the Code Cleanup Options Form appears. Use this form to tell the program how you want your code modified. The following sections describe the settings in detail.

More:

Option Explicit

Error Handling

Code Indentation

Variable Naming Conventions

Module Comments

Procedure Comments

Module Sorting

Adding Line Numbers

Other Options


Option Explicit

This feature inserts "Option Explicit" into the declarations section of every module that lacks it. By putting this statement in your module, you are telling Access to require every variable in that module to be explicitly declared using statements such as Dim, Const, Public, etc. See the Writing Better Code section  for information on why using "Option Explicit" is important.

Note: Access 97 includes a new option under the Tools | Options, Module dialog. This option, called "Require Variable Declaration", causes Access to automatically insert "Option Explicit" in every new module you create. This option should be on.

This option is very important if you need to clean code without "Option Explicit". You should note that, although this is good to have in your code, inserting it into modules often causes the module to generate compilation errors. By inserting "Option Explicit" into a module with undeclared variables, you will need to manually fix the problem variables.

Usage

To enable this option, check the Insert Option Explicit in modules that lack it check box.

If you think you have modules that lack "Option Explicit", you should select only this option, perform the cleanup process, try to compile your database, and fix any compilation problems. Then, you can consider using Code Cleanup for other tasks such as applying variable naming conventions.

Issues

Option Explicit is inserted in all modules that lack it on the line after the module's last Option command.


Error Handling

Robust applications require error handling. Without error handling, your Access application can leave the user looking at cryptic error messages and leave data in an inconsistent state. VBA provides a variety of commands and functions that allow you to implement error handling in your code. It does this with the On Error Goto ... command along with standard error handling code which you can define. For more information on the importance of error handling, see the Writing Better Code section .

For example, the following procedure tries to delete a file:

Function DeleteFile()
  Kill "C:\Myfile.txt"
End Function

If the file does not exist, a runtime error occurs and presents your application's user with an Access error message dialog. After running the Code Cleanup tool, the same function looks like this:

Function DeleteFile()
  On Error Goto PROC_ERR
  Kill "C:\Myfile.txt"
  Exit Function

PROC_ERR:
  MsgBox "The following error occurred: " & Error$
  Resume Next
End Function

The error handling code that Code Cleanup inserts is completely customizable and can also use the name of the current procedure to create a unique error handling label name.

Note: Access 2.0 requires that each label in a module have a unique name. This restriction forces you to make each procedure's label names unique, usually by including the procedure name in the label name. For example, to create an error label for a procedure named OpenTable, you would type OpenTable_ERR. With Access 97, this restriction no longer exists: you can use any label name you want. For this reason. Code Cleanup uses the defaults of PROC_ERR and PROC_EXIT for label names.

Usage

To enable the insertion of error handling code, check the Insert Error Handling Code check box.

Issues

Error handling is only inserted in procedures that do not already have error handling. If a procedure has "On Error" anywhere within it, error handling is not added since it may conflict with the procedure's existing code. Procedures that have no code or only comments are also skipped.

Editing the Error Handling Code

To edit the error handling code that the Code Cleanup tool inserts, press the [Edit] button. The Edit Error Handler Text form appears which is identical to the form used for the Procedure Builder.

Edit Error Handler Text Form

Use this form to edit the text that you want the Code Cleanup tool to insert as error handling code.

There are several character sequences with special meaning to Code Cleanup. By entering these character sequences into either the Enable Error Handler or the Error Handler text boxes, Code Cleanup replaces those characters with other values when the code is actually written into your procedures. The following table lists the special character sequences that are available in the Edit Error Handler Text form:

Characters	Meaning
#P#	Inserts the name of the current procedure.
#X#	Inserts the word "Sub", "Function" or "Property" as appropriate. Use this character sequence in conjunction with the VBA Exit keyword to exit the procedure.
#M#	Inserts the name of the module. If the module is a form or report, it is prefixed with the object type (e.g. "Form.FormName", where FormName is the name of the object.)
The Enable Error Handler text box holds the text that is placed at the beginning of your procedure. This is typically the statement or statements that enable error handling in the procedure and consists of an On Error Goto... statement. The default for this value is:

On Error Goto PROC_ERR:

The Error Handler text box holds the text of the section of code that implements the error handler. This is typically composed of a label that identifies the start of the section, and code that is executed when the error occurs. The default for this value is:

  Exit #X#

PROC_ERR:
  Msgbox "The following error occurred: " & Error$
  Resume Next

Notice the Exit statement in the text. This is inserted into the procedure so the code does not "fall-through" to the error handler if a runtime error doesn't occur.


Code Indentation

Standardized indenting is probably the single most important factor in making module code readable and maintainable. Consider the following function:

Function TestVars (A As Integer, B As Integer) As Double
Dim dblReturn As Double
Dim x As Integer
dblReturn = 10
If A <= 20 Then
For x = 1 to 20
dblReturn = dblReturn + x ^ B
Next x
Else
x = A
Do
x = x + 1
dblReturn = dblReturn + x * B
Loop Until x > A
End If
TestVars = dblReturn
End Function

Now compare this code to the following version with standardized indenting:

Function TestVars (A As Integer, B As Integer) As Double
  Dim dblReturn As Double
  Dim x As Integer
  dblReturn = 10
  If A <= 20 Then
    For x = 1 to 20
      dblReturn = dblReturn + x ^ B
    Next x
  Else
    x = A
    Do
      x = x + 1
      dblReturn = dblReturn + x * B
    Loop Until x > A
  End If
  TestVars = dblReturn
End Function

Which function would you rather decipher? Obviously, indentation plays an important role in code readability. Using the Code Cleanup tool, you can apply indenting to each line of your module code, defining the number of spaces for each indentation.

Usage

To enable this option, check the Indent Code check box. To set the number of characters used for each Tab Stop, type the desired number into the Tab Stop Width text box. Note that this value is global to all Total Access Code Tools 8 tools.

When you select the Indent Code option, Code Cleanup also changes lines that have multiple statements separated by colons. For example, the following code:

Select Case strTable
  Case "Customers": strIndex = "LastName"
  Case "Orders": strIndex = "OrderID": fBigTable = True
  Case "Employees": strIndex = "EmpID"
End Select

becomes:

Select Case strTable
  Case "Customers"
    strIndex = "LastName"
  Case "Orders"
    strIndex = "OrderID"
    fBigTable = True
  Case "Employees"
    strIndex = "EmpID"
End Select


Variable Naming Conventions

Variable naming conventions make your code easier to read and maintain by adding information to variable names. Naming conventions work by adding a character or string of characters to the beginning or end of variable names. For example:

Dim MyName As String

becomes with the prefix option:

Dim strMyName As String

or with the suffix option:

Dim MyNameStr As String

Adding a naming convention not only makes it easier to identify variables, it also has the benefit of minimizing the chance your variables will conflict with reserved words of future versions of Access and VBA.

The Naming Conventions option of Code Cleanup allows you to add naming conventions to all of your variables. By default, Total Access Code Tools 8 uses the naming conventions we use at FMS and generally accepted in the Access/VBA community, but you can customize these to your own convention.

To see the application of naming conventions in action, consider the following function:

Function CountFields (Customer As String) As Integer
  Dim D As Database
  Dim Cust As Tabledef

  Set D = CurrentDB()
  Set Cust = D.Tabledefs(Customer)

  CountFields = Cust.Fields.Count
End Function

Now consider the same code after naming conventions are applied:

Function CountFields (strCustomer As String) As Integer
  Dim dbsD as Database
  Dim tdfCust as Tabledef

  Set dbsD = CurrentDB()
  Set tdfCust = dbsD.Tabledefs(strCustomer)

  CountFields = tdfCust.Fields.Count
End Function

With naming conventions, you can easily identify your variables by the type of value they hold. For instance, the  variables Customer and Cust become more understandable when defined as strCustomer and tdfCust.

Usage

To enable this option, check the Apply Naming Conventions check box. There is a Prefix/Suffix option to determine whether the variable tags are put before or after the variable name.

Editing Naming Conventions

The values for the naming conventions can be viewed and changed by pressing the [Edit] button:

Edit Naming Conventions Form

Identifiers are defined for each variable type. For example, if you want all integer variables to be tagged with the text int, type int into the Integer text box.

Public/Global Variables

Specify the character(s) to use on each variable that is declared as Public or Global.

Module Level Variables

If you want naming conventions applied to module-level variables (those defined in the declarations section of the module, but not declared as Public), type the value into the Module-Level Variables text box.

Public/Suffix Option Group

Use the Prefix/Suffix option group to specify where the Public/Global and Module tags are to be placed. This can be different from the variable tag's location which is specified on the main Code Cleanup form.

Type Members

Click the [Don't Apply Naming Conventions To Type Members] checkbox to tell Code Cleanup to not apply naming conventions to the names of elements within a Type statement. If this option is turned off, naming conventions are added to element names according to their data type.

Restoring Values

To clear all values, press the [Clear All Entries] button. To restore the values to their original default values, press the [Restore From Defaults] button. To cancel all changes and return to the previous form, press the [Cancel] button.

Issues

If you leave a value blank, Code Cleanup does not apply naming conventions to that variable type. For example, if you leave the Integer field blank, no changes will be made to the names of Integer variables.

All variables throughout your modules are analyzed for conversion to the specified naming convention. If a variable is already named with the appropriate naming convention, it is not modified (for instance, variable strTemp is not renamed strStrTemp). If renaming a variable causes a conflict with global variables, the conversion is not applied. For instance, if a global variable intX exists, an integer variable X is not renamed to intX. Variables that are not renamed are listed in the Messages report. For information on the Messages Report, see the Finishing Up – View Messages Section.

Note that variable renaming does not change the name of public variables in class modules. This is because public variables in class modules are essentially the same as class property and method names--they should have naming conventions.


Module Comments

Having comment text in the declarations section of your module is useful for identifying the contents of, and revisions to, the module. Total Access Code Tools 8 can apply a standard comment block to the beginning of each of your modules. Although the program cannot infer or deduce what the code actually does, it can take a lot of the work out of the process of inserting comment blocks into each module.

Additionally, Total Access Code Tools 8 can write a list of the procedures contained in the module or the complete procedure definitions. This feature allows you to include an inventory of procedure names right in the module comments. See the Writing Better Code section  for more information on the practice of adding comments.

Usage

To enable this option, check the Insert Module Comments check box. To add comments to all modules regardless of existing comments, check the Always Add check box. If the Always Add option is not selected, and Code Cleanup finds existing module comments (the first comment lines match), this comment block is not added for that module.

Editing Module Comment Text

To change the text for module comments, press the [Edit] button. After a moment, the Edit Module Comments Text form appears:

Edit Module Comment Text Form

You can modify this text to suit your particular documentation and coding styles.

Like the Error Handling Insertion feature, there are several character sequences that have special meaning to the Code Cleanup tool. By typing these character sequences into the Module Comment text box, you tell the Code Cleanup tool to replace those characters with other values when the code is actually written into your procedures. These special character sequences are available:

Characters	Meaning
#D#	Inserts the current date.
#T#	Inserts the current time.
#M#	Inserts the name of the module.
#U#	Inserts the name of the current user. The name of the current user comes from the VBA built-in CurrentUser() function which is based on Microsoft Access security. If you do not have user-level security implemented in your database, this function always returns the value "admin". You may want to substitute your initials rather than using #U#.
#L#	Inserts a list of procedure names from the current module.
#V#	Inserts a list of procedure definitions from the current module.
#C#	Inserts the module's creation date. This date is determined by reading the module object's DateCreated property. Note that this may not be the actual date the module was first created, because the module may have been created at an earlier date and then imported into the database. In this case, the module's creation date would be the date it was imported.
Date Formats

You can control the format of dates inserted by Total Access Code Tools 8 (#D# and #C# tokens) by choosing a format with the Date Format option.

The available formats are:

•	General Date

•	Long Date

•	Medium Date

•	Short Date

The "Short Date" and "Long Date" formats use the global Windows settings as specified by the Control Panel, Regional Settings dialog. Use one of these two formats if you want Total Access Code Tools 8 to use your global Windows settings.

Sample Results

These examples show you the results of using Total Access Code Tools 8 default module commenting features:

' Module     : Utility Functions
' Description:
' Procedures : ConvertNulls
'              CountFields
'              GetUserName
'              IsTableAttached
'
' Created    :  12/31/96 Frank
' Modified   :
' 04/01/97-Frank Cleaned with CodeTools
' ----------------------------------------------------------

If you used the procedure definition (#V#) character sequence instead of the procedure name (#L#) sequence, the comment block would look like this:

' Module     : Utility Functions
' Description:
' Procedures : ConvertNulls(varIn As Variant) As Variant
'              CountFields(strTable As String) As Integer
'              GetUserName(strGroup As String) As String
'              IsTableAttached(strTable) As Integer
'
' Created    :  12/31/96 Frank
' Modified   :
' 04/01/97-Frank Cleaned with CodeTools
' ----------------------------------------------------------

At FMS, we use the module comment header to identify who created it and the modifications made since its creation. Rather than procedure level detail, the module level comments summarize changes across the whole module. This is particularly useful for describing new features or changes that impact many procedures.

Issues

If the first portion of the first line of the module comments matches the start of the first line of a module, and the Always Add option is not selected, the module comments are not added. This allows you to prevent multiple module comment blocks every time you run Code Cleanup.


Procedure Comments

Total Access Code Tools 8 can apply standardized comment headers to existing procedures. Although the program cannot infer or deduce what the code is actually doing, it can take a lot of the drudgery out of the process of creating consistent comment headers for each procedure. Consider the following procedure:

Function Mystery(strIn As String) As String
  Dim intCounter As Integer
  Dim strTmp As String
  For intCounter = Len(strIn) to 1 Step -1
    strTmp = strTmp & mid$(strIn, intCounter, 1)
  Next intCounter
  Mystery = strTmp
End Function

Without the benefit of comments, it is difficult to tell what this function does. Now consider the same function after the Code Cleanup tool applies procedure comments:

Function Mystery (strIn As String) As String
  ' Comments  :
  ' Parameters: strIn -
  ' Returns   : string -
  ' Created   :
  ' Modified  :
  '
  ' --------------------------------------------------------
  Dim intCounter As Integer
  Dim strTmp As String
  For intCounter = Len(strIn) to 1 Step -1
    strTmp = strTmp & mid$(strIn, intCounter, 1)
  Next intCounter
  Mystery = strTmp
End Function

The standard comment block for each procedure lets you easily enter comments including comments for every parameter. You are spared the work of typing the block into every procedure. Since the comments are completely customizable, you end up with code that is consistently commented according to your standards.

Usage

To enable this option, check the Insert Procedure Comments check box. To specify where the inserted comments begin, check either the Before Procedure Declaration or After Procedure Declaration option.

To include procedure comments regardless of whether comments already exist in the procedure, select the Always Add check box. If this is not checked, and the first comment line matches what's already in the procedure, the procedure block is not added.

Editing Procedure Comment Text

To change the text for procedure comments, press the [Edit] button to display the Edit Procedure Comment Text form:

Edit Procedure Comment Text Form

You can modify this text to suit your documentation and coding style. This form is very similar to the comment feature of the Procedure Builder.

As with the Error Handling Insertion and Module Comments features, there are several character sequences that have special meaning to the Code Cleanup tool. By typing these character sequences into the text box, you tell the Code Cleanup tool to replace those characters with other values when the code is actually written into your procedures. These special character sequences are available in the Edit Procedure Comment Text form:

Characters	Meaning
#D#	Inserts the current date. The format is based on the Date Format option.
#P#	Inserts the name of the current procedure.
#T#	Inserts the current time.
#N#	Inserts parameter names with each parameter on a separate line starting at this position.
#U#	Inserts the name of the current user. The name of the current user comes from the VBA built-in CurrentUser() function which is based on Microsoft Access security. If you do not have user-level security implemented in your database, this function always returns the value "admin". You may want to substitute your initials rather than using #U#.
#R#	Inserts the data type of the parameter return value.
Using the default text shown above, the following procedure:

Function GetField (strTbl As String, strFld As String) As Variant
  Dim dbCurrent As Database
  Set dbCurrent = CurrentDB()
  GetField = dbCurrent.Tabledefs(strTbl).Fields(strFld).Value
End Function

is modified to this:

Function GetField (strTbl As String, strFld As String) As Variant
  ' Procedure  : GetField
  ' Comments   :
  ' Parameters : strTbl -
  '              strFld -
  ' Returns    : variant -
  ' Created   :
  ' Modified  :
  '
  ' --------------------------------------------------------
  Dim dbCurrent As Database
  Set dbCurrent = CurrentDB()
  GetField = dbCurrent.Tabledefs(strTbl).Fields(strFld).Value
End Function

Notice how the special characters are replaced by actual values, and the procedure's parameters and return type are included in the description.

Issues

As with module comments, if the [Always Add] option is not selected, procedure comments are not added if Total Access Code Tools 8 detects an existing comment block starting with the same line as the first line of the comments to be added.


Module Sorting

The module editor in Access 2.0 automatically sorted all procedures in a module by name. The new editor in Access 95/97 dropped this behavior in favor of the new "full-module" view. This view lets you see multiple procedures and keeps them in the exact order you entered them. This behavior can be very helpful, but it can also make it difficult to find specific procedures.

Code Cleanup offers a module sorting option to solve this problem. This option allows you to group your procedures by type, and sort them alphabetically by procedure name.

Using Module Sorting

Check the [Sort Procedures] check box to enable this option. There are a variety of ways to sort your procedures. To modify this, press the [Settings] button to display this form:

Procedure Sort Settings Dialog

To change the sort order, click on the item you want rearrange and press the Up or Down button to move the item within the list. For example, if you want Private Procedures to be grouped before Public Procedures/Methods, select the Private Procedures item and press the Up button until it appears at the top of the list.

You can also specify the order for property procedures by using the listbox on the right. Select the items and use the up and down arrows

Note: Technically, Access should only allow property procedures in class modules. However, Access does allow you to create property procedures in standard modules. If you select the Procedure Sorting option, and your standard modules contain property procedures, they are sorted in the same way as property procedures in a class module—Code Cleanup makes no distinction about the type of module.


Adding Line Numbers

Visual Basic for Applications (VBA) supports the Erl() function. This function returns the number of the line of code that was executing when an error occurred. This feature can be invaluable for debugging and troubleshooting your applications. For example, if an error occurs in a copy of your application that is installed at a remote location, your application can report the line number of code where the error occurred using the Erl() function. This makes your job easier as you try and pinpoint the part of your application that caused the error.

For the Erl() function to work, your module code must have actual line numbers in the code. For example, the following code shows a procedure that is correctly set up to use the Erl() function:

Function DeleteFile (strFile As String)
10 If Len(Dir(strFile)) > 0 Then
20   Kill strFile
30 Else
40   Beep
50   MsgBox "File Doesn't Exist."
60 End If
End Function

If the above function encounters a runtime error when it tries to delete the file with Kill statement, the Erl() function returns a value of 20 which identifies the line number where the error occurred.

Unfortunately, adding line numbers to code is a tedious manual process, requiring you to edit every procedure in every module in your application. Total Access CodeTools makes this process easy with the 'Add Line Numbers' feature of Code Cleanup.

When To Use Line Numbers

An important concept to understand when using Line Numbers is the idea of two versions of your database: the Development version and the Delivered version. The Development version of your database is the one that you, as a developer, work with to create and modify objects, write code, and test features.

The Delivered version of your application is the version that you distribute to users.

The reason for this distinction is that you should only add line numbers to your Delivered version. This is because line numbers make code very difficult to maintain—you don't want to be adding and editing code in a module that has line numbers.

To create a Delivered version, follow these steps:

1. Create a directory called "DELIVER". This directory will contained the "Delivered" version of your application.

2. Copy your Development database to the DELIVER directory and open it with Access.

3. Start Total Access CodeTools and select Code Cleanup.

4. Select the Line Numbers Option. Set the Start At and Increment values.

5. Finish running Code Cleanup.

The database in the Deliver directory is now your "Delivered" version. Use this version to distribute to users. When an error occurs, use the numbered code in this database to locate the specific error.

Using Line Numbers

To add line numbers, select the 'Add Line Numbers' checkbox on the Code Cleanup Options form. To identify the starting number, fill in the 'Start At' textbox. To identify the value to increment line numbers, fill in the 'Increment By' textbox.

Line Numbering Issues

Access 97 does not support line numbers greater than 65536. Fortunately, this is rarely an issue since Code Cleanup resets the line number to the Start At value for each module. This means that you won't run into the limit unless you have more than 65536 lines in a single module.

If Code Cleanup encounters a procedure that already has line numbering, it adjusts its current line number counter accordingly to ensure that duplicate line numbers are not written.

Code Cleanup does not add line numbers in certain places:

•	A line that already has a line number

•	A module's declarations section

•	Label lines

•	Comments LInes

•	Blank Lines

•	Lines that start with Dim, Static or Const

Also note that when you run Code Cleanup with the Line Number option enabled, the Procedure Sorting option is automatically disabled—you cannot add line numbers and sort procedures in the same pass.

The Accuracy of the Erl() Function

The original shipping version of Microsoft Access 97 contained a bug that caused the Erl() function to return incorrect line numbers. FMS has tested the Microsoft Office 97 Service Release 1 patch and it appears that Erl() functions properly if the patch is installed. However, FMS cannot guarantee the accuracy of the Erl() function, or any other part of Access. It is the responsibility of the user to ensure that Access is reporting correct results. For more information on the Microsoft Office 97 Service Release 1 patch, visit the Microsoft web site at http://www.microsoft.com/office.


Other Options

Code Cleanup offers three additional settings to control how code is modified.

Split DIMs into Multiple Lines

Access allows you to use multiple DIM statements on one line, such as:

Dim X, Y, Z As Integer

Although this is convenient, it leads to bugs, and makes your code difficult to read. For more information on this behavior, see the Writing Better Code section. Code Cleanup allows you to fix this problem by splitting every Dim statement into its own line. This results in more readable code and makes it much easier to spot bugs:

Dim X
Dim Y
Dim Z As Integer

Lines Between Procs

With the new full module view editor in Access, procedures can have different numbers of lines between them, or no lines at all. After a few weeks of development on a module, you end up with inconsistent spacing. Code Cleanup can fix this by inserting a user specified number of lines between procedures.

To enable the feature, select one of the numeric values from the combo box. To disable the feature, select "Don't Change" as the option.


Finishing Up

Once you set all the options, start the conversion process by pressing the [OK] button. You can also press the [Cancel] button to cancel the process.

First, a confirmation form appears to ask you if you want to continue. If you answer [Yes], the Code Cleanup Status form appears:

Code Cleanup Status Form

This form keeps you informed of the program's progress. Once all the phases are complete, the Module Parsing Complete form appears:

Module Parsing Complete Form

This form gives you the option to view messages generated by Code Cleanup, preview the code changes before they are applied to your database, and then decide whether or not to modify your module code. At this point, none of your modules have been modified.

More:

Viewing Code Cleanup Messages

Preview the Code

Apply the Changes

Checking the Results


Viewing Code Cleanup Messages

If errors do exist, a red box appears around the [View Messages] button with the text "Alerts Exist!". Be sure to press the [View Messages] button to see a list of problems that Total Access CodeTools encountered while trying to modify your code.

When you press the [View Messages] button, the View Messages form appears. Use the arrow keys to scroll through the messages. If you want a report of the messages, press the [Print] button. If your printer uses the A4 paper size, check the [Use A4 Paper] checkbox before pressing the [Print] button.

View Messages Form

If errors occurred that left your code in a state that is not usable, press the [Cancel] button from the Module Parsing Complete form. This stops the process, and no changes are made to your database.

The following section details the messages that can appear in this form.

Option Explicit Added

This message lets you know that the "Option Explicit" statement was added to a specific module. Because the addition of Option Explicit to modules can cause compilation problems, you should review this list carefully to determine which modules need immediate attention after running Code Cleanup.

EVAL Statement Encountered

An Eval statement was encountered. Code Cleanup cannot evaluate the contents of expressions inside Eval statements. Because of this, you should examine any code using the Eval statement to determine if it needs to be manually modified.

Variable Not Renamed

This message occurs when Code Cleanup cannot rename a variable because of a conflict with:

•	a reserved word

•	a public procedure name

•	a private procedure name in the current module

•	a user defined type

•	another variable which could be in the same procedure, or public

User Defined Type Not Renamed

This message occurs when a user defined type is not renamed because of a conflict with another user defined type.

User Defined Type Element Not Renamed

This message occurs when an element within a user defined type cannot be renamed because of a conflict with another element.

Module Comments Not Added

This message appears to let you know that module comments where not added to a specific module because the module already contained comments generated by Code Cleanup and you do not have the "Always Add" option turned on.

Procedure Comments Not Added

This message appears to let you know that procedure comments where not added to a specific procedure because the procedure already contained comments generated by Code Cleanup and you do not have the "Always Add" option turned on.

Error Handler Added

This message lets you know that error handling code was added to a specific procedure.

Line Numbering Exceeded

If you have the Line Numbering option turned on, this message appears when the line number applied by Code Cleanup exceeds the maximum limit of 65536. To solve this problem, lower your Starting Line number and Increment values. Note that this will only occur if a single module contains more than 65536 lines.


Code Cleanup - Finishing Up - Preview the Code
Preview the Code

Press the [Preview] button to preview the code changes. A form appears that shows both the original and modified versions of the code. Use the navigation buttons at the bottom of the form to move through the modules and procedures.

Note: You can view the Messages log at a later time by selecting Code Cleanup from the Total Access Code Tools 8 toolbar. If any messages exist from a previous run, the [View Messages] button on the Code Cleanup form is enabled. Press this button to view or print the log.


Apply the Changes

If no unexpected errors have occurred, you are ready to apply the changes. Your database is in this state:

•	All modified module code is stored in tables in the Total Access Code Tools 8 temporary directory.

•	No changes were made to your database's modules.

You now need to invoke the final phase which will:

•	Replace your module code with the modified code.

•	Close and re-open your database.

From the Module Parsing Complete form, press the [OK] button. A status form appears showing you the progress of the insertion of modified code into your database. When this process is complete, you are returned to the Total Access Code Tools 8 toolbar.


Checking the Results

Your database's module code is now transformed. The final step is to ensure that Total Access Code Tools 8 made the necessary modifications and additions to your code. You also want to ensure that your code still compiles. Do this by opening a module and selecting Debug | Compile All Modules from the Access menu.

If your database has problems which you cannot easily solve, consider restoring your backup and starting the process again. In almost all cases, problems are the result of code that could not compile correctly before the process was run, or a failure to follow all the steps listed here.


Overview of Other Tools

Full Toolbar, Other Tab

The following tools are available on the "Other" tab of the Total Access Code Tools 8 toolbar.

Note that if you are using the Small Toolbar, these tools appear on one form along with the other tools—there is no "Other" tab. See the Toolbar Section for more information.

More:

Constants Generator

List Fill Function Builder

Access Information

Fix Screen

BookMarker


Other Tools - Overview of Other Tools - Constants Generator
Constants Generator

This tool makes it easy to generate module code that declares constants for each of the objects in your database. You can then use these constants to refer to objects in your database instead of hard-coding object names throughout your code.

More Information


Other Tools - Overview of Other Tools - List Fill Function Builder
List Fill Function Builder

The Access List Fill functions make it easy to fill List Box and Combo Box controls with data from your program's code. The List Fill Function Builder makes the construction of these functions easy.

More Information List Fill Function Builder


Other Tools - Overview of Other Tools - Access Information
Access Information

This utility provides detailed information about your Access environment, including data on your installation of Access, your database, file versions, and various performance statistics.

More Information


Other Tools - Overview of Other Tools - Fix Screen
Fix Screen

During system development, the hourglass cursor may be turned on and echo mode turned off. If your code crashes, it can be difficult to get Access back into a usable, debuggable state. The Fix Screen utility solves this by providing a simple button to restore Access's echo and cursor state.

More Information


BookMarker

The BookMarker tool makes it easy to keep simple development notes in your database. You can keep notes about pending tasks, bugs, and unimplemented features, attaching each note to a specific object.

More Information


Other Tools - Constants Generator
Constants Generator

The Constants Generator creates code that assigns constants for each object in your database. You can then use these constants to refer to objects in your database instead of hard-coding object names throughout your code. For example, the following code shows a typical reference to an object using a hard-coded reference:

Set rst = db.OpenRecordset("Customers")

The use of constants changes the code to:

Const tblCustomers = "Customers"
Set rst = db.OpenRecordset(tblCustomers)

The advantage of having a constant defining each object in your code is that it eliminates typos when referencing an object in more than one place, and if you change the object name, you can change it in one place. Of course, this does not affect non-code references to objects in forms, reports, queries, etc. However, it can significantly reduce the number of changes you need to make in your modules when you change an object's name.

More:

Invoking the Constants Generator

Options

Preview the Code

Using the Generated Code


Invoking the Constants Generator

To invoke the Constants Wizard, press the [Const Gen] button on the Total Access Code Tools 8 Toolbar. An inventory of all objects in your database is generated and presented for your selection:

Constants Generator Form

The option group at the top provides a quick way to filter the list. Choose [All Objects] to display every object. If you want to include the Access system tables such as MSysObjects, make sure your Show System Objects setting is set to Yes under the Access View | Options | General settings.

By selecting the other options (Tables, Queries, Forms, Reports, etc.), you can limit the displayed list to just objects of the particular type. You can choose objects individually by clicking on the check box next to them. You can also press the [Select All] and [Clear All] buttons to select and de-select all the objects in the current view.


Other Tools - Constants Generator - Options
Options

Once the desired objects are selected, there are several options for creating the constants. Press the [Options] button to display this form:

Constants Generator Options Form

Public Constants

Check this option to have the constants declared as public constants rather than module-level constants. With this option, all the modules in your database (and loaded library databases) can use the constants.

Const Customer = "Customer"
            vs.
Public Const Customer = "Customer"

Prefix

You can add a prefix in front of each constant name. Enter the text for the prefix in the corresponding text box. This is used when you want all your variables, especially global variables, to start with a particular set of characters. For instance, using "c" changes the constant to:

Global Const cCustomer = "Customer"

Suffix

You can add a suffix to the end of each constant name. You can have both a prefix and suffix, or neither. This is what adding a "_c" suffix does:

Public Const Customer_c = "Customer"

Capitalize Constant Names

Check this option if you want to capitalize all the letters of the object name in the constant name. Without this option, the variables use the same case as your object name. This option does not affect the prefix, suffix, or naming conventions. If you want those capitalized, you should enter them in all caps. Using the last example, this option changes the code to:

Public Const CUSTOMER_c = "Customer"

Comment Each Line

Check this option to have a comment character ( ' ) placed in front of each line. This lets you generate constants for every object and insert it into your modules and have them disabled. Enable them when you want to use them by manually removing the comment character.

' Public Const Customer = "Customer"
' Public Const Orders = "Orders"
' Public Const Invoice = "Invoice"

Naming Conventions

You can specify a naming convention for each object type. You can eliminate the naming convention identifiers by deleting them, but that may cause conflicting constant names (e.g. a table and form both named "Customer").

You can choose to have the tags added as a prefix or suffix. The Prefix and Suffix values specified earlier are always put outside the naming convention characters. This example shows the application of a prefix (c) with the object naming convention:

Public Const ctblCustomer = "Customer"
Public Const ctblOrders = "Orders"
Public Const cfrmInvoice = "Invoice"

When you are finished setting the options, press [OK] to return to the main form.


Other Tools - Constants Generator - Preview the Code
Preview the Code

After selecting the objects and setting your options, press the [Preview] button to see the generated code:

Constants Generator Preview Code

If this code is not correct, return to the main form and adjust your selections and options.


Other Tools - Constants Generator - Using the Generated Code
Using the Generated Code

Once the preview form displays the code you want, you will want to place these variables in the declarations section of a new module, or the declarations section of an existing module. There are two ways to do this:

Press the [Copy To Clipboard] button to copy the generated code to the Windows clipboard. Then open the module, position the cursor where you want it to be inserted, and use the Edit|Paste menu command or Ctrl+V to insert the code.

Press [Cancel] to discard the generated code.


Other Tools - List Fill Function Builder
List Fill Function Builder

List fill functions provide a powerful mechanism for programmatically filling a combo box or list box. They are also difficult to write because they have a specific set of parameters and return values that Microsoft Access expects to see.

As described in the Access help file, you can write a VBA function that fills a list box or combo box with values.  Although this is generally a more complicated way to fill either type of box, it enables you to design a list box or combo box in which the options are constantly changing or are not stored in the database. For example, you could write a function that presents a list of dates in a list box, each one a specific number of days after the current date.

Note: For more information on list fill functions, search the Access 97 online help file for RowSourceType property. Then select the entry labeled Create a list box or combo box that gets its rows from a function.

The List Fill Function Builder allows you to quickly create the structure of a standard list fill function.

More:

Invoking the List Fill Function Builder

Options

Using the Generated Code


Invoking the List Fill Function Builder

Press the List Fill button to launch the List Fill Function Builder form:

List Fill Function Builder Form

Enter the name of the new list fill function in the Procedure Name text box. After typing this in, press [Tab] to move from the field. The builder generates the code and displays it in the Generated Code text box for your review.


Other Tools - List Fill Function Builder - Options
Options

Press the [Options] button to display the List Fill Function Builder Options form:

List Fill Function Builder Options Form

Include Comments

Select this option if you want the builder to include helpful comment text in the generated code.

Indent Lines

Select this option if you want the generated code to be indented.

Tab Width

This value controls how many spaces are used to indent the generated code. This value is only used if the Indent Lines option is selected.


Other Tools - List Fill Function Builder - Using the Generated Code
Using the Generated Code

Once the List Fill Function Builder generates the code you need, press the [Copy To Clipboard] button to place the generated code to the Windows clipboard. You can then transfer the code to the appropriate module in your database using the standard Windows Paste command.


Other Tools - Access Information
Access Information

Often when you are debugging Access applications, your code fails to run as expected even though there are no errors in the code. This can be caused by many factors, including settings in Access, and the Access application's current state. Getting information about these factors can be difficult because it requires an intimate knowledge of Access and Windows. Total Access Code Tools 8 makes this information available through the Access Information tool.

The Access Information tool shows information about your installation of Access, your database, operating system settings and more. The following section describes each of the items shown.

The Access Information Form

More:

Settings Tab

Versions Tab

Errors Tab

References Tab

ISAM Tab

Files Tab

MRU List Tab

System Tab

Other Tab


Settings Tab

Shows the settings for the various design windows available in Access. For example, Table Design Property Sheet value shows whether or not the Property Dialog is enabled when you are in table design and the coordinates where the Property sheet is to be displayed.

A value of 0;371;222;778;483; shows that the property sheet is turned off (the first number=0, turned on would be 1), and the following four numbers are the top, left, width and height settings for the window.


Versions Tab

Even though you are using version 8 of Access (Access 97), the actual files that make up the product vary widely in their version numbers. For example, the original version of Access 97 that shipped with Office 97 has different version numbers than the latest version. This is usually caused when you download service packs or install new software. The Versions tab of the Access Information tool shows you the versions of various things as Access reports them, and also shows the actual file versions.

Access Version

The version of Access as reported by Access. This value is an overall product version and will almost certainly always be 8.0 for Access 97, regardless of the updates or service packs applied.

Database Access Version

The version of the Jet database engine used to create the database. The following table shows the possible values and how they relate to other products that use the Jet engine:

Microsoft Jet Version (year released)	Microsoft Access	Microsoft Visual Basic	Microsoft Excel	Microsoft Visual C++
1.0 (1992)	1.0	N/A	N/A	N/A
1.1 (1993)	1.1	3.0	N/A	N/A
2.0 (1994)	2.0	N/A	N/A	N/A
2.5 (1995)	N/A	4.0 (16-bit)	N/A	N/A
3.0 (1995)	'95 (7.0)	4.0 (32-bit)	'95 (7.0)	4.x
3.5 (1996)	'97 (8.0)	5.0	'97 (8.0)	5.0
Jet Version

The version of the Jet database engine installed on your system. Like the Access Version value, this value does not reflect service packs and updates, and will almost certainly return a value of 3.5 for the Access 97 product.

Runtime Version

Whether or not you are running Access 97 in runtime mode. Note that Access 95 and Access 97 do not have a separate runtime executable like Access 2.0 did. They use the same program as a standard copy of Access, but invoke it in a special runtime "mode".

Access EXE Version

The actual internal file version of Access. Use this number to determine if the current copy of Access has had any service packs installed. Note that FMS cannot provide information on the various releases of file versions—only Microsoft has that information.

Jet DLL Version

The actual internal file version of the installation of the Jet database engine.

COMCTL32.OCX Version

The internal file version of the Windows Common Control ActiveX control. This file changes often as you install new versions of Microsoft development products.


Errors Tab

This tab shows the description, number, associated help file, and help context id of the last VBA error that occurred. For more information on these values, search Access online help for Err Object.


References Tab

This tab shows the total number of VBA project references in the current database, including how many are valid and how many are broken. For more information on VBA project references, search Access online help for References.


ISAM Tab

This tab shows various performance statistics for the Jet database engine, including the number of disk reads and writes, the number of cache and read-ahead cache reads, and the number of locks placed and released. These numbers start incrementing from the time you start Access.

Press the [Refresh] button on this page to show the latest values. Press the [Reset] button to reset to 0.


Files Tab

This tab shows information on files and paths.

Current DB Name

The name and extension of the current database.

Current DB Path

The path (without the name and extension) of the current database.

Code DB Name

The name and extension of the currently running library database.

Code DB Path

The path (without the name and extension) of the currently running library database.

INI File

The value of the Access SysCmd INIFile command. Note that INI files are no longer used in Access 97, so this value points to an imaginary file for backward compatibility.

Profile

The name of the profile (if any) in use. Access allows the use of User Profiles by implementing the /profile startup key. For more information, search Access online help for Startup command-line options.

Workgroup File

The path and name of the workgroup information file (also known as the security database) currently in use.


MRU List Tab

This tab lists the values of each of the entries in the Access "Most Recently Used" list. This list shows up on the Access file menu.


System Tab

This tab shows information about your computer's hardware, memory, operating system version, and operating system directories.


Other Tab

This tab contains miscellaneous information about your database

Database Under SCC

Tells you if the current database is under Source Code Control through Microsoft Visual SourceSafe.

Access Window Handle

The numeric handle to the Access main window.

Current User

The name of the currently logged in Access user.

Database Compiled

Whether all the module code in the database is compiled. Search Access online help for IsCompiled for more information.

User Control

The value of the Access UserControl property.

Database Replicated

Whether or not the current database is a replica (using Access database replication).


Other Tools - Fix Screen
Fix Screen

A common requirement in Access application development is controlling how the screen looks at certain points while your application is running. The two most common operations your program will make in this area are:

1.	Setting the cursor to an hourglass cursor (to show that the system is busy) by using the DoCmd.Hourglass True command./

2.	Temporarily suspending screen updates to hide screen activity, by using the DoCmd.Echo False or Application.Echo False commands.

Unfortunately, as you are debugging your application, these two operations can lead to big problems. For example, if an error occurs in your application while you have the hourglass cursor turned on and screen updates turned off, it is impossible to see the code or forms on the workspace. To solve this, you have to find the Access Debug window (which may itself be turned off) and type in the appropriate commands to re-enable screen updates and turn off the hourglass cursor.

The Total Access Code Tools 8 Fix Screen tool makes these steps as easy as pressing a button. If you find yourself in this state, simply press the Fix Screen button and Access is restored to a usable state. Fix Screen does the following:

•	Turns the hourglass cursor off

•	Turns screen painting on

•	Restores and refreshes the database window

•	Restores and refreshes the Access title bar


Bookmarker

As you develop an Access application, you need to keep track of pending items, undone tasks, bugs, and other pieces of development information. You may keep these in a NotePad file, a Word document, or even in a paper notebook. Total Access Code Tools 8 can help with its BookMarker tool.

This tool provides you with a simple user-interface to add, edit and delete notes about specific objects in your database. Best of all, the notes are stored in an Access table in your database. That way, the notes always travel with the database.

More:

Starting BookMarker

Using BookMarker

Adding a New Record

Changing an Existing Record

Deleting a Record

Printing Records

Removing BookMarker Objects from Your Database


Starting BookMarker

To start the BookMarker tool, select the BookMarker button from the "Other" tab of the Total Access Code Tools 8 toolbar. (If you are using the Small Toolbar, there are no tabs—see the Toolbar section for more information).

If this is the first time you use BookMarker on the current database, the program needs to create its notes table and report in your database. The following dialog appears:

BookMarker Setup Dialog

Press [OK] to have the table and report created in your database. Once this process is complete, the main BookMarker form appears.


Using BookMarker

The main BookMarker form is the main interface for working with the tool:

BookMarker Form

The following items are available on this form:

Notes for All Objects

This list shows all the notes currently in the table for the current database. The type and name of the object the note is related to is shown, along with any comments and a flag to denote whether or not the task is completed.

Notes

This shows the notes or comments for the specific entry that is currently highlighted in the Notes List.

Created By/On

This shows the name of the user who created the note and the time it was created.

Modified By/On

This shows the name of the user who last changed the note and when it was changed.


Adding a New Record

From the main BookMarker form, press [Add] to add a new note for an object. The Add Dialog appears:

BookMarker Add Dialog

To add a new record, follow these steps:

1.	Select the type of object you want to make note of by selecting a value in the Object Type combo box.

2.	Select the specific object in the Object Name combo box.

3.	Type in the notes or comments in the Notes field

4.	If the note is for a task that is already completed, check the [Done] checkbox.

5.	Press the [Add] button to add the entry to the table
–or-
Press the [Cancel] button to discard the new entry.

Note that the Created By/On fields are automatically filled with the current Access user name and current date and time.


Changing an Existing Record

Press the [Edit] button to make changes to an existing note record. The Edit dialog appears:

BookMarker Edit Dialog

Make changes to the record by editing text in the Notes field. You can also change the status by checking or unchecking the [Done] checkbox. Notice that the Modified By/On fields are automatically updated when you enter this form.

When completed, press [OK] to save your changes, or [Cancel] to discard them.


Deleting a Record

From the main BookMarker form, highlight a record in the Notes List and the press the [Delete] button. Confirm the deletion when the dialog box appears and the note is permanently deleted.


Printing Records

To print a list of records, press the [Print] button. This brings up the BookMarker report in preview mode. To print the report, select File | Print from the Access menu.


Removing BookMarker Objects from Your Database

You can remove the BookMarker objects from you database by deleting the USysCodeTools_Bookmarker table and the USysCodeTools_Bookmarker report from your database. Once you delete these objects, the data you saved in BookMarker is gone. Note that objects whose names start with USys are not visible in the database window unless you select Tools | Options from the Access menu. Then select the View tab and check the "System Objects" checkbox. This makes all USys objects visible.


Program Files

All Total Access Code Tools 8 files are stored in the folder where you installed the program. These files are installed by Total Access Code Tools 8:

File	Description
TACTL8_C.MDA	Main program library database.
TACTL8_S.MDA	Shell database for attached tables.
TACTL8_X.MDA	Alternate database for attached tables.
TACTOOL8.HLP	Total Access Code Tools 8 Help File.
README.TXT	Text file describing latest information.
TACTEMP.MDB	Temporary database.
TACTOOL8.CNT	Help File contents
Please check the README file for any additional information that was added after the manual and help file were written.


Uninstalling

To remove Total Access Code Tools 8:

•	Open the Windows Control Panel

•	Run Add/Remove Programs

•	Select Total Access Code Tools 8 from the list and click the [Add/Remove] button

After a few moments, the Total Access Code Tools 8 program files and its registry entries are deleted, and it no longer appears in your Access Tools|Add-in menu.

Note that the uninstall process does not remove any files created by Total Access Code Tools 8 after it was installed. For example, a temporary database may still exist in the CodeTools directory. After uninstalling Total Access Code Tools 8, check the directory where it was originally installed for any remaining files that need to be manually deleted.

The uninstall process does not remove the USys… objects added to your database by the Total Access Code Tools 8 BookMarker tool. See the Bookmarker section for information on manually deleting these objects.