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.
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.
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:
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
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
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.
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.
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:
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 |
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 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
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
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 While …Loop 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 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 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 Each …Next construct instead.
The following code segment sets up a For …Next 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
'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
When you work with members of a collection or elements in an array, you can use the For Each …Next 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 Each …Next 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 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
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 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
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 (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
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
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
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 |
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.
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:
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.