# Creating Maintainable Code - MS Access

The topic of how you should name objects and variables in VBA is one that always attracts controversy amongst developers. There is no enforced rule on how best to do this. In this section, we want to give you some suggestions and reasoned arguments as to why people adopt different naming conventions. The best advice we can give is to find a convention that suits your development style and try to stick to the convention so that your code is consistent.

Naming Access Document Objects

In an Access application, the first objects that need to be named are Form, Report, Table, and Query. Developers tend to name objects by functional area; for example, in the order processing part of a system, you could have forms named such as OrderList, OrderDetails, OrderList_ForExport, and so on, where the Order prefix indicates the order processing function in the system.

The advantage of this naming convention is that the Order forms are all naturally sorted alphabetically together. Other useful conventions with forms are to group them by behavior. For example, some forms are used for data maintenance, and others are pop-ups (the forms used for making selections prior to generating a report). With this convention you could have frmOrders, and dlgOrders, and rptOrders. This is a two-level classification for which the prefix rpt, frm, and dlg indicates the behavior of the form, and then you use the functional area (in this case Orders) for a sub-classification. Another popular convention is to name a form’s subform as frmOrders SubOrder Details, using the word Sub or SubForm as part of the name.

Tables and queries are sometimes displayed in a single combined list in Access; typically when specifying a RecordSource for a form. This has led to a convention of using tbl or qry as a prefix to distinguish between the two types of objects.

Modules can be prefixed with mod. This is useful when you start prefixing the procedures with the module name. This then draws the distinction between code on a form (without a module prefix) and code in a general module (with a prefix).

Many developers adopt camel notation. This is when you capitalize the first letter of each word in the name, such as OrderDetails. Spaces are often avoided, and using the _ (underbar) character is used to break words apart. The table that follows presents some common naming strategies.

Events for controls on a form and the form itself have underbars added by Access; for example, CompanyName_OnClick().

Naming Database Fields

Most developers name database fields with a simple functional name such as Product Description and Product Code, or Product _Description and Produc t_Code. Here again, the camel notation is very popular.

You should avoid using spaces and special characters such as %, and &; names such as R&D Budget or Sales% might look meaningful, but they can be very painful to fix at some later date when you try to publish a database to SharePoint, for example.

Where names have spaces (which is not recommended because it inevitably makes things difficult with some other technologies), you often need to use square brackets to refer to the names in program code; for example, a field called Company Name could be referenced in a Recordset as rst![Company name].

Naming Unbound Controls

When a field is added to a form, unless the field already exists on the form, Access assigns the control a name to match the field name. When naming unbound controls, such as command buttons or calculated expressions, using a standard prefix is important, because once you add code behind the control, if you have not named the control, then it is very difficult to figure out what a control does—say for example, one named Text0. The table that follows lists some common controls and the prefixes that are typically used when naming them.

Other problems that you need to be aware of can occur after you have named a control and attached code. If you change the control name, then existing code becomes orphaned and no longer executes. This is because Access concatenates the control name and an ”_“ (underbar) character with the event name, such as CompanyName_OnClick().

When you copy and paste controls between Access objects, any associated code is not copied. One very attractive feature of embedded macros and controls is that when you copy and paste them between forms the embedded macro code is copied along with the control (except when working with web forms, where paste operations are not supported).

Naming Variables in Code

Some developers prefer to use a convention when naming variables. For example, a string variable for CompanyName might be called strCompanyName, and for a numeric long integer ID, the name might be lngID.

One argument against using this convention is that you might find the code more difficult to read. Another argument is the loss of meaning as the number of different types of objects that you work with increases. In .NET, where you have hundreds of objects, the prefix can become either meaningless or difficult to consistently assign. However, the bulk of VBA Access code manipulates a small number of different types of objects, and much of the naming prefix convention has grown up historically with the product.

The first argument for using a prefix on variable types is that you are less likely to make an error in program code by passing a string variable on a function call that expects a long value parameter. This argument spurs the question, is your development environment clever enough to spot this mistake at runtime? Fortunately, a modern environment such as VBA can spot this mistake at an early stage when compiling the code (even if the error message is sometimes difficult to fully understand), as shown in Figure .

VBA recognizes an attempt to pass the wrong kind of parameter on a function call.

However, the following code, which does not specify a variable type for the function parameters, would pass compilation and not be detected as a potential conflict because the parameter types are not specified in the function definition; they are assumed to be variants, and a string value is a valid variant value:

Function modParam _CalculateSum(lngAddFirst, _ lngAddSecond) As Long modParam _CalculateSum = lng Add First + lng Add Second End Function Sub mod Param _CallCalculate() Dim TheValue As String Dim TheNewValue As Long The NewValue = modParam _Calculate Sum(TheValue, TheValue) End Sub

The preceding code will fail with a type mismatch inside the function when it attempts to add the string and numeric data types together. The second argument for using prefixes is that if, for example, you have a form with a bound control called Company, using a variable called Company will cause confusion, as illustrated in Figure, whereas having a variable called strCompany cannot be confused with the control name.

The compiler recognizes that the name Company would conflict with the control called Company.

However, depending on how the code is written this might not be detected. For example, if you change the order of what is written to that shown in Figure , you end up with code that compiles without detecting the potential error.

Re-ordering the code means this code compiles without detecting the error, and the value printed will be an empty string.

Here are some suggestions for naming the built-in data types. The popular approach is to use a three-character prefix:

For a more formal approach to naming objects (one that is very popular in the VBA community), we recommend searching online for contributions from Greg Reddick or Stan Leszynski

The examples in this book follow a consistent, standard naming convention that is close to the spirit of this convention. In summary, it is possible to write 100% reliable code without having any convention for naming variables, but you need to be careful when doing this. Using a simple convention to prefix a variable with the variable type can save a lot of problems.

Using Me to reference controls in program code

In the example shown in Figure, because we have not referred to the bound control by name, the compiler cannot spot the problem and thinks the value of Company is an empty string. But if we had used Me.Company this would show that the control actually has a value that is not an empty string. On forms and reports, controls on the object can be referred to using the “Me” object. This also has the advantage of invoking IntelliSense when typing control names, and thus reducing typing errors.

Indenting Code

Indenting your program code makes it easier to read and maintain. The VBA Editor allows you to highlight a block of code and then use the Tab key to right-indent the block by a tab stop (position) or the Shift+Tab key to indent back a tab stop. shows a block of code highlighted and indented one tab stop.

Highlighting code and using the Tab or Shift+Tab keys to indent the code forward or backward by one tab position.

Other Variable Naming Conventions

Historically, there was a convention for ending a variable with a special symbol to indicate the data type. For strings, this symbol was the “$” (dollar) character, so Customer$ indicated a string variable name; for Integers, the “%” (percent) symbol was used. We would not advise using this as a convention because it does not appear to be very popular. The Locals window in Figure shows the resulting inferred data types.

The Locals variable window shows how the \$ symbol has been implicitly regarded as a string variable.

One of the oldest conventions that you might come across is using the letters I, J, or K for counters in loops. The roots of this convention go back to languages like Fortran, where these variable names were used for integer values in loops.