An Overview of VBA Programming

3 4

Access forms, reports, and controls have attached events, and you can write code behind forms to perform actions when the events fire—for example, on closing a form, clicking a command button, or formatting a report. Code behind forms is saved in class modules, one for each form or report that has code.

You can also write code in standard modules that can be called from anywhere in the application, and (starting with Access 97) you can write a special type of class module (not attached to a form or report) that defines a new type of object. Figure 20-1 shows the Project pane of the Visual Basic Editor for the Crafts database, which lists form and report class modules and a standard module.

figure 20-1. class and standard modules are listed in the visual basic editor project pane.

Figure 20-1. Class and standard modules are listed in the Visual Basic Editor Project pane.

Getting to the Code

There are several ways to get to VBA code in Access, depending on whether the code belongs to a form or report or is located in a standard or class module. If you’re working on a form or report in Design view, you can open its attached code module by clicking the Code button on the Form Design (or Report Design) toolbar. The Visual Basic Editor opens, displaying the form or report’s code module in the code window.

To go directly to a control’s procedure, select the control on a form or report in Design view, open the control’s properties sheet, and click the Events tab. Click the event that corresponds to the procedure you want to examine or create, and then select [Event Procedure] from the drop-down list. Click the event’s Build button to open the Visual Basic Editor. The code window displays the form or report module, with the insertion point in the event procedure you selected. If no event procedure exists for the event you selected, a procedure stub that you can fill in with code is created.

For command buttons, there’s a shortcut: Right-click the command button, and select Build Event to go directly to the command button’s Click event. (This is the most frequently used event for command buttons.) You can use the same technique with other controls as well, but the default event procedure (probably BeforeUpdate) most likely won’t be the one you want to work with.

There are two ways to open a standard or class module: If you’re in the Access database, select the Modules group, and double-click the module to open it. If you’re in the Visual Basic Editor, open the Modules or Class Modules folder in the Project pane, and double-click the module to open it.

Where Code Resides

Access code is stored in modules, which are collections of declarations, statements, and procedures. Standard modules are stored as separate database objects on the Modules tab in the Database window, and form and report code behind form modules are accessible from their forms or reports or in the Visual Basic Editor window’s Microsoft Access Class Objects folder. Access has several types of modules, as follows:

  • Class module (code behind forms). Contains event procedures for form and report events, form and report section events, and control events. Can also contain functions to be called from event procedures. When you create the first event procedure for a form or report, a class module is created for that form or report.
  • Standard module. Contains functions and subroutines to be called from form or report event procedures and other procedures.
  • Class module (new object type). Creates a definition for a custom object. Procedures define the object’s methods, and public Property Let, Property Get, and Property Set procedures define the object’s properties.

Understanding Functions and Subroutines

Procedures in modules can be either functions or subroutines (commonly abbreviated as subs). Functions and subs can take arguments (constants, variables, or expressions), but only functions can return values.

tip


If you specify arguments when calling a procedure, statement, or method, you don’t need to enclose the arguments within parentheses except when calling functions or methods that return a value.

The following function is from the basGeneral module of the Crafts database. I use this function and the similar FromDate function in many of my databases. It picks up the value of the ToDate field of tblInfo for use wherever it’s needed in the database—for example, to filter a report by order date.

 Public Function ToDate() As Date On Error GoTo ErrorHandler    'Pick up To date from Info table.    Set dbs = CurrentDb    Set rst = dbs.OpenRecordset("tblInfo", dbOpenTable)    With rst       .MoveFirst       ToDate = Nz(![ToDate], "12/31/2002")       .Close    End With ErrorHandlerExit:    Exit Function ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit     End Function 

The following subroutine is also from the basGeneral module of the Crafts database. It formats the txtStateProvince value in all capital letters if the country is U.S.A. It takes three arguments: strStateProvince, strCountry, and txt, which allows it to be called from various event procedures on different forms and controls.

 Public Sub FormatStateProvince(strStateProvince As String, _    strCountry As String, txt As Access.TextBox) On Error GoTo ErrorHandler    If strStateProvince = "" Then       Exit Function    End If    If strCountry = "U.S.A." Then       If Len(strStateProvince) = 2 Then          txt.Value = UCase(strStateProvince)       End If    End If ErrorHandlerExit:    Exit Function ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit End Sub 

The following subroutine is the AfterUpdate event procedure for the txtSalesStateProvince text box control on the frmPublishers form. It calls the preceding FormatStateProvince sub after setting the three variables needed for the function’s arguments.

 Private Sub txtSalesStateProvince_AfterUpdate() On Error GoTo ErrorHandler    strStateProvince = Nz(Me![txtSalesStateProvince].Value)    strCountry = Nz(Me![txtSalesCountry].Value)    Set txt = Me![txtSalesStateProvince]    Call FormatStateProvince(strStateProvince, strCountry, txt)     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit End Sub 

Understanding Variables and Constants

A variable is a placeholder for data that can be modified during the execution of code. Constants are placeholders for data that can’t be changed during the execution of code. Both variables and constants can be declared as specific data types. Although you can declare procedure-level variables or constants anywhere in a procedure, your code will be more readable if you declare them at the beginning of the procedure. Module-level variables and constants are declared in the Declarations section at the top of the module.

Data Typing

It’s good practice to declare all variables as specific data types. Doing so conserves memory resources, helps your code run faster, and makes your code easier to read and debug. You should use the Variant data type only if you don’t know what type of data will be assigned to the variable or if data of several types might be assigned to the same variable. The most commonly used data types for declaring variables in VBA code are listed in Table 20-1, along with their corresponding Access table field data types.

Table 20-1. VBA, DAO, and ADO data types

Access table field data type VBA data type DAO data type ADO data type

N/A

N/A

dbNumeric*

adNumeric

N/A

N/A

dbTime*

adDBDate

N/A

N/A

dbTimeStamp*

adDBTimeStamp

N/A

N/A

dbVarBinary*

adVarBinary

N/A

N/A

dbBigInt*

adBigInt

N/A

N/A

dbBinary

adBinary

Yes/No

Boolean

dbBoolean

adBoolean

N/A

N/A

dbChar*

adChar

Currency

Currency

dbCurrency

adCurrency

Date/Time

Date

dbDate

adDate

N/A

N/A

dbDecimal*

adDecimal

Number (Field Size: Double)

Double

dbDouble

adDouble

AutoNumber (Field Size: Replication ID)

N/A

dbGUID

adGUID

AutoNumber (Field Size: Long Integer

Long

dbLong

adInteger

Number (Field Size: Long Integer)

Long

dbLong

adInteger

OLE Object

String

dbLongBinary

adLongVarBinary

Memo

String

dbMemo

adLongVarWChar

Hyperlink

String

dbMemo

adLongVarWChar

Number (Field Size: Single)

Single

dbSingle

adSingle

Number (Field Size: Integer)

Integer

dbInteger

adSmallInt

Number (Field Size: Byte)

Byte

dbByte

adUnsignedTinyInt

N/A

Variant

N/A

adVariant

Text

String

dbText

adVarWChar

*ODBCDirect only

Early and Late Binding

When you create code that uses an object exposed by another application’s type library, VBA uses a process called binding to determine that the object exists and that your code uses the object’s methods and properties correctly. VBA supports two kinds of binding: late binding and early binding.

Late binding occurs at runtime and is much slower than early binding because VBA must look up an object and its methods and properties each time it executes a line of code that includes the object. Early binding occurs at compile time, so it’s significantly more efficient than late binding.

To take advantage of early binding, you must set a reference to the Automation server’s type library (for example, Microsoft DAO 3.60 Object Library) and you must declare object variables as specific data types (for example, DAO.Recordset).

If you declare a variable as the generic data type Object, late binding occurs. With late binding, an object variable is attached to a specific data type only when the variable is set to an object in the code. Late binding doesn’t require a reference to an object library, and thus it’s useful when your code has to work with objects (such as Microsoft Word documents or Microsoft Excel worksheets) that might be of different versions, or when you’re preparing code that will be imported into other databases, where you don’t know what references have been set.

Apart from these special cases, it’s generally preferable to use early binding.

Scope and Lifetime of Variables and Constants

All variables have scope and lifetime. The scope of a variable determines where you can access that variable in your code. A variable’s scope can vary from global, where any code in your program can access the variable, to local, where the variable is visible to a single procedure. The lifetime of a variable indicates how long the variable exists in code. A variable can exist the entire time your program is running, or it might exist only while a particular procedure is executing. Depending on how and where a variable is declared, there are three scoping levels:

  • Procedure level. Variables or constants declared within a procedure. Available only within that procedure.
  • Private module level. Variables or constants declared Private (or declared without a keyword, which is the same as Private) in the Declarations section of a module. Available only within that module.
  • Public module level. Variables or constants declared Public in the Declarations section of a module. Available from any module in that project.

Table 20-2 describes the scope and availability of procedures, variables, and constants in different types of modules, depending on how they’re declared.

Table 20-2. Scope and lifetime of procedures, variables, and constants

Declaration keywords Declaration level Variable scope and lifetime

Public Sub

Module level

Available to all modules in all projects

Public Function

Module level

Available to all modules in all projects

Public Sub

Procedure level

N/A

Public Function

Procedure level

N/A

Private Sub

Module level

Available only in its own module

Private Function

Module level

Available only in its own module

Dim variable

Private variable

Module level

Available only in its own module; retains its value as long as the module is running

Static variable

Module level

N/A

Public variable

Module level

Available to all procedures in all modules

Dim variable

Procedure level

Available only in its own procedure; retains its value as long as the procedure is running

Public variable

Procedure level

N/A

Static variable

Procedure level

Available only in its own procedure; retains its value as long as the application is running

Const constant

Module level

Available only in its own module

Public Const constant

Module level (standard modules only)

Available to all procedures in all modules

Const constant

Procedure level

Available only in its own procedure

Controlling Data Flow Using VBA Statements

VBA has a number of statements for processing data within procedures, using a variety of logical structures. The most commonly used statements are described in this section.

The Call Statement

The Call statement is used to execute a sub from another procedure. You can omit the Call keyword, but your code will be more comprehensible if you include it. The following subroutine calls another sub to check the validity of a phone number:

note


Functions aren’t called. Instead, you set a variable (or field, or control) equal to the function to save the return value of the function.

 Private Sub txtGeneralPhone_BeforeUpdate(Cancel As Integer) On Error GoTo ErrorHandler    strCountry = Nz(Me![txtSalesCountry].Value)    strPhone = Nz(Me![txtGeneralPhone].Value)    intLength = Len(strPhone)    Call CheckPhone(strCountry, strPhone, intLength)     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit End Sub 

The Do While…Loop Statement

This logical structure repeats a block of statements as long as a condition remains True. When used with the EOF property (indicating the end of a recordset), Do WhileLoop structures are useful for iterating through recordsets, as in the code segment below, which is an extract from a long procedure that takes data from Access tables and writes it to a Word document. (See Chapter 17, "Exporting Data from Access," for the full procedure.)

    Set rst = dbs.OpenRecordset("tmakInvoiceDetails", dbOpenDynaset)    With rst       .MoveFirst       Do While Not .EOF          lngProductID = Nz(![ProductID])          Debug.Print "Product ID: " & lngProductID          strProductName = Nz(![ProductName])          Debug.Print "Product Name: " & strProductName          dblQuantity = Nz(![Quantity])          Debug.Print "Quantity: " & dblQuantity          strUnitPrice = Format(Nz(![UnitPrice]), "$##.00")          Debug.Print "Unit price: " & strUnitPrice          strDiscount = Format(Nz(![Discount]), "0%")          Debug.Print "Discount: " & strDiscount          strExtendedPrice = Format(Nz(![ExtendedPrice]), "$#,###.00")          Debug.Print "Extended price: " & strExtendedPrice                    'Move through the table, writing values from the variables          'to cells in the Word table.          With objWord.Selection             .TypeText Text:=CStr(lngProductID)             .MoveRight Unit:=wdCell             .TypeText Text:=strProductName             .MoveRight Unit:=wdCell             .TypeText Text:=CStr(dblQuantity)             .MoveRight Unit:=wdCell             .TypeText Text:=strUnitPrice             .MoveRight Unit:=wdCell             .TypeText Text:=strDiscount             .MoveRight Unit:=wdCell             .TypeText Text:=strExtendedPrice             .MoveRight Unit:=wdCell          End With          .MoveNext       Loop       .Close    End With 

The Do Until…Loop Statement

The Do Until…Loop logical structure runs a block of statements until a condition becomes True. The following procedure exports data from an Access table to a Microsoft Outlook calendar:

 Private Sub cmdExportDates_Click() On Error GoTo ErrorHandler    Dim dbs As Database    Dim rst As Recordset    Dim appOutlook As New Outlook.Application    Dim nms As Outlook.NameSpace    Dim flds As Outlook.Folders    Dim fld As Outlook.MAPIFolder    Dim itm As Object    Dim lngCount As Long        Set nms = appOutlook.GetNamespace("MAPI")    Set fld = nms.Folders("Personal Folders").Folders("Class Dates")        Set dbs = CurrentDb    Set rst = dbs.OpenRecordset("tblClassDates", dbOpenDynaset)    lngCount = rst.RecordCount    MsgBox lngCount & " records to transfer to Outlook"    'Loop through table, exporting each record to Outlook.    Do Until rst.EOF       Set itm = fld.Items.Add("IPM.Appointment")       itm.Subject = rst!ClassName       itm.Start = rst!ClassDate       itm.Duration = 60       itm.Close (olSave)       rst.MoveNext    Loop ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit End Sub 

The For…Next Statement

The ForNext statement lets you repeat a block of statements a specific number of times. It’s often used with a number derived from counting items, although if the items are members of a collection, you can use the more efficient For EachNext construct instead.

The following code segment sets up a ForNext structure to pick up data from a Word table and store it in an Access table. The code uses a Data Access Objects (DAO) recordset and a variable named lngTableRows, which holds the number of rows in the Word table.

note


You can’t use For Each…Next with rows in a Word table because there is no collection of table rows in the Word object model.

    'Pick up data from table cells and store it in the table.    Set sel = appWord.Selection        For lngRow = 0 To lngTableRows - 2       rst.AddNew       strName = Nz(sel.Text)       Debug.Print "Name: " & strName       If strName = "" Then          GoTo Done       Else          rst![EmployeeName] = strName       End If       sel.MoveRight Unit:=wdCell, Count:=1       strExt = sel.Text       rst![Extension] = strExt       sel.MoveRight Unit:=wdCell, Count:=1       rst.Update    Next lngRow 

The For Each…Next Statement

When you work with members of a collection or elements in an array, you can use the For EachNext construct to iterate through the collection or array, using a variable to stand for a member of the collection. The following code segment uses the For EachNext construct to iterate through the ItemsSelected collection of a list box control on an Access form, picking up information to merge into a Word letter:

    For Each varItem In lst.ItemsSelected       'Check for required address information.       strTest = Nz(lst.Column(5, varItem))       Debug.Print "Street address: " & strTest       If strTest = "" Then          MsgBox "Can’t send letter -- no street address!"          Exit Sub       End If              strTest = Nz(lst.Column(6, varItem))       Debug.Print "City: " & strTest       If strTest = "" Then          MsgBox "Can’t send letter -- no city!"          Exit Sub       End If              strTest = Nz(lst.Column(8, varItem))       Debug.Print "Postal code: " & strTest       If strTest = "" Then          MsgBox "Can’t send letter -- no postal code!"          Exit Sub       End If           strName = Nz(lst.Column(2, varItem)) & _          " " & Nz(lst.Column(3, varItem))       strJobTitle = Nz(lst.Column(11, varItem))       If strJobTitle <> "" Then          strName = strName & vbCrLf & strJobTitle       End If       strAddress = Nz(lst.Column(5, varItem)) & vbCrLf & _          Nz(lst.Column(6, varItem)) & ", " & _          Nz(lst.Column(7, varItem)) & _          "  " & Nz(lst.Column(8, varItem))       Debug.Print "Address: " & strAddress       strCountry = Nz(lst.Column(9, varItem))       If strCountry <> "USA" Then          strAddress = strAddress & vbCrLf & strCountry       End If              'Open a new letter based on the selected template.       appWord.Documents.Add strWordTemplate              'Write information to Word custom document properties.       Set prps = appWord.ActiveDocument.CustomDocumentProperties       prps.Item("Name").Value = strName       On Error Resume Next      prps.Item("Salutation").Value = Nz(lst.Column(4, varItem))       prps.Item("CompanyName").Value = Nz(lst.Column(10, varItem))       prps.Item("Address").Value = strAddress       prps.Item("TodayDate").Value = strLongDate              'Check for existence of previously saved letter in documents folder,       'and append an incremented number to save name if found.       strDocType = appWord.ActiveDocument.BuiltInDocumentProperties(2)       strSaveName = strDocType & " to " & _          lst.Column(2, varItem) & " " & lst.Column(3, varItem)       strSaveName = strSaveName & " on " & strShortDate & ".doc"       i = 2       intSaveNameFail = True       Do While intSaveNameFail          strSaveNamePath = strDocsPath & strSaveName          Debug.Print "Proposed save name and path: " _             & vbCrLf & strSaveNamePath          strTestFile = Nz(Dir(strSaveNamePath))          Debug.Print "Test file: " & strTestFile          If strTestFile = strSaveName Then             Debug.Print "Save name already used: " & strSaveName                          'Create new save name with incremented number.             intSaveNameFail = True             strSaveName = strDocType & " " & CStr(i) & " to " & _                lst.Column(2, varItem) & " " & lst.Column(3, varItem)             strSaveName = strSaveName & " on " & strShortDate & ".doc"             strSaveNamePath = strDocsPath & strSaveName             Debug.Print "New save name and path: " _                & vbCrLf & strSaveNamePath             i = i + 1          Else             Debug.Print "Save name not used: " & strSaveName             intSaveNameFail = False          End If       Loop              'Update fields in Word document and activate document.       With appWord          .Selection.WholeStory          .Selection.Fields.Update          .Selection.HomeKey Unit:=6          .ActiveDocument.SaveAs strSaveName       End With    Next varItem 

The GoTo Statement

The GoTo statement lets you jump directly to a named label in your code. It’s typically used in error handlers. For example, when you use the following line at the beginning of a procedure, the code jumps to the ErrorHandler label when an error occurs:

 On Error GoTo ErrorHandler 

The If…Then…Else Statement

An If…Then…Else statement lets you run a block of statements if a condition is met, and (optionally) another block of statements if the condition isn’t met. You can also add one or more ElseIf statements to an If…Then…Else statement to run a second (or further) condition test(s) if the first condition isn’t met. The following code segment sets a strAddress variable to business address components (from an Outlook contact item) if the intAddressType variable is equal to 1 and to home address components if the variable is equal to 2:

 If intAddressType = 1 Then    strAddress = IIf(![Company] <> strEmpty, ![Company] & vbCrLf, _    ![Company]) & IIf(![BusinessStreet1] <> strEmpty, _    ![BusinessStreet1] & vbCrLf, ![BusinessStreet1]) & _    IIf(![BusinessStreet2] <> strEmpty, ![BusinessStreet2] & _    vbCrLf, ![BusinessStreet2]) & IIf(![BusinessCity] <> strEmpty, _    ![BusinessCity] & Chr$(44) & Chr$(32), ![BusinessCity]) & _    IIf(![BusinessState] <> strEmpty, ![BusinessState] & Chr$(32) _    & Chr$(32), ![BusinessState]) & ![BusinessPostalCode] ElseIf intAddressType = 2 Then    strAddress = IIf(![HomeStreet1] <> strEmpty, ![HomeStreet1] _    & vbCrLf, ![HomeStreet1]) & IIf(![HomeStreet2] <> strEmpty, _    ![HomeStreet2] & vbCrLf, ![HomeStreet2]) & IIf(![HomeCity] _    <> strEmpty, ![HomeCity] & Chr$(44) & Chr$(32), ![HomeCity]) & _    IIf(![HomeState] <> strEmpty, ![HomeState] & Chr$(32) & Chr$(32), _    ![HomeState]) & ![HomePostalCode] End If 

The Select Case Statement

The Select Case statement is used to run different blocks of code when an expression might have several different values (more than can conveniently be handled with an If…Then…Else statement). As a rule of thumb, if there are more than three possible values, it’s best to use a Select Case statement.

The following function, from the Menu Manager add-in, uses a Select Case statement to deal with the possible values of an option group on an Access form. (The option group is used to select a background picture for a menu form.)

You can find more information about the Menu Manager add-in, which uses this function, in Chapter 15, "Using Add-Ins to Expand Access Functionality."

 Public Function ChangePicture() As String On Error GoTo ChangePictureError    Dim intPicture As Integer    Dim strPicture As String    Dim ctlPicture As Control    Set frm = Screen.ActiveForm    intPicture = frm![fraPicture]        Set ctlPicture = frm![imgBackground]        Select Case intPicture           Case 1          strPicture = "imgBooks"                 Case 2          strPicture = "imgContacts"              Case 3          strPicture = "imgMusic"              Case 4          strPicture = "imgFood"              Case 5          strPicture = "imgHoushold"                 Case 6          strPicture = "imgInventory"                 Case 7          strPicture = "imgMembers"                 Case 8          strPicture = "imgMoney"              Case 9          strPicture = "imgPhoneOrders"              Case 10          strPicture = "imgPhotos"              Case 11          strPicture = "imgResources"              Case 12          strPicture = "imgSchool"              Case 13          strPicture = "imgVideos"              Case 14          strPicture = "imgWorkout"              End Select        ctlPicture.Picture = frm.Controls(strPicture).Picture    ChangePicture = frm.Controls(strPicture).Picture ChangePictureExit:    Exit Function ChangePictureError:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ChangePictureExit     End Function 

The While…Wend Statement

This statement executes a series of statements while a given condition is True. It’s rarely used because the Do…Loop statement offers more flexibility.

The With Statement

The With statement (introduced in Access 97) lets you perform a number of operations on an object without repeating the object’s name or variable. The following code segment, from an Access form, sets the Visible property of a combo box control and calls two of the combo box control’s methods:

    With Me![cboAuthorSearchList]       .Visible = True       .SetFocus       .Dropdown    End With 

Naming Conventions

Many Access versions ago, two Access developers (Stan Leszynski and Greg Reddick) developed a naming convention for Access objects, based on Charles Simonyi’s Hungarian notation for naming objects. The Leszynski-Reddick Naming Convention (L/R) was proposed as a standard to make it easier for developers to understand their own code (especially when returning to it after a significant amount of time had passed), and to understand databases and code developed by others. L/R was introduced in the charter issue of Smart Access, in February 1993, and was widely adopted by Access developers and used through Access 95. Since then this naming convention has split into two branches, the Leszynski Naming Convention (LNC) and the Reddick VBA Naming Convention (RVBA), although there’s considerable overlap between the two.

Why use a naming convention? Basically, using a naming convention for database objects and controls makes your database self-documenting. Every time you see a drop-down list of database objects, each item’s tag will tell you what kind of object it is. Likewise, when you see a reference to an object, control, or field in VBA code, you’ll know what kind of element it is—essential information for understanding what you can do with it.

You can call a sub, set a control’s value, or assign a variable as a function argument. But if you try to call a control, assign a form name as an Integer argument of a function, or reference the Height property of variable, you’ll get compile errors or runtime errors in your code. Consistent use of a naming convention prevents these errors, because you’ll know from the tag that frmSales is a form, intChildren is an Integer variable, and CalcTotals (since it has no tag) is a procedure name.

Hungarian notation (named after the native country of its originator) uses the following schema for naming objects:

[prefixes][tag]BaseName [Suffix/Qualifier]

Table 20-3 describes each of the schema’s components.

Table 20-3. Hungarian notation components

Component Description Example

Prefix(es)

A lowercase letter that adds more information to the tag.

p for Public variable

Tag

A three-letter or four-letter sequence that indicates the object type.

frm for Form

BaseName

One or more words describing what the object represents. Each word in the name begins with a capital letter.

EmployeesBySSN for a selection of employees sorted by Social Security number

Suffix (RVBA),

Qualifier (LNC)

A word that gives more information about the BaseName. Its initial letter is capitalized.

Max for the last element in an array

Of these four components, only the tag is required, although most object names consist of a tag and a base name. A stand-alone tag is useful when you’re declaring variables in VBA code—for example, frm as a variable for a form. You don’t have to use all four naming components—using tags alone gives you most of the benefits of a naming convention.

note


This book uses the LNC, mainly because it’s similar to the original L/R naming convention, which I’ve used since the earliest versions of Access. Both the LNC and RVBA have branched out from Access objects and Access Basic (later, Access VBA) to cover other VBA dialects as well, and thus they encompass much more than the original Access components. Complete coverage of these naming conventions is beyond the scope of this book, but you can obtain the latest version of the LNC from http://www.kwery.com and the latest version of the RVBA convention from http://www.xoc.net.

Applying a Naming Convention to a New Database

To apply the LNC to a new database, all you have to do is name each database object, control, variable, and (if desired) field you create using the appropriate tag and base name. (See Tables 20-4 through 20-10, beginning, for listings of the LNC tags.)

Although it might seem cumbersome at first, the LNC quickly repays the user’s effort in implementing it. When all your database objects have been named with the suggested three-letter or four-letter tags, you can tell what kind of object you’re looking at whenever you see a list of database objects. Although Access (since Access 2000) generally offers separate tabs for tables and queries in record source drop-down lists or, in some cases, prefaces table names with Table and query names with Query, all this tells you is whether an object is a table or a query. LNC naming tags give you much more information, allowing you to pinpoint just the relevant objects (for example, select queries or subforms) for selection.

In a database that uses a naming convention, when you create an expression referencing a field or a form control in VBA code or for a query criterion, the object name’s tag indicates whether you’re referencing a control on a form or the underlying field in the table. Contrast this with a form created by the Form Wizard or a report created by the Report Wizard—Access unhelpfully assigns controls on the form the same names as their fields, which can lead to circular expression errors.

If you use a naming convention, there won’t be any confusion between controls and fields, and you can tell at a glance whether a table is a lookup table, a system table, or alinking table or whether a query is an append query, a make-table query, or some other query type.

When you look at your code six months later, it will be considerably more comprehensible—not only to you, but to other developers. And if you work as part of a development team, if all the developers on the team use the same naming convention, you’ll all be able to understand each other’s code much better than you would otherwise.

You can (and no doubt most users do) add your own extensions to the standard naming convention. I have added the tmp tag to indicate a form or report template, the fpri tag for the primary form in a database, the qtot tag for a Totals query, and the tmak tag for a table made by a make-table query, to remind me that if I want to change the table’s structure, I have to change the matching qmak query, not the table itself.

Although the LNC in its entirety provides tags for many more objects that developers will probably work with in VBA code, some of these tags (for example, type structures) are of little interest to the average user. All Access users should at least use tags for database objects, controls, and variables; advanced developers will probably want to use tags for other objects, and prefixes and qualifiers as well.

The following tables of tag names follow the LNC, with a few additions of my own. Table 20-4 lists the LNC tags for database objects; Table 20-5 lists field tags; Table 20-6 lists variable tags; Table 20-7 lists control tags; Table 20-8 lists object prefixes; Table 20-9 lists variable scope and lifetime prefixes; and Table 20-10 lists qualifiers.

note


The tags and other identifiers in these tables are only a subset of the entire LNC. Fo the full set of identifiers, see the "LNC for Access" white paper available at http://www.kwery.com.

Table 20-4. LNC database object tags

Object Tag

Class module

cls

Data access page

dap

Form

frm

Form (dialog)

fdlg

Form (menu)

fmnu

Form (message)

fmsg

Form (subform)

fsub

Macro

mcr

Module

bas

Query (any type)

qry

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)

qry (or qsel)

Query (SQL pass-through)

qspt

Query (totals)

qtot

Query (union)

quni

Query (update)

qupd

Report

rpt

Report (subreport)

rsub

Table

tbl

Table (attached dBASE)

tdbf

Table (attached Excel)

txls

Table (attached FoxPro)

tfox

Table (attached Lotus)

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 relationship)

trel

Table 20-5. LNC table field tags

Object 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

Table 20-6. LNC VBA variable tags

Object Tag

Combo box

cbo

Command bar

cbr

Control (generic)

ctl

Currency

cur

Database

dbs

Double

dbl

Form

frm

Integer

int

Label

lbl

List box

lst

Long

lng

QueryDef

qdf

Report

rpt

Single

sng

Snapshot

snp

String

str

Table

tbl

Text box

txt

Type (user-defined)

typ

Variant

var

Table 20-7. LNC control tags

Object Tag

Bound object frame

frb

Chart (graph)

cht

Check box

chk

Combo box

cbo

Command button

cmd

Custom control (ActiveX control)

ocx

Frame

fra

Hyperlink

hlk

Image

img

Label

lbl

Line

lin

List box

lst

Option button

opt

Option group

grp

Page (tab)

pge

Page break

brk

Rectangle (shape)

shp

Subform/report

sub

Text box

txt

Toggle button

tgl

Unbound object frame

fru

Table 20-8. LNC database object prefixes

Object Tag

Objects that are incomplete, backup, or under development

_ or – (In Access 2000 and later, dashes sort to the beginning of the database object list.)

Hidden system objects

zh

Displayed system objects

zs

Programmatically created temporary objects

zt

Backup copies of objects, for later copying or reuse

zz

Table 20-9. LNC scope and lifetime prefixes

Variable type Tag

Local variable

[no prefix]

Local static variable

s

Module-level variable

m

Public variable in a form or report module

p

Public variable declared in the Declarations section of a standard module

g

Table 20-10. LNC qualifiers

Qualifier Description

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

New

New instance or value

Next

Next element of a set

Old

Prior instance or value

Prev

Previous element of a set

Src

Source

Temp

Temporary value

Table 20-11 lists some sample object and variable names, using prefixes, tags, and qualifiers.

Table 20-11. LNC examples

Object or variable name Object or variable naming components Object or variable description

tblCustomers

tag + base name

A table of customer data

qupdSales

tag + base name

A query that updates sales data

fsubInvoiceTotals

tag + base name

A subform that shows invoice totals data

tblCustomersOld

tag + base name + qualifier

A table of old Customers data

intChildren

tag + base name

An Integer variable to hold a value representing the number of children

curSalary

tag + base name

A Currency variable holding a Salary value

gcurBaseSalary

prefix + tag + base name

A global Currency variable holding a Salary value

zztblContracts

prefix + tag + base name

A backup Contracts table, for copying and filling with imported data

Applying a Naming Convention to an Existing Database

It’s easy to use a naming convention in a new database. It takes just a few seconds to type a three-letter or four-letter tag when you’re naming a database object, control, or variable, and the small amount of time you spend will be saved many times over as you proceed to work on the database. However, if you inherit a database that’s been created with no naming convention—or, for that matter, if you create database objects using wizards—you have a more burdensome task: applying a naming convention to an existing database.

In short, the problem is that renaming an object (say, a table) doesn’t cause the name change to ripple throughout the database, changing Customers to tblCustomers wherever the table is referenced—as a form, report, or query data source; a combo box or list box row source; or in VBA code. You have to track down the changes yourself, renaming all the references to match the new object names, to avoid errors whenever the object whose name you changed is referenced.

I was inspired to write my first add-in, the LNC Rename add-in, after struggling with an inherited database in which the developer had named objects of many different types Sales. I had no way of knowing when I encountered Sales in the code whether it was a function name, a Currency variable, or a table, form, query, or report. If the variable had been named curSales, the table tblSales, the form frmSales, the query qrySales, and the function just Sales, there would have been no confusion.

Renaming database objects requires renaming references to them in form and report record sources, combo box control and list box control row sources, report grouping expressions, query fields and expressions, and VBA code—a very time-consumingprocess. Using a naming convention right from the start eliminates the need to rename objects later on.

Since Access 2000, Access has had a limited ability to propagate name changes: If you select the Name AutoCorrect check boxes on the General tab of the Options dialog box (see Figure 20-2), Access will fix some name references automatically when a form, report, or other object is opened.

figure 20-2. select the name autocorrect check boxes on the general tab in the options dialog box.

Figure 20-2. Select the Name AutoCorrect check boxes on the General tab in the Options dialog box.

For example, if you rename the Orders form in the sample Northwind database frmOrders and rename the Orders subform fsubOrders (in accordance with the LNC), when you next open frmOrders, it’ll display the renamed subform correctly, and if you switch to Design view, you’ll see that the subform is displaying fsubOrders. However, recognizing a renamed subform or subreport is only the tip of the iceberg. A search for Orders Subform in the Northwind database’s VBA code reveals numerous references to the subform’s original name, none of which have been changed, and all of which will cause errors when these procedures are run.

Name AutoCorrect doesn’t work for references in the following elements:

  • VBA code (modules)
  • Projects
  • Replicated databases
  • Data access pages
  • Macros
  • Desktop shortcuts for specific database objects
  • Linked tables

InsideOut

With so many exceptions, the Name AutoCorrect feature isn’t always as useful as it could be. When you change a database object, control, field, or variable name, you need to have it changed throughout the database, wherever it occurs. Long before the Name AutoCorrect feature was added to Access, I wrote the LNC Rename add-in, which automatically renames database objects and controls according the LNC.

(Variables can easily be renamed using search and replace in modules, so I didn’t include them.) This add-in is described in detail in Chapter 15, "Using Add-Ins to Expand Access Functionality."

The LNC Rename Access add-in (available on the companion CD) ensures that when you rename database objects and controls using the LNC, all of the references to the renamed objects will be changed as well. I also prepared an LNC Rename COM add-in (also on the companion CD) for quick renaming of form and report controls only.



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net