Chapter 6

Leszynski Naming Conventions for Access


It's amusing to look at applications you wrote a year or two ago. If you're constantly improving yourself and learning new techniques, the approaches demonstrated in your older applications can seem outdated now, almost embarrassing. However, even if your development style today is different from yesterday, it would be nice to still be able to understand the structure of applications you wrote in the past and to be able to debug or upgrade their code. These tasks are facilitated by the use of standardized naming conventions. In this chapter, I detail for you the following:

Cartoon Chaos

"I learned the value of naming conventions years ago. Before I was at Microsoft, I was working on an accounting application for a client and needed to hire a contractor to do some of the work on the project. At the time, I didn't have any formal naming conventions, or a specific structure or style that I wanted the code to look like-I just wanted the app to work. Well, the contract coder did his part of the application, and it seemed to work fine. Some time later, however, I had to modify the application, and I couldn't figure out what the code did. For example, his code looked like this:

Barney = 500

Do While Fred < Barney

...

Fred = Wilma + 1

...

Loop

"My confusion stemmed from the fact that he had named all of his objects and variables after cartoon characters, and they had no consistency or link to the application at all!"


Tod Nielsen, General Manager, Microsoft Access Business Unit


Developers by nature have a love/hate relationship with naming conventions. Such standards are often seen as slowing the development process, increasing the size of object names and files, and stifling true programming creativity. And yet, without order, the laws of entropy invariably draw every project toward incoherent spaghetti code (or perhaps spaghetti objects). Thus, few developers would argue against the need for an ordered approach to development, but they want the least intrusive system.

Sorry, but you can't have it both ways! A system that is comprehensive and applied consistently will also, by nature, be intrusive. If you want to apply a naming convention to your objects, you will incur a penalty of a few keystrokes every time you type an object name. For a detailed explanation of why the small pain of extra keystrokes produces a large gain, refer to "Why Use Naming Conventions?" in Chapter 5, "Creating Naming Conventions."

In Chapter 5, I describe the methods you might use when creating your own naming conventions; you may want to review that chapter before proceeding for two reasons:

In this chapter, I detail for you the Leszynski Naming Conventions (LNC), a set of standardized approaches to naming objects during Access development. These naming conventions were born of necessity because some members of my staff would spend all day in Access development, year after year. They were also born of a different need-a void that existed in the marketplace due to a lack of consensus about development styles among leading Access developers.

LNC began in the days of Access 1.0, when it was referred to as L/R (the Leszynski/Reddick conventions). The L/R conventions were distributed broadly-with over 500,000 copies in print, and have become the most widely used conventions in the Access community. Over the last few years, we have received feedback about L/R from hundreds of developers and companies, and we have tried to accommodate some of their input, as well as our ongoing experiences, into LNC.

LNC improves upon the previous Access style by considering developers who work with multiple Microsoft development tools. Access, Visual Basic, Office, SQL Server, and other Microsoft products have more in common in their 97 versions than in any previous iterations. Consequently, this Access style dovetails with the LNC development style for the Microsoft toolset, as detailed in Appendix C, "Leszynski Naming Conventions for Microsoft Solution Developers."

I will use the terms naming conventions, style, and LNC interchangeably throughout this chapter.

Naming Conventions: A Primer

Naming conventions are one of the foundation elements of your overall development style. We developed our naming conventions primarily to achieve the following four objectives (as described in detail in Chapter 5, "Creating Naming Conventions"):

To meet these objectives, we create and apply consistent naming conventions to the Access objects. The following are target objects for naming conventions:

Because object names are the foundation upon which you build your entire application, it is almost impossible to change them once you begin development. Therefore, you will not find it cost-effective or time-efficient to retrofit naming conventions into your existing applications. For new applications, however, you should apply these conventions consistently from the moment you create your first object in a new database application file.

Leading Tags

Our naming conventions rely primarily on leading tags, which are several characters placed before an object's name (for example, qryOrderByMonth). This approach, known as Hungarian Notation, is defined in Chapter 5, "Creating Naming Conventions." Leading tags provide the following benefits:

Trailing Tags

If you are averse to Hungarian Notation for some reason and prefer trailing tags, LNC will still work for you. However, LNC prescribes no standard for locating and punctuating trailing tags. You will have to decide to offset them with underscoring (OrderByMonth_qry), by capitalization (OrderByMonthQry), or by some other technique.

Using trailing tags on database objects is problematic when your application also contains VBA code. The primary justification given by developers who prefer trailing tags on database objects is that it allows the objects to sort by base names rather than tags in ordered lists. However, such developers often still use leading tags and prefixes in their VBA code because there is no compelling argument for trailing tags on VBA objects such as variables. If you mix your styles like this, be prepared to justify your lack of consistency.

Because some developers, especially newer ones, prefer to minimize the complexity of a naming convention, LNC provides two levels for Access users. Level One has the minimum realistic subset of tags but provides lesser detail about the application. It is intended for users whose work is centered around the Database window and who develop database objects rather than applications. Level Two provides greater detail and the flexibility to create extensions. It is intended for application developers building expert solutions.

Access Object Types

For purposes of this chapter, I have created the following standardized terminology for grouping objects. I will use these group names when discussing naming conventions:

Structuring Object Names

In LNC, object names are constructed using the following syntax for Level One:

For Level Two, the syntax varies slightly:

The brackets indicate optional syntax elements. Notice that, for Level One, the BaseName element is required and the tag is optional in some cases. At Level Two, the tag element is required even though the BaseName is not in some cases. (This will be explained later in this chapter; see "Naming Conventions for VBA Objects.") Table 6.1 shows sample object names using these constructions.

Table 6.1 Object Names Constructed in LNC Format

Object Name

Prefixes

Tag

BaseName

Qualifier

Suffix

tblCust


tbl

Cust



qsumSalesPerfBest_WA


qsum

SalesPerf

Best

_WA

plngRecNumMax

p

lng

RecNum

Max


ialngPartNum

ia

lng

PartNum



Note in the syntax diagrams that the case of each element reflects its case in actual use. The element tag is in lowercase because the tags themselves are always lowercase.

What Is a Prefix?

A prefix is an identifier that precedes a tag and clarifies it narrowly. Prefixes describe one or more important properties of an object. For example, a Long Integer variable that is public in scope (declared Public) has a prefix p, as in plngRecNumMax. Prefixes are one or two characters long and lowercase. Multiple prefixes can be used together on one object, as in ialngPartNum, where i and a are both prefixes.

What Is a Tag?

A tag is a character phrase placed against an object base name to characterize it. In object-oriented programming terms, the tag is basically an identifier for the class. At Level One, tags define an object's general class: for example, qry for a query of any type. At Level Two, tags define the specific subclass: for example, qdel for a delete query.

Note that the word class here refers to a naming convention construction, not an object model construction. For example, there is only one Query (or QueryDef) class object in Access, and the data action (delete, update, etc.) is determined by its SQL statement rather than its class. LNC prescribes several tags for this one Access class.

Tags are three or four characters long for readability and to allow for the hundreds of combinations necessary as the Microsoft Office object model grows over time. They are always to the left of the base name and in lowercase, so that your eye reads past them to the beginning of the base name.

Tags are ideally created to mnemonically represent the word they abbreviate, such as "frm" for "form." However, some tags may not seem fully mnemonic for two reasons. First, the most appropriate, or obvious tag for a particular object may already be assigned to another object. Second, common objects (those with similar properties and usage) may exist in multiple Microsoft applications; therefore, the tag for one may be used to represent similar objects in other products, even if the names differ. For example, an Access Rectangle object is almost identical in structure and purpose to a Visual Basic Shape object. Since our Visual Basic conventions have existed longer than our Access conventions, I used the Visual Basic Shape object tag shp to also represent the Access Rectangle.

What Is a Base Name?

When you name a particular object, the base name is the starting point-it is a name you would use anyway if you had no naming conventions. For example, you might use Customer as the name for a table filled with customer records. The LNC guidelines for creating base names is driven by a set of guidelines discussed in the section, "Creating Database Object Base Names," later in this chapter.

What Is a Qualifier?

A qualifier is an extension following the base name that provides context to the specific use of an object. Unlike prefixes, which detail properties of the object (for example, that the variable has public scope), qualifiers describe how the object is being used in a context. For example, plngRecNumMax is obviously the maximum record number in an application that could also have variables for the minimum (plngRecNumMin) and current (plngRecNumCur) record numbers. Qualifiers are short and written in upper- and lowercase, using the list in Table 6.2.

Table 6.2 Suggested LNC Qualifiers

Qualifier

Usage

Curr

Current element of a set

Dest

Destination

First

First element of a set

Hold

Hold a value for later reuse

Last

Last element of a set

Max

Maximum item in a set

Min

Minimum item in a set

Next

Next element of a set

New

New instance or value

Old

Prior instance or value

Prev

Previous element of a set

Sub

Subform/subreport (Level One only)

Src

Source

Temp

Temporary value

What Is a Suffix?

Suffix elements provide specific information about the object and are only used as "tie breakers" when more detail is required to differentiate one object name from another. These are the only elements in the syntax for which our naming conventions do not specify standardized values. You will create suffix items as needed by your company, development team, or application. For example, a series of queries that summarized the best sales performance by state would need the state name in the object name to properly qualify it, as in qsumSalesPerfBest_AK. Placing the state name at the very end of the name as a suffix item allows the entire collection of related queries to sort together, like this:

qsumSalesmanPerfBest_AK

qsumSalesmanPerfBest_AL

...

qsumSalesmanPerfBest_WY

Since the suffix is the last piece of information in a name, it can be easier for the eye to find if delimited from the rest of the object name with an underscore, as shown. Use of the underscore is optional.

Creating Database Object Base Names

The building blocks of your Access application are its database objects. When you create a base name for an object, consider its purpose, the approaches used in naming associated objects, and the following rules of thumb.

Rules for Base Names

Observe the following rules when developing a base name for a new database object:

The first two rules also apply to the other naming convention elements: prefixes, tags, qualifiers, and suffixes. These elements should never include spaces or special characters.

You should abbreviate object base name elements wherever possible using a standardized abbreviation table such as Table 6.17 in the section "Standardized Abbreviations." You can extend LNC with your own standard abbreviations as well. You should create and use standardized terminology in your applications wherever possible; for examples, see "Standardized Terminology."

Base Name Length Limits

LNC includes some constraints and suggestions for object name lengths.

I target table name lengths at a 15-character maximum for the following two reasons:

Beyond the 15-character target, I absolutely limit table name lengths to 30 characters, which maintains compatibility with the table name length limit in SQL Server. For other objects, I target a 30-character limit as well because Access shows no more than the first 30 characters of object names in the default width of any of its lists or property grids.

Compound Base Names

An object name that is driven by a table must include the base name of that table. Thus, for the tblCust table, the primary query would be qryCust, the primary form frmCust, and so forth. Queries, forms, and reports that are sourced from multiple tables should reflect the base names of all the tables if it is practical. If not, you must decide which tables are the primary tables and list as many as possible in the name.

Generally, in a multi-table query, form, or report, the most "important" tables are not necessarily the first and second, but more often the first and last. So, a query joining tblCust to tblAddr to tblPhone to get the phone numbers for customers would be named qryCustAddrPhone if the address information is included in the query result. If the address information is used simply to join to the phone numbers and is not displayed, the query would be simply qryCustPhone.

Bound control base names on forms and reports are always equivalent to the base name of the bound field (the ControlSource). For example, a text box tied to the LastName field is named txtLastName.

Field Base Names

As a part of standardizing terminology, I adhere to the concept of a centralized data dictionary. This principle dictates that any fields in the data structure having the same name must have the same properties and data purpose. For example, if the LastName field in tblCust is of type Text 30 and holds the customer last name, then any other field named LastName in the same application must have the same type, length, properties, and purpose. If your application needs last name fields for both customers and dealers, this rule dictates that you name them differently (such as CustLastName and DlrLastName).

Applying the centralized data dictionary principle also means that table fields do not get leading prefixes or tags because I prefer my data dictionaries to be platform-neutral. That way, a field does not have to be renamed if data is upsized or ported to a platform with different data types. A table is still called a table in SQL Server, so moving tblCust there from Access would not require renaming.

However, if tblCust had a field lngCustID defined as a Long Integer in Access, moving the database to SQL Server would require a field rename to intCustID because SQL Server uses the data type name Integer to mean the same as the Access Long Integer. Because renaming fields affects all dependent objects and code, avoid it at all costs. Therefore I would call the field simply CustID.

If field tags are part of your development style and you must use them, LNC provides the tags listed in Table 6.3.

Table 6.3 Database Field Object Tags

Data Type

Tag

AutoNumber (Random non-sequential)

idn

AutoNumber (Replication ID)

idr

AutoNumber (Sequential)

ids

Binary

bin

Byte

byt

Currency

cur

Date/Time

dtm

Double

dbl

Hyperlink

hlk

Integer

int

Long

lng

Memo

mem

Ole

ole

Single

sng

Text (Character)

chr

Yes/No (Boolean)

bln

As much as is practical, LNC maps similar Jet and SQL Server data types to the same tag so that the data structure can be upsized with a minimum of object name changes. See Appendix C, "Leszynski Naming Conventions for Microsoft Solution Developers," for an expanded discussion of this subject.

Unlike tags, qualifiers and suffixes are acceptable in field names because they describe the object's data purpose and not its type.

Ordering Base Name Elements

Object base name elements should be ordered from left to right with respect to their importance, readability, and desired sort order. In the example from the previous paragraph, CustLastName is a better name than LastNameCust because the group name portion (Cust or Dlr) carries greater weight in an object's name than the specific item name (LastName or PhoneNum). Think of Cust as the name of a collection of customer-related items and this rule becomes clear-what you are really saying is that CustLastName is analogous to Cust(LastName) or Cust.LastName in Collection.Object terminology.

Some of you will naturally carry this example to its extreme and say that the Customers collection really has a Names collection with multiple elements, including Last, thus the representation of that idea as Cust.Name(Last) would lead to the field name CustNameLast instead. Such a construction model still fits within the rules of LNC, and I won't debate you against using it. In practice, however, such names often become fairly unreadable, even if they are accurate.

Naming Conventions for Database Objects

In Level Two of LNC, the following Access (and Jet) database objects require tags:

Level One also recommends that you place tags on every object name. However, Level One recognizes that nondevelopers may prefer to save time, effort, and complexity by leaving tags off of objects where the context is obvious while viewing the Database window. Thus, Level One users are required only to place the qry tag on queries in order to differentiate them from tables in any combined lists, such as the Choose the Table or Query combo box on Form and Report Wizards. Placing tags on other objects in the preceding list is optional.


I described earlier how difficult it can be to propagate name changes throughout a database, so if you are a casual user now but expect to become a developer later-and thus migrate from Level One of LNC to Level Two-you would be unwise to leave tags off any object names. Use the Level Two tags now on all objects.


Tags for Database Window Objects

Table 6.4 lists the Level One tags for Database window objects. Note that only one tag exists for each object type.

Table 6.4 Level One Database Window Object Tags

Object

Tag

Class module

cls

Form

frm

Macro

mcr

Module

bas

Query

qry

Report

rpt

Subform

fsub

Subreport

rsub

Table

tbl

Though Level One is the simplified naming model, it is necessary to provide tags to identify subform and subreport objects specifically. The distinction between objects and subobjects is critical for nondevelopers who navigate using the Database window. Because it is not appropriate to open subforms and subreports directly from the Database window, they must be clearly identified and grouped using tags. Table 6.5 lists the Level Two tags for Database window objects.

Table 6.5 Level Two Database Window Object Tags

Object

Tag

Class module

cls

Form

frm

Form (class module)

fcls

Form (dialog box)

fdlg

Form (lookup table)

flkp

Form (menu/switchboard)

fmnu

Form (message/alert)

fmsg

Form (subform)

fsub

Form (wizard main)

fwzm

Form (wizard subform)

fwzs

Macro

mcr

Macro (for form/report)

m[obj]

Macro (bar menu)

mmbr

Macro (general menu)

mmnu

Macro (shortcut menu)

mmct

Macro (submenu/drop-down)

mmsb

Module

bas

Query

qry

Query (form/report source)

q[obj]

Query (append)

qapp

Query (crosstab)

qxtb

Query (data definition)

qddl

Query (delete)

qdel

Query (form filter)

qflt

Query (lookup)

qlkp

Query (make table)

qmak

Query (select)

qsel

Query (SQL pass-through)

qspt

Query (union)

quni

Query (update)

qupd

Report

rpt

Report (detail)

rdet

Report (sub)

rsub

Report (summary)

rsum

Table

tbl

Table (attached Btrieve)

tbtv

Table (attached dBASE)

tdbf

Table (attached Excel)

txls

Table (attached Fox)

tfox

Table (attached Lotus 1-2-3)

twks

Table (attached ODBC)

todb

Table (attached Paradox)

tpdx

Table (attached SQL Server)

tsql

Table (attached text)

ttxt

Table (lookup)

tlkp

Table (many-to-many relation)

trel

Table (summary information)

tsum

The tags for Level Two provide rich detail about the objects and sort objects with similar attributes. For example, lookup tables and their maintenance forms are often used repeatedly in multiple applications. The tags tlkp and flkp clearly identify these objects, making it easy for you to perform tasks such as importing all lookup-related objects from an existing database into a new one when using the object list in the Access Import dialog box.


If you like the idea of using tags to sort objects but still need access to the base name of the object, place each object's unmodified base name in its Description property. When you need to view database objects sorted by their base name, sort the Database window using the Description column. To add a description to a database object, highlight the object in the Database window and select View Properties.

In two special cases above as indicated by the [obj] placeholder, the conventions prescribe a single character tag added to the front of the full object name (including the tag) of a related object. This situation occurs when

and

Tags for Form and Report Control Objects

Table 6.6 lists the Level One tags for control objects on forms and reports.

Table 6.6 Level One Form and Report Control Object Tags

Control

Tag

Label

lbl

Other types

ctl

These Level One control tags provide no differentiation of control type other than to distinguish labels, which do not interact with the user, from controls that can display or modify data. This level of detail is inadequate for applications where VBA code will be written behind forms or reports.

Table 6.7 lists the Level Two tags for control objects on forms and reports. A different tag is provided for each built-in control type. VBA code written behind forms and reports using this convention will reflect a control's type in its event procedure names (for example, cboState_AfterUpdate). The automatic sorting provided by this notation in the Access form and module design windows can be very helpful during development. The table lists controls by their Access internal names rather than by more commonly used terms.

Table 6.7 Level Two Form and Report Control Object Tags

Control

Tag

TypeOf

Bound object frame

frb

BoundObjectFrame

Chart (graph)

cht

ObjectFrame

Check box

chk

CheckBox

Combo box

cbo

ComboBox

Command button

cmd

CommandButton

Custom control

ocx

CustomControl

Detail (section)

det

Section

Footer (group section)

gft[n]

Section

Form footer (section)

fft

Section

Form header (section)

fhd

Section

Header (group section)

ghd[n]

Section

Hyperlink

hlk

Hyperlink

Image

img

Image

Label

lbl

Label

Line

lin

Line

List box

lst

ListBox

Option button

opt

OptionButton

Option group

grp

OptionGroup

Page (tab)

pge

Page

Page break

brk

PageBreak

Page footer (section)

pft

Section

Page header (section)

phd

Section

Rectangle

shp

Rectangle

Report footer (section)

rft

Section

Report header (section)

rhd

Section

Section

sec

Section

Subform/Subreport

sub

Subform

Tab

tab

TabControl

Text box

txt

TextBox

Toggle button

tgl

ToggleButton

Unbound object frame

fru

ObjectFrame

The placeholder [n] in the headers and footers for reports indicates the GroupLevel index, as in gft0 for the first group footer.

All control tags are three characters long.

Appendix C, "Leszynski Naming Conventions for Microsoft Solution Developers," includes a listing of tags for ActiveX controls that can be used in Access applications.

Prefixes for Database Objects

The following list describes the database object prefixes and their usage. With the exception of zz for backup copies of objects, these prefixes are Level Two (i.e. developer) elements only.

Note that most of these database object prefixes use z as the first character. Database objects with such prefixes sort to the bottom of the Database window, below the user- or developer-oriented objects that are accessed more frequently.

Using Menus and Toolbars

Menu macros behave differently than standard macros; thus, they fall under separate guidelines when you create their names. Menu macros are used either for bar menus or for shortcut menus.

When creating menu macros, bar menu macros should be prefixed with mmbr, submenus (drop-down) with mmsb, and shortcut menus with mmct. The tags for command bar menu objects are similar: mbr for a menu bar (which includes its submenus), mct for a shortcut menu (also now called a popup), and tbr for a toolbar object.

Detailed tags like theses will help greatly when you are selecting from a list of menus to assign to the MenuBar or ShortcutMenuBar properties of a form or in the Startup properties dialog box. This convention also sorts menu macros by type in the Database window.

Because any saved submenu macro can be selected as the primary object in a shortcut menu, this convention gives you the flexibility to create component submenus, which can be called from both multiple bar menus and multiple shortcut menus and thus reused. The structure of command bar menus is different from that of menu macros; you cannot create a component submenu and use it by name in multiple bar menus. You can, however, make a copy of a submenu to another bar menu when customizing toolbars.

Database Object Name Examples

Table 6.8 shows examples of database objects, applying the various conventions in this section.

Table 6.8 Database Object Naming Convention Examples

Object

Description

zhtxtUser

Hidden system text box

mfrmCust

Macro for form

zttfoxCustHist

Temporary Fox attachment

qupdCustBal_Dlr

Update customers that are dealers

trelCustAddrPhone

Link many addresses to phones

Creating VBA Object Base Names

When creating VBA object base names, remember that the base name must be descriptive even in the absence of its tag. For many programmers, the syntax Dim I As Integer for a loop variable is quite acceptable. Within LNC, however, the variable named I would become iintLoop. Single character variable names, especially without tags, are not allowed. Instead, create a list of short and standardized work variables to handle common daily needs. Table 6.9 suggests the LNC approach to commonly used variables.

Table 6.9 Standardized LNC Work Variables

Variable

Description

blnRet

Captures a True or False return value from a function call

cccDebug

A conditional compilation constant for turning conditional debugging on and off

dbsCurr

Database object variable for the current Access database (CurrentDB)

intErr

Preserves the value of Err

iintLoop

A counter for For...Next loops

intMsg

Captures a return value from a MsgBox() function call

intResult

Holds the result of math operations (also dblResult, lngResult, etc.)

intRet

Captures a numeric return value from a function call (also dblRet, lngRet, etc.)

intWork

Used for any temporary work (also dblWork, lngWork, etc.)

strMsg

Used to build long message box strings

strOrder

Used to build long SQL ORDER BY strings

strSQL

Used to build long SQL strings

strWhere

Used to build long SQL WHERE clauses

Rules for Base Names

Creating VBA object base names involves observing the same rules listed in the section "Rules for Base Names" for creating database object base names, as follows:

Note that the last rule is an expanded version of the corresponding rule for database objects, which states that table base names should propagate into names of dependent objects. In VBA, that rule expands to require a reference in variable names to objects of any type that they relate to. For example, a RecordSet variable created on tblCust should be named rstCust. Also, if a string array variable of part numbers astrPartNum has an Integer index variable, its name should include the array's base name: iaintPartNum.

Base Name Lengths

There is no rule limiting variable name length, but common sense would dictate that variable names longer than 15 or 20 characters waste a lot of keystrokes. The VBA module editor shows roughly the first 30 characters of a procedure name by default, so 30 is suggested as the target maximum procedure name length.

Abbreviate VBA object base name elements wherever possible using a standardized abbreviation table, such as in Table 6.17 in the section "Standard Abbreviations." You can extend LNC by creating your own standard abbreviations as well. You should create and use standardized terminology in your applications wherever possible; for examples see the section "Standardized Terminology."

Compound Base Names

Procedure base names follow the construction ObjectVerb, where the Object portion describes the primary object type affected (often the same as the primary argument), and Verb describes the action. This style sorts functions and subs by their target object when shown in ordered lists:

Naming Conventions for VBA Objects

In Level Two of LNC, the following VBA objects require tags:

Optional tags also are available for some types of procedures. By definition, if you are a Level One user, LNC assumes that you are not writing VBA code. If you are creating procedures, you are a Level Two user and should always apply Level Two tags and prefixes throughout your application.

In the syntax diagram earlier, I noted that base names are optional in some Level Two constructions. When programming in VBA in Level Two, the tag element is always required, but the base name is optional for local variables only. For example, a procedure that declared only one form object variable could legitimately use the variable name frm, which is a tag without a base name. Type structures, constants, and variables that have module-level or public scope must have both a tag and base name.

Tags for Variables

Visual Basic for Applications variable tags are noted in Table 6.10, Table 6.11, and Table 6.12, grouped by type of variable.

Table 6.10 Tags for VBA Data Variables

Variable Type

Tag

Boolean

bln

Byte

byt

Conditional Compilation Constant

ccc

Currency

cur

Date

dtm

Double

dbl

Error

err

Integer

int

Long

lng

Object

obj

Single

sng

String

str

User-Defined Type

typ

Variant

var

In the table above, note that Conditional Compilation Constant, Error and User-Defined Type are not true data types (created with Dim name As type) but rather programming concepts. A Conditional Compilation Constant variable is a flag of type Boolean, an Error variable is a Variant created with the CVErr() function, and user-defined types are unique constructs.

Table 6.11 Tags for VBA Object Variables

Object

Tag

Application

app

Assistant

ast

Collection

col

CommandBar

cbr

CommandBars

cbrs

Control

ctl

Controls

ctls

CustomControl

ocx

CustomControlInReport

ocx

DoCmd

dcd

Form

frm

Forms

frms

GroupLevel

lvl

Module

bas

Modules

bass

Page

pge

Pages

pges

Reference

ref

References

refs

Report

rpt

Reports

rpts

Screen

scn

Section

sec

Table 6.12 Tags for Data Access Object Variables

Object

Tag

Connection

cnc

Connections

cncs

Container

con

Containers

cons

DBEngine

dbe

Database (any type)

dbs

Database (Btrieve)

dbtv

Database (dBASE)

ddbf

Database (Excel)

dxls

Database (FoxPro)

dfox

Database (Jet)

djet

Database (Lotus 1-2-3)

dwks

Database (ODBC)

dodb

Database (Paradox)

dpdx

Database (SQL Server)

dsql

Database (Text)

dtxt

Databases

dbss

Document

doc

Documents

docs

Dynaset

dyn

Error

err

Errors

errs

Field

fld

Fields

flds

Group

gru

Groups

grus

Index

idx

Indexes

idxs

Parameter

prm

Parameters

prms

Property

prp

Properties

prps

QueryDef (any type)

qdf

QueryDef (Btrieve)

qbtv

QueryDef (dBASE)

qdbf

QueryDef (Excel)

qxls

QueryDef (FoxPro)

qfox

QueryDef (Jet)

qjet

QueryDef (Lotus 1-2-3)

qwks

QueryDef (ODBC)

qodb

QueryDef (Paradox)

qpdx

QueryDef (SQL Server)

qsql

QueryDef (Text)

qtxt

QueryDefs

qdfs

Recordset (any type)

rst

RecordSet (Btrieve)

rbtv

RecordSet (dBASE)

rdbf

RecordSet (dynaset)

rdyn

RecordSet (Excel)

rxls

RecordSet (Fox)

rfox

RecordSet (Lotus 1-2-3)

rwks

RecordSet (ODBC)

rodb

RecordSet (Paradox)

rpdx

RecordSet (snapshot)

rsnp

RecordSet (SQL Server)

rsql

RecordSet (table)

rtbl

RecordSet (text)

rtxt

Recordsets

rsts

Relation

rel

Relations

rels

Snapshot

snp

Table

tbl

TableDef (any type)

tdf

TableDef (Btrieve)

tbtv

TableDef (dBASE)

tdbf

TableDef (Excel)

txls

TableDef (FoxPro)

tfox

TableDef (Jet)

tjet

TableDef (Lotus 1-2-3)

twks

TableDef (ODBC)

todb

TableDef (Paradox)

tpdx

TableDef (SQL Server)

tsql

TableDef (Text)

ttxt

TableDefs

tdfs

User

usr

Users

usrs

Workspace

wsp

Workspaces

wsps

The tags dyn, snp, and tbl above for dynaset, snapshot, and table objects are directly relevant to users of Access 1 and Access 2. Starting with Access 95, these object types are allowed only as a subtype of recordset variables, thus the recordset tags rdyn, rsnp, and rtbl.

In Tables 6.11 and 6.12, tags for collection variables are made by adding s after the tag for the object type stored in the collection.

Even though I noted above that a tag by itself is a legitimate variable name, some variable tags shown (such as int) are reserved words and will not compile in VBA-such tags require a base name.

Creating Automation Variables

Table 6.13 lists the LNC tags for Automation server variables.

Table 6.13 Tags for Automation Server Variables

Object

Tag

Access.Application

accapp

DAO.DBEngine

daodbe

Excel.Application

xlsapp

Excel.Chart

xlscht

Excel.Sheet

xlssht

Graph.Application

gphapp

MAPI.Session

mpimps

MSForms.DataObject

fmsdoj

MSForms.UserForm

fmsufm

MSProject.Application

prjapp

MSProject.Project

prjprj

OfficeBinder.Binder

bndbnd

Outlook.Application

otlapp

PowerPoint.Application

pptapp

SchedulePlus.Application

scdapp

SQLOLE.SQLServer

sqlsvr

TeamManager.Application

mgrapp

Word.Application

wrdapp

Word.Basic

wrdbas

Note that variables for objects in the object hierarchy of a referenced type library can be dimensioned directly by class, as in this line:

Dim xlsapp As Excel.Application

Alternately, if the variable is created with late binding (i.e., as a generic object) rather than early binding (as an object class), the prefix o is added to denote an object variable:

Dim oxlsapp As Object

Set oxlsapp = CreateObject("Excel.Application")

Our naming convention for entry point variables into Automation server applications follows this syntax:

applicationtag [entrypointtag] primaryobjecttag BaseName

The item applicationtag is a three-character notation for the server application, and entrypointtag is three characters denoting the entry point used. The entrypointtag is optional and should be used when clarification is necessary (when variables for several entry points are declared in the same procedure), or when the entry point is not the standard Application object. The primaryobjecttag describes the ultimate class of the object (the one you intend to address with the variable). The BaseName is optional and clarifies the use of the variable, as with other VBA variables.

For example, the following code creates an Excel Range object and manipulates it.

Sub SalesCheck()

Dim xlswksSales As Excel.Worksheet

Dim xlsrngYTD As Excel.Range

Set xlswksSales = GetObject("C:\Data\Sales.Xls", "Excel.Sheet")

Set xlsrngYTD = xlswksSales.Range("YTDSales")

If xlsrngYTD.Value < 100000 Then

MsgBox "Sales are lame.", vbOKOnly, "Get to Work!"

End If

Set xlswksSales = Nothing

End Sub

In this example, the Range object is technically several layers deep in the application hierarchy, and a purely accurate combination of tags and code structure would yield this line of code, which actually runs:

Set xlsappwkbwksrngYTD = _

xlsapp.ActiveWorkbook.Worksheets("Sales").Range("YTDSales")

In practice, of course, such nomenclature is unwieldy, and the shorter style is more friendly yet still accurate.

I prefer to show the server name in the variable declaration for clarity of code-while both lines below will run, the second is less ambiguous:

Dim xlsrng As Range

Dim xlsrng As Excel.Range

See the section "Creating Your Own Tags" for more discussion of Automation syntax.

Prefixes for Variables

The prefixes for VBA variables can be categorized into two groups: prefixes for scope and all other prefixes. Because the model for variable scope changed somewhat between Access 2 and 95, I will discuss scope prefixes first. The following prefixes are ordered by increasing (broader) scope.

No prefix
Use no prefix for variables that are local to a procedure.

s

Place this prefix before variables that are declared locally to a procedure with a Static statement.

m

Use this prefix for module-level variables that are declared with Dim or Private statements in the Declarations section of a module.

p

Use this prefix to denote variables declared as Public in the Declarations section of a form or report module. Such variables are publicly available to other procedures in the same database only.

g

Use this prefix to denote variables declared as Public in the Declarations section of a standard module. Such variables are truly global and may be referenced from procedures in the current or other databases.

When used, scope prefixes always begin a variable name and precede any other prefixes.

In addition to scope, there are other characteristics of variables that can be identified by prefixes, as follows:

a
Use this prefix to denote a variable that is declared as an array, including a ParamArray argument to a function.

c

This prefix is placed before constants defined with the Const statement.

i

Use this prefix to denote a variable (usually of type Integer) that serves as an index to an array or an index counter for a For...Next loop.

o

This prefix is placed before object variables that reference Automation servers through late binding (and Object variable), where the tag denotes the type of server.

r

Use this prefix for variables that are arguments (parameters) passed in to a procedure and declared as ByRef, or not declared as either ByRef or ByVal (including a ParamArray), which implies ByRef.

t

Use this prefix to describe a variable that is declared as a user-defined Type structure. The variable should inherit the base name from the original declaration for the type.

v

Use this prefix for variables that are arguments (parameters) passed in to a procedure and declared as ByVal.

Because a prefix provides a very detailed description of a variable, the number of allowable prefix combinations is limited, as shown in Table 6.14.

Table 6.14 Allowable Prefix Combinations

Any One of These...

...Can Come Before This

s, m, p, g, r, v

a

m, p, g

c

s, m, p, g, r, v

e

s, m, p, g, r, v

i

s, m, p, g, r, v

ia

s, m, p, g, r, v

o

m, p, g

t

Variables require a unique prefix when declared Public in a widely distributed application. See the "Tags and Prefixes for Procedures" section below for more information.

Naming Constants

Access 95 introduced sweeping changes in the area of constants. The changes most relevant to naming conventions include the following:

When creating constants, use a scope prefix (if appropriate), the prefix c, and the suitable tag for the constant's data type. To properly synchronize the tag and the data type, do not let Access assign the type; always use the full Const name As datatype syntax.

Constants require a unique prefix when declared Public in a widely distributed application. See the "Tags and Prefixes for Procedures," section for more information.

Tags and Prefixes for Procedures

Whether to prefix and tag procedure names and how to do so are debatable subjects. This style neither requires nor encourages placing characters before a procedure name except in the situations discussed next.

Prefixes for Procedures

Procedures can have scope similar to that of variables-s (Static), m (Private), p (Public), or g (global Public). LNC supports the use of these scope prefixes on function names if they solve a particular need and are used consistently throughout an application.

If you are creating software for retail, for inclusion in the public domain, or for some other form of broad distribution, LNC requires that you prefix Public variables, constants, and procedures with a unique author prefix identifying you or your company. The prefix consists of two or three unique characters and an underscore and prevents your object names from conflicting with others in databases on a user's machine.

To create an author prefix, use your personal or company initials. For example, author prefixes for my companies are lbi_ for Leszynski Barker Inc., lci_ for Leszynski Company Inc., and kwc_ for Kwery Corporation. Before using your selected prefix, make an effort to determine if the prefix is already widely in use.

Tags for Procedures

The LNC style prescribes the following naming convention tags for procedures:

cbf
Use this tag on procedure names for code behind a form or report that is Private to the object. This tag clearly differentiates such procedures from Property procedures and event procedures.

mtd

Use this tag to differentiate class method procedures, which are method procedures defined in a class module and Public procedures in a form or report object (which are de-facto methods).

prp

Use this tag on Property procedure names defined with Property Get, Property Let, and Property Set statements. This tag clearly differentiates such procedures from functions, subs procedures, and event procedures.

LNC does not require or suggest assigning a data type tag to functions to reflect their return value. However, if you have a specific need to tag procedures to reflect their return value type, use the appropriate tags from the section "Tags for Variables" and apply them consistently to all procedures in an application.

Using Macros Instead of VBA

Occasionally, you might have a good reason to using macros to create action scripts. If so, apply the rules described earlier for VBA procedure base names to your macro base names. Macro groups in the Database window should utilize the macro prefixes and tags previously noted. Individual macros within a macro group do not have prefixes or tags except for event macros.

All macros for a specific form or report should be placed into one macro group with the form or report name as the macro base name. Within the macro group, create standard macros and event macros for the form or report. If a macro is tied to a form or report event, show the event name or an abbreviation in the macro name. For example, you would store macros related to frmCust in macro group mfrmCust. Event macro names in the group might include Form_Current and txtLastName_Change.

Macros that are not specific to a form or report should hold actions that have some common functionality or purpose. Use the same grouping methodology (related items together) that you would apply to locate procedures in VBA modules.

Visual Basic for Applications Object Name Examples

Table 6.15 shows examples of VBA variables applying the various conventions in this section.

Table 6.15 VBA Variable Name Examples

Declaration

Description

Dim oxlsaBudget As Object

Excel.Application

Function lci_ArraySum (ParamArray ravarNum() As Variant) As Double

Company identifier

Public giaintPartNum As Integer

Global index into array

Const clngCustNumMax As Long = 10000

Const for max CustID

Function FileLock(ByVal vstrFile As String) As Boolean

ByVal argument

Creating Your Own Tags

What do you do when LNC doesn't address a particular object naming need? First, contact me at stanl@kwery.com and let me know why so that we can improve the style for the benefit of all users. Second, consider whether what you are trying to do is covered by the style in some other way. For example, in your development team you call tables that link two other tables in a many-to-many relationship linking tables, and you want to create a new table tag, tlnk, as a result.

However, on examination of all table tags, you find trel already exists, defined as "Table (many-to-many relation)," which is the correct tag for what you need. Even though the nomenclature is not exactly what you use, it is better to use an existing tag than to create another one.

Finally, when other options are exhausted, you can create a custom tag to address your need. When creating a custom tag, use the following guidelines:

When you create a new tag, use a character combination that is mnemonic enough to uniquely shorten the word the tag represents, and only use characters from the root word or a generally accepted shorthand.

Table 6.16 Some Standard Tag Components

Item

Segment

Example

Location

bar

br

pbr

anywhere

database/databound

d

dout

leading

definition

df

qdf/tdf

anywhere

footer

ft

fft

anywhere

form

f

fdlg

leading

header

hd

fhd

anywhere

macro

m

mmnu

leading

MAPI

mp

mpm

leading

module

b

bas

leading

query

q

qsel

leading

report

r

rdet

leading

set

st

rst

anywhere

table

t

tdf

leading

view

vw

lvw

anywhere

To create tags for object variables pointing to Automation server applications, start with the three-character MS-DOS file extension for the files created by the server application if unique and applicable (for example, xls for Excel files). If not unique, create a meaningful abbreviation of the application name. Add to the three-character abbreviation a single character for the actual object that serves as the entry point for the application, such as sht for "sheet" in Dim xlssht As Excel.Sheet.

For example, to create a tag for Automation with Shapeware's Visio program, which is an OLE server, use either vsd (the data file extension) or vis (a good mnemonic for Visio) as the basis for the tag, then add app for Application because the entry point to Visio's automation engine is a call to Visio.Application. Thus the tag and its use in variable declarations would look like the following:

Dim ovisapp As Object

Dim ovisdocHouse As Object

Set ovisapp = CreateObject("Visio.Application")

Set ovisdocHouse = ovisapp.Documents.Open("C:\VISIO\HOUSE.VSD")

VBA Coding Conventions

In addition to object naming conventions, LNC proposes several standardized coding conventions for VBA procedures. The use of standardized coding conventions makes your code more readable when your are debugging, doing a code review for yourself or another developer, or documenting an application.

Coding conventions and style issues that are not specifically related to object names are detailed in Chapter 11, "Expert Approaches to VBA."

Code Comments

There are as many in-line VBA code commenting styles as there are Basic coders. Whatever convention you use, the keys are to be terse yet descriptive and be consistent.

LNC suggests placing the following minimum set of comments at the beginning of each procedure:

Comments placed on the same line as code should be separated from the code by two spaces. Comments placed on their own line should be no longer than 60 characters so they are displayed fully in the Access default module design view size.

Trapping Errors

Every procedure that can fail, which is virtually every procedure with more than a few lines, should have an error trap. You can create an error trap by placing the following line at the beginning of the procedure, after the header comments and before any other statements:

On Error GoTo procname_Err

The marker procname should be replaced with the full procedure name. The error handler is placed at the bottom of the procedure, denoted with the label procname_Err. At the end of the error handler, control is returned somewhere in the procedure, usually to a line label name procname_Exit that precedes a block of code immediately above the error handler.

To turn off error trapping during program debugging, LNC suggests that you place the On Error statement inside a conditional compilation directive such as the following:

#If pcccDebug Then

On Error Goto 0

#Else

On Error Goto procname_Err

#Endif

Before running an application, you can enable or disable error trapping by setting the value of pcccDebug to -1 (True) or 0 (False) in the Conditional Compilation Arguments text box on the Module tab of the Tools Options dialog box.

Standardized Abbreviations

Table 6.17 lists some of my standard abbreviations for use in building object names. This is not a comprehensive list and is meant only to give you a starting point for your own taxonomy.

Table 6.17 Standardized Object Name Abbreviations

Abbreviation

Description

Acct

account

Actg

accounting

Addr

address

Admin

administration

Agmt

agreement

Amt

amount

Anal

analysis

Apvd

approved

Arch

archive

Arvl

arrival

Asst

assist(ant)

Atty

attorney

Auth

authorized

Avg

average

Beg

beginning

Bilg

billing

Bldg

building

Busn

business

Char

character

Comm

comment

Cont

contact

Corp

corporate, corporation

Ctrl

control

Ctry

country

Cty

county

Cur

currency

Curr

current

Cust

customer

Dept

department

Desc

description

Det

detail, details

Devlpmt

development

Disc

discount

Dlr

dealer

Empe

employee

Engrg

engineering

Exec

executive

Extd

extend, extended

Extn

extension

Fin

finance, financial

Genl

general

Glbl

global

Int

interest

Intl

international

Inv

inventory

Invc

invoice

Loca

location

Mfg

manufacturing

Mgmt

management

Mgr

manager

Mkt

market

Mktg

marketing

Mon

month

Mtg

meeting

Mtl

material

Mtls

materials

Num

number

Ofc

office

Ofcr

officer

Op

operation

Ops

operations

Ordr

order

Othr

other

Perd

period

Pers

personal, personnel

Phon

phone

Phys

physical

Pmt

payment

Prim

primary

Prnt

print

Proj

project

Pros

prospect, prospective

Qty

quantity

Rec

record

Recd

received

Rem

remark

Schd

schedule, scheduled

Secy

secretary

Seq

sequence

Srce

source

Stat

status

Stats

statistics

Std

standard

Sum

summary, summaries, summation

Super

supervise, supervisor

Svc

service

Titl

title

Tran

transaction

Ttl

total

Var

variable

Ver

version

Whse

warehouse

Whsl

wholesale

Xsfr

transfer

Xsmn

transmission

Xsmt

transmit

Standardized Terminology

When creating code comments, object names, help files, and system documentation, it is important to use terms that have an accepted and nonambiguous meaning. Build a list of standardized terms for your specific industry or application to ensure consistency. Table 6.18 provides a short list of standardized terminology. These terms are not required by LNC; they are only examples.

Table 6.18 Examples of Standardized Terminology

Term

Description

Add

To create a new record. Select Add, Create, Enter, or New, and be consistent.

Beg

The start of a process.

Close

To close an open object.

Comment

A more familiar term for text originating with a human than Remark or Notes.

Desc

A description, often a long text string.

Edit

To change or modify.

Editor

The last person to change a record.

End

The end of a process.

Flag

A programming item with fixed set of values, usually True/False (a Boolean).

Key

An index used to find a record.

Max

The maximum, better than Most.

Min

The minimum, better than Least.

Open

To open an object.

Owner

The creator of a record, process, or object.

Save

To commit a record.

User

The person currently running an application.

From Here...

This chapter details comprehensive standardized naming conventions for Access. Application development examples found throughout the book will use these naming conventions.


1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.