Introduction to VBA: Part 3

This article is part 3 of a 3-part series:


This article, Introduction to VBA: Part 3 covers the more advanced topics not included in the previous articles including a discussion of the DoCmd object, built-in functions, practical applications, and more.

The DoCmd Object: Performing Macro Actions

The Access environment is rich with objects that have built-in properties and methods. By using VBA code, you can modify the properties and execute the methods. One of the objects available in Access is the DoCmd object, used to execute macro actions in Visual Basic procedures. The macro actions are executed as methods of the DoCmd object. The syntax looks like this:

DoCmd.ActionName [arguments]
                      

Select all Open in new window

Here’s a practical example:

DoCmd.OpenReport strReportName, acViewPreview
                      

Select all Open in new window

The OpenReport method is a method of the DoCmd object; it runs a report. The first two parameters that the OpenReport method receives are the name of the report you want to run and the view in which you want the report to appear (Preview, Normal, or Design). The name of the report and the view are both arguments of the OpenReport method.

Most macro actions have corresponding DoCmd methods that you can find in Help, but some don’t. They are AddMenu, MsgBox, RunApp, RunCode, SendKeys, SetValue, StopAllMacros, and StopMacro. The SendKeys method is the only one that has any significance to you as a VBA programmer. The remaining macro actions either have no application to VBA code, or you can perform them more efficiently by using VBA functions and commands. The VBA language includes a MsgBox function, for example, that’s far more robust than its macro action counterpart.

Many of the DoCmd methods have optional parameters. If you don’t supply an argument, its default value is assumed. You can use commas as place markers to designate the position of missing arguments, as shown here:

DoCmd.OpenForm "frmOrders", , ,"[OrderAmount] > 1000"
                      

Select all Open in new window

The OpenForm method of the DoCmd object receives seven parameters; the last six parameters are optional. In the example, two parameters are explicitly specified. The first is the name of the form ("FrmOrders"), a required parameter. The second and third parameters have been omitted, meaning that you’re accepting their default values. The commas, used as place markers for the second and third parameters, are necessary because one of the parameters following them is explicitly designated. The fourth parameter is the Where condition for the form, which has been designated as the record in which the OrderAmount is greater than 1,000. The remaining parameters haven’t been referred to, so default values are used for these parameters.

If you prefer, you can use named parameters to designate the parameters that you are passing. Named parameters, covered later in this article, can greatly simplify the preceding syntax. With named parameters, you don’t need to place the arguments in a particular order, and you don’t need to worry about counting commas. The preceding syntax can be changed to the following:

DoCmd.OpenForm FormName:="frmOrders",  _
                                 WhereCondition:="[OrderAmount] > 1000"
                      

Select all Open in new window

Working with Built-In Functions

VBA has a rich and comprehensive function library as well as tools to assist in their use.

Built-In Functions

Some of the more commonly used VBA functions and examples are listed in the following sections. On some rainy day, go through the online Help to become familiar with the rest.

Note

The following examples are located in basBuiltIn in the Chap8Ex database.

Format

The Format function formats expressions in the style specified. The first parameter is the expression you want to format; the second is the type of format you want to apply. Here’s an example of using the Format function:

Sub FormatData()
                         Debug.Print Format(50, "Currency")
                         'Prints $50.00
                         Debug.Print Format(Now, "Short Date")
                         'Prints the current date
                         Debug.Print Format(Now, "DDDD")
                         'Displays the word for the day
                         Debug.Print Format(Now, "DDD")
                         'Displays 3 - CHAR Day
                         Debug.Print Format(Now, "YYYY")
                         'Displays 4 - digit Year
                         Debug.Print Format(Now, "WW")
                         'Displays the Week Number
                      End Sub
                      

Select all Open in new window

Instr

The Instr function returns the position where one string begins within another string:

Sub InstrExample()
                        Debug.Print InStr("Alison Balter", "Balter") 'Returns 8
                        Debug.Print InStr("Hello", "l") 'Returns 3
                        Debug.Print InStr("c:\my documents\my file.txt", "\") 'Returns 3
                      End Sub
                      

Select all Open in new window

InStrRev

InStrRev begins searching at the end of a string and returns the position where one string is found within another string:

Sub InstrRevExample()
                          Debug.Print InStrRev("c:\my documents\my file.txt", "\") 'Returns 16
                      End Sub
                      

Select all Open in new window

Notice that the InStr function returns 3 as the starting position for the backslash character within "c:\my documents\my file.txt", whereas the InStrRev function returns 16 as the starting position for the backslash character in the same string. The reason is that InStr starts searching at the beginning of the string, continuing until it finds a match, whereas InStrRev begins searching at the end of the string, continuing until it finds a match.

Left

Left returns the left-most number of characters in a string:

Sub LeftExample()
                        Debug.Print Left("Hello World", 7) 'Prints Hello W
                      End Sub
                      

Select all Open in new window

Right

Right returns the right-most number of characters in a string:

Sub RightExample()
                       Debug.Print Right("Hello World", 7) 'Prints o World
                      End Sub
                      

Select all Open in new window

Mid

Mid returns a substring of a specified number of characters in a string. This example starts at the fourth character and returns five characters:

Sub MidExample()
                          Debug.Print Mid("Hello World", 4, 5) 'Prints lo Wo
                      End Sub
                      

Select all Open in new window

UCase

UCase returns a string that is all uppercase:

Sub UCaseExample()
                          Debug.Print UCase("Hello World") 'Prints HELLO WORLD
                      End Sub
                      

Select all Open in new window

DatePart

DatePart returns the specified part of a date:

Sub DatePartExample()
                          Debug.Print DatePart("YYYY", Now)
                          'Prints the Year
                          Debug.Print DatePart("M", Now)
                          'Prints the Month Number
                          Debug.Print DatePart("Q", Now)
                          'Prints the Quarter Number
                          Debug.Print DatePart("Y", Now)
                          'Prints the Day of the Year
                          Debug.Print DatePart("WW", Now)
                          'Prints the Week of the Year
                      End Sub
                      

Select all Open in new window

DateDiff

DateDiff returns the interval of time between two dates:

Sub DateDiffExample()
                        Debug.Print DateDiff("d", Now, "12/31/2010")
                        ''Days until 12/31/2010
                        Debug.Print DateDiff("m", Now,"12/31/2010")
                        ''Months until 12/31/2010
                        Debug.Print DateDiff("yyyy", Now, "12/31/2010")
                        ''Years until 12/31/2010
                        Debug.Print DateDiff("q", Now, "12/31/2010")
                        ''Quarters until 12/31/2010
                      End Sub
                      

Select all Open in new window

DateAdd

DateAdd returns the result of adding or subtracting a specified period of time to a date:

Sub DateAddExample()
                          Debug.Print DateAdd("d", 3, Now)
                          'Today plus 3 days
                          Debug.Print DateAdd("m", 3, Now)
                          'Today plus 3 months
                          Debug.Print DateAdd("yyyy", 3, Now)
                          'Today plus 3 years
                          Debug.Print DateAdd("q", 3, Now)
                          'Today plus 3 quarters
                          Debug.Print DateAdd("ww", 3, Now)
                          'Today plus 3 weeks
                          Debug.Print DateAdd("ww", -3, Now)
                          'Today minus 3 weeks
                      End Sub
                      

Select all Open in new window

Replace

Replace replaces one string with another:

Sub ReplaceExample()
                          Debug.Print Replace("Say Hello if you want to", "hello", "bye")
                          'Returns Say bye if you want to
                          Debug.Print Replace("This gets rid of all of the spaces", " " , "")
                          'Returns Thisgetsridofallofthespaces
                      End Sub
                      

Select all Open in new window

StrRev

StrRev reverses the order of text in a string:

Sub StrReverseExample()
                          Debug.Print StrReverse("This string looks very funny when reversed!")
                          'Returns !desrever nehw ynnuf yrev skool gnirts sihT
                      End Sub
                      

Select all Open in new window

MonthName

MonthName returns the text string associated with a month number:

Sub MonthNameExample()
                          Debug.Print MonthName(7)
                          'Returns July
                          Debug.Print MonthName(11)
                          'Returns November
                      End Sub
                      

Select all Open in new window

Functions Made Easy with the Object Browser

With the Object Browser, you can view members of an ActiveX component’s type library. In plain English, the Object Browser enables you to easily browse through a component’s methods, properties, and constants. You can also copy information and add it to your code. It even adds a method’s parameters for you. The following steps let you browse among the available methods, copy the method you want, and paste it into your code:
1.     With the VBE active, select View, Object Browser from the menu (note that the menu line also shows an icon that you can use from the toolbar), or press F2 to open the Object Browser window (see Figure 1).


2.      The Object Browser window is divided into two parts: the upper part of the window and the lower part. The drop-down list at the upper left of the window is used to filter the items to be displayed in the lower part of the window. Use this drop-down list to select the project or library whose classes and members you want to view in the lower part of the window.
3.      In the lower portion of the window, select the class from the left list box, which lists Class modules, templates for new objects, standard modules, and modules containing subroutines and functions.
4.      Select a related property, method, event, constant, function, or statement from the Members Of list box. In Figure 2, the basUtils module is selected from the list box on the left. Notice that the subroutines and functions included in basUtils appear in the list box on the right.
5.    Click the Copy to Clipboard button (third from the right in the upper toolbar within the Object Browser window) to copy the function name and its parameters to the Clipboard so that you can easily paste it into your code.

The example in Figure 2 shows choosing a user-defined function selected from a module in a database, but you can also select any built-in function. Figure 3 shows an example in which the DatePart function is selected from the VBA library.

Working with Constants

A constant is a meaningful name given to a meaningless number or string. Constants can be used only for values that don’t change at runtime. A tax rate or commission rate, for example, might be constant throughout your application. There are three types of constants in Access:


Symbolic constants, created by using the Const keyword, are used to improve the readability of your code and make code maintenance easier. Instead of referring to the number .0875 every time you want to refer to the tax rate, you can refer to the constant mccurTaxRate. If the tax rate changes, and you need to modify the value in your code, you’ll make the change in only one place. Furthermore, unlike the number .0875, the name mccurTaxRate is self-documenting.

Intrinsic constants are built into Microsoft Access; they are part of the language itself. As an Access programmer, you can use constants supplied by Microsoft Access, Visual Basic, Data Access Objects (DAO), and ADO. You can also use constants provided by any object libraries you’re using in your application.

There are only three system-defined constants - True, False, and Null  - and they are available to all applications on your computer.

Working with Symbolic Constants

As mentioned, you declare a symbolic constant by using the Const keyword. You can declare a constant in a subroutine or function, or in the General section of a Form or Report module. You can strong-type constants. The declaration and use of a private constant looks like this:

Private Const TAXRATE As Currency = .0875
                      

Select all Open in new window

This code, when placed in a module’s Declarations section, creates a private constant called TAXRATE and sets it equal to .0875. Here’s how you use the constant in code:

Function TotalAmount(curSaleAmount As Currency)
                         TotalAmount = curSaleAmount * TAXRATE
                      End Function
                      

Select all Open in new window

This routine multiplies the curSaleAmount, received as a parameter, by the constant TAXRATE. It returns the result of the calculation by setting the function name equal to the product of the two values. The advantage of the constant in this example is that the code is more readable than TotalAmount = curSaleAmount * .0875 would be.

Scoping Symbolic Constants

Just as regular variables have scope, user-defined constants have scope. In the preceding example, you created a private constant. The following statement, when placed in a module’s General Declarations section, creates a public constant:

Public Const TAXRATE As Currency = .0875
                      

Select all Open in new window

Because this constant is declared as public, you can access it from any subroutine or function (including event routines) in your entire application. To better understand the benefits of a public constant, consider a case in which you have many functions and subroutines all making reference to the constant TAXRATE. Imagine what would happen if the tax rate were to change. If you hadn’t used a constant, you would need to search your entire application, replacing the old tax rate with the new tax rate. However, because your public constant is declared in one place, you can easily go in and modify the one line of code where this constant is declared.

By definition, the values of constants cannot be modified at runtime. If you try to modify the value of a constant, you get this VBA compiler error:

Assignment to constant not permitted
                      

Select all Open in new window

Figure 4 illustrates this message box. You can see that an attempt is made to modify the value of the constant TAXRATE, which results in a compile error.


If you need to change the value at runtime, you should consider storing the value in a table instead of declaring it as a constant. You can read the value into a variable when the application loads, and then modify the variable if needed. If you choose, you can write the new value back to the table.

Working with Intrinsic Constants

Microsoft Access declares a number of intrinsic constants that you can use in Code, Form, and Report modules. Because they’re reserved by Microsoft Access, you can’t modify their values or reuse their names; however, you can use them at any time without declaring them.

You should use intrinsic constants whenever possible in your code. Besides making your code more readable, they make your code more portable to future releases of Microsoft Access. Microsoft might change the value associated with a constant, but Microsoft isn’t likely to change the constant’s name. All intrinsic constants appear in the Object Browser; to activate it, simply click the Object Browser tool on the Visual Basic toolbar. To view the constants that are part of the Access library, select Access from the Object Browser’s Project/Library drop-down list. Click Constants in the Classes list box, and a list of those constants is displayed in the Members Of ‘Constants’ list box (see Figure 5).


In the list shown in Figure 5 all VBA constants are prefixed with vb, all Data Access Object constants are prefixed with db, and all constants that are part of the Access language are prefixed with ac. To view the Visual Basic language constants, select VBA from the Project/Library drop-down list and Constants from the Classes list box. If the project you are working with has a reference to the ADO library, you can view these constants by selecting ADODB from the Project/Library drop-down list. Click <globals>. A list of the ADODB constants appears (these constants have the prefix ad).

Another way to view constants is within the context of the parameter you’re working with in the Code window. Right-click the name of a parameter and select List Constants to display the constants associated with the parameter.

Working in With the Visual Basic Editor Tools

Effectively using the tips and tricks of the trade, many of which are highlighted in this article, can save you hours of time. These tricks help you to navigate around the coding environment, as well as to modify your code quickly and easily. They include the capability to easily zoom to a user-defined procedure, search and replace within modules, get help on VBA functions and commands, and split the Code window so that two procedures can be viewed simultaneously.

Access 2010 offers a very rich development environment. It includes several features that make coding easier and more pleasant for you. These features include the capability to do the following:
                                    
All these features that help you with coding are available with a right-click when you place your cursor within the Module window.

List Properties and Methods

With the List Properties and Methods feature, you can view all the objects, properties, and methods available for the current object. To invoke this feature, right-click after the name of the object and select List Properties, Methods (you can also press Ctrl+J). The applicable objects, properties, and methods appear in a list box (see Figure 6). To find the appropriate object, property, or method in the list, use one of these methods:

                  
Use one of these methods to insert your selection:
            

Tip

The Auto List Members option, available on the Editor tab of the Options dialog box, causes the List Properties and Methods feature, as well as the List Constants feature, to be invoked automatically each time you type the name of an object or property.

List Constants

The List Constants feature, which is part of IntelliSense, opens a drop-down list displaying valid constants for a property you have typed and for functions with arguments that are constants. It works in a similar manner to the List Properties and Methods feature. To invoke it, right-click after the name of the property or argument (in cases in which multiple arguments are available, the previous argument must be delimited with a comma) and select List Constants (or press Ctrl+Shift+J). A list of valid constants appears (see Figure 7). You can use any of the methods listed in the preceding section to select the constant you want.

Quick Info

The Quick Info feature gives you the full syntax for a function, statement, procedure, method, or variable. To use this feature, right-click after the name of the function, statement, procedure, method, or variable, and then select Quick Info (or press Ctrl+I). A tip appears, showing the valid syntax for the item (see Figure 8). As you type each parameter in the item, it’s displayed in boldface type until you type the comma that delineates it from the next parameter.


Tip

The Auto Quick Info option, available in the Options dialog box, causes the Quick Info feature to be invoked automatically each time you type the name of an object or property.

Parameter Info

The Parameter Info feature gives you information about the parameters of a function, statement, or method. To use this feature, after the delimiter that denotes the end of the function, statement, or method name, right-click and select Parameter Info (or press Ctrl+Shift+I). A pop-up list appears with information about the parameters of the function or statement. This list doesn’t close until you enter all the required parameters, you complete the function without any optional parameters, or you press the Esc key.

Note
The Parameter Info feature supplies information about the initial function only. If parameters of a function are themselves functions, you must use Quick Info to find information about the embedded functions.

Complete Word

The Complete Word feature completes a word you’re typing. To use this feature, you must first type enough characters for Visual Basic to recognize the word you want. Next, right-click and select Complete Word (or press Ctrl+Spacebar). Visual Basic then finishes the word you’re typing.

Definition

The Definition feature shows the place in the Code window where the selected variable or procedure is defined. To get a definition of a variable or procedure, right-click in the name of the variable or procedure of interest, and select Definition (or press Shift+F2). Your cursor is moved to the module and location where the variable or procedure was defined.

As you become more proficient with VBA, you can create libraries of VBA functions and subroutines. When you’re viewing a call to a particular subroutine or function, you often want to view the code behind that function. Fortunately, VBA gives you a quick and easy way to navigate from procedure to procedure. Assume that the following code appears in your application:

Private Sub cmdOkay_Click()
                          Dim intAgeInTen As Integer
                          If IsNull(Me.txtNameValue) Or IsNull(Me.txtAge.Value) Then
                              MsgBox "You must fill in name and age"
                              Exit Sub
                          Else
                              MsgBox "Your Name Is: " & Me.txtName.Value & _ 
                              and Your Age Is: " & Nz(Me.txtAge.Value)
                              Call EvaluateAge(Nz(Me.txtAge.Value))
                              intAgeInTen = AgePlus10(Fix(Val(Me.txtAge.Value)))
                              MsgBox "In 10 Years You Will Be " & intAgeInTen
                          End If
                      End Sub
                      

Select all Open in new window

If you want to quickly jump to the procedure called EvaluateAge, all you need to do is place your cursor anywhere within the name, EvaluateAge, and then press Shift+F2. This procedure immediately moves you to the EvaluateAge procedure. Ctrl+Shift+F2 takes you back to the routine you came from (in this case, cmdOkay_Click). This procedure works for both functions and subroutines.

Tip
If you prefer, you can right-click the name of the routine you want to jump to and select Definition. To return to the original procedure, right-click again and select Last Position.

Note
If the definition is in a referenced library, the Object Browser is invoked, and the definition is displayed.

Mysteries of the Coding Environment Solved

If you’re a developer who’s new to VBA, you might be confused by the VBE. We will begin by talking about the Code window. The Code window has two combo boxes, shown in Figure 9. The combo box on the left lists objects. For a form or report, the list includes all its objects; for a standard module, which has no objects, only (General) appears.


The combo box on the right lists all the event procedures associated with a particular object. Figure 10 shows all the event procedures associated with a command button. Notice that the Click event is the only one that appears in bold because it’s the only event procedure that has been coded.

The Project Window

The Project window, shown in Figure 11, enables you to easily maneuver between the modules behind the objects within your database. The elements of your project are displayed hierarchically in a tree view within the Project window. All elements of the project are divided into Microsoft Access Classes and Modules. All Form, Report, and Class modules are found within the Microsoft Access Classes. All Standard modules are found within Modules. To view the code behind an object, simply double-click the object within the Project window. To view the object, such as a form, single-click the name of the form in the Project window and then click the View Object tool (the second icon from the left on the Project window toolbar). You are returned to Microsoft Access with the selected object active.

Note

You can also right-click the object and then select View Code (the left icon on the Project window toolbar) to view the code or View Object to view the object. The context-sensitive menu also enables you to insert modules and Class modules, to import and export files, to print the selected object, and to view the database properties.

The Properties Window

The Properties window, pictured in Figure 12, enables you to view and modify object properties from within the VBE. At the top of the Properties window is a combo box that allows you to select the object whose properties you want to modify. The objects listed in the combo box include the parent object selected in the Project window (for example, the form) and the objects contained within the parent object (for example, the controls). After an object is selected, its properties can be modified within the list of properties. The properties can be viewed either alphabetically or categorically. In the example, the command button cmdOK is selected. The properties of the command button are shown by category.

The View Microsoft Access Tool

If at any time you want to return to the Access application environment, simply click the View Microsoft Access icon (the left icon) on the toolbar. You can then return to the VBE by using the taskbar or using one of the methods covered earlier in this article.

Find and Replace

Often, you name a variable only to decide later that you want to change the name. VBA comes with an excellent find-and-replace feature to help you with this change. You can simply search for data, or you can search for a value and replace it with some other value. To invoke the Find dialog box, shown in Figure 13, choose Edit, Find, or press Ctrl+F.


Type the text you want to find in the Find What text box. Notice that you can search in the Current Procedure, Current Module, Current Project, or Selected Text. The option Find Whole Word Only doesn’t find the text if it’s part of another piece of text. For example, if you check Find Whole Word Only and then search for Count, VBA doesn’t find Counter. Other options include toggles for case sensitivity and pattern matching.

You can also use the Replace dialog box to search for text and replace it with another piece of text (see Figure 14). You can invoke this dialog box by selecting Edit, Replace from the menu or by pressing Ctrl+H (or Alt+E, E). It offers all the features of the Find dialog box but also enables you to enter Replace With text. In addition, you can select Replace or Replace All. Replace asks for confirmation before each replacement, but Replace All replaces text without this prompt. I recommend you take the time to confirm each replacement because it’s all too easy to miscalculate the pervasive effects of a global find-and-replace.

Help

A very useful but under-utilized feature of VBA is the ability to get context-sensitive help while coding. With your cursor placed anywhere in a VBA command or function, press the F1 key to get context-sensitive help on that command or function. Most of the help topics let you view practical examples of the function or command within code. Figure 15 shows help on the With…End With construct. Notice that the Help window includes the syntax for the command, a detailed description of each parameter included in the command, and remarks about using the command. If you scroll down, examples of the construct appear that you can copy and place into a module (see Figure 16). This feature is a great way to learn about the various parts of the VBA language.

Splitting the Code Window

You can split the VBA Code window so that you can look at two routines in the same module at the same time. This option is useful if you’re trying to solve a problem involving two procedures or event routines in a large module. To split your Code window, as shown in Figure 17, choose Window, Split.

Notice the splitter. Place your mouse cursor on the gray splitter button just above the Code window’s vertical scrollbar. By clicking and dragging, you can size each half of the window. The window can be split into only two parts. After you have split it, you can use the Object and Procedure drop-down lists to navigate to the procedure of your choice. The drop-down lists will work for either of the two panes of the split window, depending on which pane was last selected.

Note

You can only view routines in the same module in a particular Code window, but several Code windows can be open at the same time. Each time you open an Access, Form, or Report module, Access places you in a different window. You can then size, move, and split each module.

Using Bookmarks to Save Your Place

The Access 2010 coding environment enables you to create place markers called bookmarks so that you can easily return to key locations in your modules. To add a bookmark, right-click on the line of code where you will place the bookmark and choose Toggle, Bookmark, or choose Bookmarks, Toggle Bookmark from the Edit menu. You can add as many bookmarks as you like.

To navigate between bookmarks, choose Edit, Bookmarks, Next Bookmark, or Edit, Bookmarks, Previous Bookmark. A bookmark is a toggle. To remove one, you simply choose Toggle, Bookmark from the shortcut menu or Bookmarks, Toggle Bookmark from the Edit menu. If you want to clear all bookmarks, choose Edit, Bookmarks, Clear All Bookmarks. Bookmarks are not saved when you close the database.

Note

Do not confuse the bookmarks discussed in this section with recordset bookmarks.

Customizing the VBE

Access 2010 provides Access programmers with significant opportunity to customize the look and behavior of the VBE. To view and customize the environment options, choose Tools, Options with the VBE active. Figure 18 shows the Options dialog box; its different aspects are discussed in detail in the following sections.

Coding Options—The Editor Tab

The coding options available to you are found under the Editor tab of the Options dialog box. They include Auto Indent, Tab Width, Auto Syntax Check, Require Variable Declaration, Auto List Members, Auto Quick Info, and Auto Data Tips.

The Auto Indent feature invokes the automatic indenting of successive lines of code. This means that when you indent one line, all other lines are indented to the same position until you specify otherwise. I recommend that you use this feature.

The Tab Width feature determines the number of characters that Access indents each line. It’s important that you do not change this number in the middle of a project; otherwise, different parts of the program will be indented differently.

The Auto Syntax Check feature determines whether Access performs a syntax check each time you press Enter after typing a single line of code. Many developers find this option annoying. It’s not uncommon to type a line of code and notice a typo in a previous line of code. You want to rectify the error before you forget, so you move off the incomplete line of code you’re typing, only to get an error message that your syntax is incorrect. I recommend that you turn off this feature.

The Require Variable Declaration option is a must. If this option is turned on, all variables must be declared before they are used. This important feature, when set, places the Option Explicit line in the Declarations section of every module you create. You’re then forced to declare all variables before they’re used. The compiler identifies many innocent typos at compile time, rather than by your users at runtime. I strongly recommend that you use this feature.

The Auto List Members option determines whether the List Properties/Methods and List Constants features are automatically invoked as you type code in the Code window. They help you in your coding endeavors by presenting a valid list of properties, methods, and constants. I recommend that you use these features.

The Auto Quick Info feature determines whether the syntax of a procedure or method is automatically displayed. If this option is selected, the syntax information is displayed as soon as you type a procedure or method name followed by a space, period, or opening parenthesis. I recommend that you use this feature.

The Auto Data Tips feature is used when you’re debugging. It displays the current value of a selected value when you place your mouse pointer over the variable in Break mode. I recommend that you use this feature.

Code Color, Fonts, and Sizes—The Editor Format Tab

In Access 2010, you can customize code colors, fonts, and sizes within the coding environment. You can also specify the foreground and background colors for the Code window text, selection text, syntax error text, comment text, keyword text, and more. You can select from any of the Windows fonts and sizes for the text in the Code window. For a more readable Code window, select the FixedSys font.

General Options—The General Tab

The General tab contains miscellaneous options that affect the behavior of the development environment. For example, the Show Grid option determines whether a form grid is displayed, and the Grid Units are used to designate the granularity of the gridlines.

Docking Options—The Docking Tab

The Docking tab enables you to specify whether the windows within the VBE are dockable. A window is said to be dockable if you can lock it alongside and dock it to another window. It is not dockable when you can move it anywhere and leave it there. The windows you can dock include the Immediate, Locals, Watch, Project, Properties, and Object Browser windows.

Caution

All the customization options that have been discussed apply to the entire Access environment. This means that, when set, they affect all your databases.

Practical Examples: Using Event Routines, User-Defined Functions, and Subroutines

The Chap8.accdb database includes two forms: frmClients and frmProjects. The frmClients form contains two command buttons. The first command button is used to save changes to the underlying record source (the tblClients table.) The code looks like this:

Private Sub cmdSave_Click()
                          'Save changes to the client record
                          DoCmd.RunCommand acCmdSaveRecord
                      End Sub
                      

Select all Open in new window

The code, placed under the cmdSave command button on the frmClients form, executes the RunCommand method of the DoCmd object. The acCmdSaveRecord intrinsic constant, when used as a parameter to the RunCommand method, causes changes made to the form to be saved to the underlying data source.

The second command button is used to undo changes made to the current record. The code looks like this:

Private Sub cmdUndo_Click()
                          'Undo changes
                          DoCmd.RunCommand acCmdUndo
                      End Sub
                      

Select all Open in new window

This code is found under the cmdUndo button on the frmClients form. It executes the RunCommand method of the DoCmd object. The acCmdUndo intrinsic constant, when used as a parameter to the RunCommand method, undoes changes made to the form.
The code originally located under the cmdViewProjects was generated by the command button wizard. It looked like this:

Private Sub cmd_Click()
                      On Error GoTo Err_cmd_Click
                      
                          Dim stDocName As String
                          Dim stLinkCriteria As String
                      
                          stDocName = "frmProjects"
                      
                          stLinkCriteria = "[ClientID]=" & Me![txtClientID]
                          DoCmd.OpenForm stDocName, , , stLinkCriteria
                      
                      Exit_cmd_Click:
                          Exit Sub
                      
                      Err_cmd_Click:
                          MsgBox Err.Description
                          Resume Exit_cmd_Click
                      
                      End Sub
                      

Select all Open in new window

The code first declared two variables: one for the name of the form to be opened and the other to hold the criteria used to open the form. It then assigned a value to the stDocName variable as well as to the stLinkCriteria variable. Finally, it used the OpenForm method of the DoCmd object to open the frmProjects form, passing the value in stLinkCriteria as the WHERE clause for the OpenForm method. This wizard-generated code is inefficient. It uses variables that are not necessary. More importantly, it is difficult to read. To eliminate both problems, the code is changed as follows:

Private Sub cmdViewProjects_Click()
                      On Error GoTo Err_cmdViewProjects_Click
                      
                          DoCmd.OpenForm FormName:="frmProjects", _
                              WhereCondition:="[ClientID]=" & Me![txtClientID]
                      
                      Exit_cmdViewProjects_Click:
                          Exit Sub
                      
                      Err_cmdViewProjects_Click:
                          MsgBox Err.Description
                          Resume Exit_cmdViewProjects_Click
                      End Sub
                      

Select all Open in new window

Although the RecordSource appears to be the entire tblProjects table, this is not the case. The key to the solution is found in the frmProjects form. The code in the Open event of the frmProjects form looks like this:

Private Sub Form_Open(Cancel As Integer)
                          If Not IsLoaded("frmClients") Then
                              MsgBox "You must load this form from the Projects form", _
                                  vbCritical, "Warning"
                              Cancel = True
                          End If
                      End Sub
                      

Select all Open in new window

This code first uses a user-defined function called IsLoaded to determine whether the frmClients form is loaded. (The mechanics of the IsLoaded function are discussed in the following text.) The function returns True if the frmClients form is loaded, and False if it is not. If the frmClients form is not loaded, a message is displayed to the user, and the loading of the frmProjects form is canceled. If the frmClients form is loaded, the RecordSource property of the frmProjects form is determined by the WHERE clause passed as part of the OpenForm method. Even in a client/server environment, Access sends only the appropriate project records over the network wire.

The IsLoaded function looks like this:

Public Function IsLoaded(strFormName As String) As Boolean
                          Const FORMOPEN = -1
                          Const FORMCLOSED = 0
                      
                          If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> FORMCLOSED Then
                              IsLoaded = True
                          Else
                              IsLoaded = False
                          End If
                      
                          'IsLoaded = SysCmd(acSysCmdGetObjectState, acForm, strFormName)
                      End Function
                      

Select all Open in new window

The function declares two user-defined constants. These constants are intended to make the function more readable. The built-in SysCmd function is used to determine whether the form whose name is received as a parameter is loaded. The SysCmd function, when passed the intrinsic constant acSysCmdGetObjectState as the first argument and acForm as the second argument, attempts to determine the state of the form whose name is passed as the third argument. The IsLoaded function returns True to its caller if the form is loaded, and False if it is not. An alternative to this function is the following:

Public Function IsLoaded(strFormName As String) As Boolean
                          IsLoaded = SysCmd(acSysCmdGetObjectState, acForm, strFormName)
                      End Function
                      

Select all Open in new window

This function is much shorter and more efficient, but is less readable. It simply places the return value from the SysCmd directly into the return value for the function.

In addition to the save and undo that are included in the frmClients form, this version of the frmProjects form contains one other routine. The BeforeUpdate event of the form executes before the data underlying the form is updated. The code in the BeforeUpdate event of the frmProjects form looks like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
                          If Me.txtProjectBeginDate.Value > _
                              Me.txtProjectEndDate.Value Then
                              MsgBox "Project Start Date Must Precede " & _
                                  "Project End Date"
                              Cancel = True
                          End If
                      End Sub
                      

Select all Open in new window

This code tests to see whether the project begin date falls after the project end date. If so, a message is displayed to the user, and the update is canceled.

Summary

This article, and the two proceeding it covered all of the basics of the VBA language. This article began with coverage of the DoCmd object and built-in functions. Effectively using built-in functions gives you the power, flexibility, and functionality required by even the most complex of applications. You also got a strong command of the VBE. Knowing how to use the VBE to your advantage is imperative to a successful development experience! Finally, you saw some practical examples of how you can use the VBA language to enhance your own applications.

This article was excerpted from Alison Balter's Mastering Access 2007 Development published by SAMS. The book is available at http://www.informit.com/store/product.aspx?isbn=0672329328.
Chap8ex.accdb