[Figures are not included in this sample chapter]

Peter Norton Guide to Access 97 Programming

- 3 -

Touring the World of Object-Oriented Programming

This chapter takes a closer look at the elements that are essential to object-oriented, event-driven programming. Without getting into the details of code generation, this chapter discusses properties and methods in more detail and introduces the concepts of variables and constants. It also discusses how information flows to and from functions and procedures. The elements that control the flow of database processing are also introduced.

Comparing with Procedural Languages

In procedural languages such as Fortran and COBOL, the application drives the process. The program statements determine what happens next. With event-driven languages, events control what happens next. If no events occur, nothing happens.

A program written in a procedural language begins executing at the beginning of the program and, with the bit in its teeth, runs to the end along a defined path. It might call functions and subroutines along the way or branch to a different path, but only because the program was written to behave this way.

With an event-driven program, a user action or some other system event runs the job. The user is holding the reins and the code responds. Because you don't know what the user will do next, your application must be prepared for anything. It is important to account for all possible forms of user action and make provisions for errors or exceptional events.

How Do Object-Oriented Languages Work?

In the VBA object-oriented language, there are three essential pieces: objects, events, and methods. Objects, as discussed in Chapter 2, "Reviewing Access Database Elements," are all the elements that make up an application: tables, forms and their controls, reports, queries, and so on. When something happens to an object, it is called an event. The object responds to the event by performing some kind of action, composed of one or more methods.

Not all objects are met with all events, nor can all objects perform all methods. Here are some examples of events:

• Field data changes.
  
  
• A mouse button is clicked.
  
  
• The user presses a key.
  
  
• An object gets or loses focus.
  
  
• A form opens, closes, or is resized (that is, maximized, minimized, or restored).
  
  
• A report is printed or formatted.
  
  
• A runtime error occurs.

Each type of object has a specific set of properties that determines its appearance and how it responds to a given event. The properties that rule an object's response to events are called event properties. When you want to specify how the object reacts, you set its event property to a macro or a procedure that contains the desired actions. Table 3.1 lists a few of the nearly 50 events that could occur. It also shows corresponding event properties and the objects to which they apply.

Table 3.1. Examples of events and event properties.

The final piece of the puzzle is the method. Each object has a set of methods that can be applied to it. A method is an action such as GoToRecord, ApplyFilter, and OpenForm. Such actions are specified in the object's event property settings. If you do not choose an event property for the object, it responds with a built-in behavior defined for each type of object. For example, when a text box gets focus and you have not specified its OnGetFocus event property, the built-in method changes the text box color.

If you have set the object's event property to a macro or an event procedure, Access first processes the built-in behavior and then executes your macro or procedure. For example, you click a command button that moves focus to the second page of a form. The button briefly changes to appear pressed in (the built-in behavior) and then quickly runs the macro to move to the next page in the form.

Getting Familiar with the Fundamental Elements

Objects are the fundamental building blocks in an Access application. That catch-all term includes everything from the most sophisticated form or report to the tiniest command button or check box. In Chapter 2, you examined many of the Access objects, how to refer to them, and how they behave. This chapter focuses more on the elements that directly relate to the VBA programming language, such as variables, constants, arguments used by procedures, and elements that control the program flow.

In an application, objects are tied together with macros and VBA procedures that determine the value or status of an object and pass the information along to another object.

Although this chapter discusses in detail the major elements used in database application development, the art of putting it all together in meaningful program code is examined more closely in Chapter 5, "Examining and Modifying the Wizard's Code." In that chapter, you will analyze the procedures and modules that the Database Wizard created in Chapter 4, "Creating an Application with a Wizard."

Variables

Variables are named locations in memory used to store values temporarily. Variables are used in a program to perform calculations and manipulate table data. They are similar to fields but exist only in VBA, not in a recordset. Variables must be declared before they can be used in a procedure. Declaring a variable can be as simple as giving it a name, or more thoroughly by also telling Access what kind of a variable you are planning on using: numeric, string, and so on.

The scope of a variable refers to who has access to it. This depends on where and how you declare the variable. The variable might be limited to the procedure that contains the declaration, throughout a specific module with several procedures, or to the entire application. The lifetime of a variable refers to how long the variable has a value. A variable gains a value when it is declared. It takes on the default value for that data type, if you don't supply a specific value. When a variable loses scope, it ceases to exist.

Variables should have unique names to avoid conflict, at least within their own scope. They are also a specified data type. The value of a variable can change over its lifetime, but the name remains the same.

Declaring Variables

You have two ways to declare variables: implicit and explicit. To implicitly declare a variable, all that is required is to use the variable name in a procedure. When you implicitly declare a variable, you are essentially throwing it out there, for better or for worse. For example, the following statements declare the variable, MyName, implicitly in a procedure:

MyName = "Bob" 
MsgBox MyName

Figure 3.1 shows the results of running this short procedure.

Figure 3.1. Declaring a variable implicitly.

There are risks with implicitly declaring variables. For example, if you misspell an implicitly declared variable later in the procedure when referring to it, Access thinks it is just another variable and you might get some surprising results.

---

Peter's Principle: Be generous with comments in all the procedures you write. Even if it is crystal clear to you at the time, weeks later you will waste precious time trying to figure out what exactly the procedure is supposed to do. For example, add a comment to the line:

---

MyName = "Bob"         `Sets MyName variable to the value "Bob".

Comments are preceded by an apostrophe (`). Access ignores the mark and all text that follows it. You can place a comment on a line by itself or inline with a statement. In either case, precede the comment with an apostrophe. Explicitly declaring variables means naming them and specifying their data type ahead of the first program statement. When you declare the variables explicitly, Access can spot a misspelled variable later in the procedure because it is not one of the ones you declared. You can declare variables in a procedure or a module. The location and syntax of the declaration determines the scope of the variable. The most common way to declare a variable is with the Dim statement.

---

Technical Note: The term Dim does not mean that the declaration appears in a subdued color. Dim is left over from earlier programming languages, where it was an abbreviated form of the Dimension statement. In those days, compilers read only the first five or six characters of a command anyway, so it was okay to abbreviate. One requirement of those early programs was to tell the computer how much memory would be needed to run the program. The Dimension statement, which appeared at the beginning of the program, specified any variables, such as vectors or arrays, that were not part of the data set but would require space.

---

Figure 3.2 illustrates declaring the MyName variable explicitly. Notice that Access gives you some help with the declaration. When you begin typing the data type, a list of relevant objects shows up. Automatically displaying this member list is one of the coding options available in the Module tab of the Options dialog box. If you don't want to see the member list when you begin typing, clear the option in the Options dialog box. You can choose to continue typing, press Esc to remove the list, or take one of the following actions to choose the appropriate name from the list:

• Press Tab to enter the highlighted item and remain on the same code line. Double-clicking the item also enters it into the statement.
  
  
• Press the spacebar to enter the item followed by a space so that you can continue with the declaration.
  
  
• Press Enter to enter the item and move to the next line in the procedure.

Figure 3.2. Declaring strMyName explicitly as a string variable.

In the figure, you also see that the variable name is preceded by the str tag, which identifies it as a string data type. This is a good practice that improves the code readability.

A complete description of the data types that can be used in Access appears in the section, "Types of Data Variables," later in this chapter.

When you declare a variable in a procedure with Dim, that variable is available only within the procedure. Its scope is local and its lifetime is over when the procedure ends. You can also declare a variable in a procedure using Static instead of Dim. The scope of a Static variable is still within the procedure where it is declared, but the variable retains its value between calls to that procedure. The next time the same procedure is run, the value of the variable is the same as when the procedure closed. A Static declaration is handy for computing running totals, for example.

When a variable is declared with the Dim statement in the Declarations section of a module instead of in a procedure, it is available to any procedure in the module. This is useful when several procedures need access to the same information, perhaps passing values back and forth.

You can also declare a variable as Public in any procedure or module. A Public variable is available to the entire application. A Public variable is useful, for example, for making the company CEO's name available throughout the application.

---

TIP: Except in special circumstances, it is best to use explicitly defined private declarations at the procedure level. This saves memory because the space the variable occupied is released as soon as the variable disappears. In addition, you don't run the risk of using the same variable name twice for two different and conflicting purposes.

---

When you first start a new module, Access automatically enters the first two lines (see Figure 3.3). The second statement, Option Explicit, requires that you explicitly declare all the variables you intend to use. If the module includes the Option Explicit statement, implicitly declared variables are not permitted. Figure 3.4 shows the compilation error that appears if Access encounters a variable you have not explicitly defined.

When you choose OK in the error message dialog box, Access points to the name of the procedure containing the error and highlights the variable name it doesn't recognize (see Figure 3.5), making it easy for you to find and correct the problem.

Figure 3.3. Access includes a statement in a new module, requiring all variables be declared explicitly.

Figure 3.4. Access displays a compile error when it doesn't recognize a variable.

Chapter 10, "Debugging VBA Procedures," contains more information about error messages and how to respond to them. It also describes many debugging techniques such as setting breakpoints and stepping through program code one statement at a time.

The alternative to declaring variables explicitly is to delete the default Option Explicit statement and declare variables implicitly as you refer to them. Both of these Option statements apply to the entire module and all the procedures and functions in it.

Figure 3.5. Access highlights the compile error.

---

NOTE: The Option Compare statement determines how Access compares character strings in your module. Your choices are Database, Binary, and Text. The default option is Database, which uses whatever sort order you have specified for your database. Option Compare Database is used only in Access applications. Option Compare Binary uses a sort order based on the internally stored binary values in which all uppercase letters are first, followed by lowercase letters. If the Option Compare statement is not used at all, the default comparison method is Binary. The third option is Text, which is case-insensitive and based on your local language. Use the default Option Compare Database except in special cases such as in a module that uses a bookmark and must be case-sensitive. Then use the Binary option, which is case-sensitive. You can use different Option Compare statements in modules within the same database.

---

Using a Declared Variable

After you have declared a variable and given it a value in the procedure, you can assign that value to an object in your application. Figure 3.6 shows a procedure that runs when MyForm is activated. It first declares strFavCD as a string variable and then gives it the value "Placido Domingo". The next statement changes the MyForm caption property from the default "Form" to that value. Notice that the form in the background shows Placido Domingo in the title bar.

Figure 3.6. Changing the form caption to a variable.

This procedure is included in the Form_MyForm class module and is available only to this form.

Naming Variables

When you declare a variable, you must give it a name. The name you assign can be very helpful when you try to maintain the program weeks after you have written it. In addition to the naming rules imposed by Access, certain naming conventions have been developed by programmers over the years to make their jobs easier. Variable base names are mixed case, using upper- and lowercase letters to help readability. For example, the name MyFirstJob is easier to read than myfirstjob. The optional tags preceding the variable base name can indicate the type of variable. StrMyFirstJob would indicate that it is a string variable.

The section, "Naming Conventions," later in this chapter, presents more details about the popular Leszynski Naming Conventions (LNC).

Types of Data Variables

Variables come in the same data types as fields, except they are classified as VBA data types instead of DAO data types. Table 3.2 lists the VBA variable data types and gives an example of each. Notice that each sample variable name in the table is preceded by a tag indicating its type.

Table 3.2. Variable data types.

The Variant data type can store all kinds of data: strings, numbers, dates and times, or one of its special values, Null or Empty. Variants are easy to use but require more memory space than other types of data. If you don't specify a data type when you declare your variables, Access uses Variant by default.

The Variant value Empty indicates the variable has not yet been assigned a value. Empty is replaced by a value as soon as one is assigned, even if it is 0. Null indicates a database field that hasn't been initialized and the data is missing or unknown. You can use these values to test for these special values in an error-trapping procedure.

Look in Chapter 10 for information about how to detect and intercept errors.

Arrays

Arrays are groups of variables of all of the same type and with the same name. They are useful for looping through a recordset and storing a field value in the array items from a number of records. The code is simpler and has fewer statements.

Arrays are declared just like other variables, depending on the scope you want them to enjoy. If you want them to be accessible to all the procedures in the module, declare them in the declaration section of the module or declare them as Public in the procedure. For a local array, declare it with a Dim or Static statement in the procedure.

The only difference between arrays and other variables is that the arrays have an index. When you declare an ordinary (fixed size) array, you include upper and lower bounds that specify the maximum number of items you expect the array to handle. The following statements are examples of declaring arrays:

Dim intCountCD(25) As Integer    `Declares 25 elements, indexed 0 through 24. 
Dim strSingers(15) As String     `Declares 15 elements, indexed 0 through 14.

To give you an idea of how arrays can be useful, suppose you run a mail-order business that specializes in CDs and you want to keep a current catalog of the most popular singers and titles.

The following statements give an example of using an array in a procedure. You have sorted the CDs by date of release with the newest ones first, and you want the names of the performers of the latest 15 recordings. The statements first declare a 15-item array for storing the singers' names. Then they loop through the recordset and copy the Singer values from the first 15 records. The DoCmd.GoToRecord.acNext is a recordset method.

Dim intI As Integer 
Dim strSinger(1 To 15) As String 
For intI = 1 To 15    `Initializes the index. 
         DoCmd.GoToRecord.acNext    `Moves to next record 
         strSinger(intI) = me!Singer 
Next intI    `Increments the index and loops.

You can also declare multi-dimensional arrays with a declaration such as the following:

Dim int3D(5, 1 To 10, 5 To 8)

This declaration specifies a three-dimensional array with one dimension, indexed from 0 to 5, the second from 1 to 10, and the third from 5 to 8. Int3D(4,8,6) is an element of this array.

---

WARNING: Be careful when you are estimating the size of your array, especially with multi-dimensional arrays. Declaring an array reserves memory for the entire array, whether you fill it up or not. Remember also that if your array contains the Variant data type variable, even more memory is used for each item.

---

Objects and Object Variables

Between the wizards and the design windows, you might be able to create and modify all the forms and reports you'll ever need, but it is important to know that you can do the same thing within a procedure. VBA statements can create, modify, or delete any object at runtime. An example was shown earlier in Figure 3.6, where the procedure changed the form's caption when the form was activated at runtime.

Recall that Access groups objects into collections: a forms collection, a reports collection, and so on. When you refer to an object in VBA, you use the collection identifier to help specify the object. Table 3.3 shows three ways to refer to an object in a procedure.

Table 3.3. How to refer to an object in a procedure.

The first example in the table uses the ! operator, which signifies that what follows is a user-named object. If the form name were My Form, the object name would be Forms![My Form].

The second example uses quotation marks within parentheses to designate the object name. Once again, to refer to My Form, the object name would be Forms("My Form"). If the object is a declared variable, you do not use the quotation marks.

The third example in this table refers to a form by using its position in the collection as an index. Forms(2) refers to the form that was opened third. The first form opened would be Forms(0).

An object variable is used to refer to a specific object. The variable can be used later in the procedure in place of the full name of the object itself. The difference between declaring a data variable and declaring an object variable is that the data variable actually stores the value in a location in memory. The object variable stores only a pointer to the physical object in the database.

---

NOTE: You can use several object variables to refer to the same object. If the object changes, every object variable that refers to it will reflect the change. For example, if you change the prices of some of the CDs in your catalog, all the object variables that refer to those CDs will also show the changes because they point to the changed value.

---

To establish the connection between an object variable and the object, use the Set statement. For example, the following statements open the CDs table in the current database, Music:

Dim dbsMusic As Database 
Dim rstCDs As Recordset 
Set dbsMusic = CurrentDb() 
Set rstCDs = dbsMusic.OpenRecordSet("CDs")

The two Dim statements declare the object variables as Database and Recordset, respectively. The first Set statement assigns the object variable name dbsMusic to the open Music database. The dbs tag indicates that the variable refers to a database object. The next Set statement defines the rstCDs object variable as the CDs table in the dbsMusic object. The rst tag indicates that the referenced object is a recordset.

Set statements can appear anywhere in a procedure or module, but the Dim and Static declaration statements must precede any action statements.

The Set statement comes with two keywords: New and Nothing. The New keyword actually creates a new instance of an existing class object, such as a form. An instance is a temporary copy of an open object. By using instances of a form, you can view several copies of the same form at once on the screen.

For example, the following statements create a new instance of the form CDs. The new instance will have all the same controls and properties as the original.

Dim frmNew As Form_CDs 
Set frmNew = New Form_CDs

The new instance is hidden at first. To see both forms on-screen, add the following statement:

frmNew.Visible = True

When you are finished with an instance of an object, it is a good idea to remove it and release the memory and other system resources. To remove the instance, disassociate it from the object variable with the following statement:

Set frmNew = Nothing

Constants

Constants are named values that, once set, do not change during the execution of the module or procedure in which they are declared. Using constants makes your code much easier to read and maintain because the constant name can give you a clue as to what it represents. You set or change the value in only one place: the declaration. A constant can represent a numeric or string value, or refer to another constant. An expression containing a combination of arithmetic or logical operators can also be named as a constant.

Constants are defined by either the system or the user. After the constant is defined, you can use it anywhere within its scope, and its value remains the same. Constant scoping is discussed later in this section.

Access supports two kinds of constants: symbolic and intrinsic. Symbolic constants, also called user-defined constants, are defined in a module or procedure using the Const statement and keep their value during execution of the module or procedure. Access, VBA, and DAO provide the intrinsic or system-defined constants that can be used anywhere in all modules.

Symbolic Constants

Symbolic constants represent values that you intend to use repeatedly in a module, such as a fixed interest rate or the title of your favorite CD. After you have given the variable a meaningful name and declared the value, you can use the name in place of the value anywhere in your module. Because you declare it only once, to make a change in the value, you need to change only the declaration of the value. For example, if the interest rate (or your favorite CD) changes, you need only change the value of the constant where it is declared rather than everywhere it occurs in the code.

To declare a symbolic constant, use the Const statement in the declarations section at the beginning of the module or procedure. The following examples show statements declaring a numeric and string constant, respectively:

Const conIntRate = .075 
Const conFavCD = "Placido Domingo"

The three-letter tag con in the constant names also improves the readability by clearly indicating that when the constant appears later in the code, it is indeed a symbolic constant. You need only look at the declaration section of the procedure or module to find out the value. This is just one example of the adopted naming conventions that help decipher code.

---

TIP: You cannot change the value of the constant during the procedure--it remains the same until you change the value in the declaration. Also, be careful not to assign a name to your constant that is the same as one of the intrinsic constants. None of the intrinsic constants begin with con, so if you stick to that, you will be safe.

---

Scoping Symbolic Constants

The scope of a symbolic constant depends on where you have declared it. Similar to variables, if you declare a constant in a procedure, it is available only within that procedure. Declaring a constant in the declaration section of a module makes it available throughout the module. Constants declared in a module are considered Private unless specifically declared Public. Public constants are available to any module in the entire application.

---

TIP: Keeping all your user-defined constants Private is a good idea. Declaring constants Public might cause a conflict with other constants of the same name in another module. It is also a good idea to explicitly declare constants in a module as Private to avoid any doubt.

---

Intrinsic Constants

In addition to the constants that Access provides, you can use any of the constants from the DAO and VBA libraries available in the Object Browser dialog box. This gives you access to hundreds of constants. You also can add references to other object libraries if that is not enough. Intrinsic constants are always available.

Chapter 17, "Linking with Other Office Applications," discusses adding references to other application libraries and using their objects in VBA code.

Many intrinsic constants are directly related to a particular function, event, method, or property. For example, the acLeftButton constant is an Access button mask constant used in event procedures for the MouseDown, MouseUp, and MouseMove events to determine whether the left mouse button was involved in the event. The constant is automatically declared by Access and set to True or False, depending on whether the event occurred or not.

---

TIP: To find out which constants are related to a certain function, event, method, or property, open the Object Browser and scroll down the lists of constants that are available in the object libraries.

---

The names of the intrinsic constants have a two-letter prefix that indicates from which library they come. Access constants begin with ac, DAO constants with db, and those from the VBA library begin with vb. Table 3.4 describes the categories of intrinsic constants and gives some examples.

Table 3.4. Categories of intrinsic constants.

In future versions of Access, the values returned by some of the intrinsic constants might change, whereas the names will not. Therefore, it is wise to use the constant name in your code instead of the value it returns. To find the value of the constant, look it up in the Object Browser.

Arguments

Arguments are bits of information that a procedure or method uses during execution. Arguments can be required or optional. You pass them to the procedure by naming them in the procedure definition right after the name of the procedure. For example, the following statement defines the ArgProc procedure as requiring three arguments:

Private Sub ArgProc(strArg1 As String, intArg2 As Integer, dtmArg3 As Date)

The first, strArg1, is declared as a string variable, the second as an integer, and the third as a date. When you call this procedure, you can supply the actual argument values in two different ways: by position or by name.

To supply the argument by position, place the values in the same order as in the procedure definition, separated by a space and a comma:

ArgProc "Bob", 15, #5/5/98#

The string variable is enclosed in quotation marks, and the date is enclosed in date delimiter characters (#). To pass the arguments to the procedure by name, you needn't follow the order in the procedure definition because you are specifically identifying each argument. The name of the argument is followed by the colon/equal sign pair (:=), and the arguments are separated by commas. Here's an example:

ArgProc Arg3:=#5/5/98#, Arg1:="Bob", Arg2:=15

Some arguments are optional and must be labeled as such in the procedure definition:

OptArgProc(strArg1 As String, intArg2 As Integer, Optional dtmArg3 As Date)

When the procedure is called, you can omit the optional arguments. If the optional arguments are in the middle of the argument list, you must still use commas to separate the arguments even if you don't include the optional ones. For that reason, it helps to list all the optional arguments last in the list. You can set a default value for an optional argument. For example, the following sets the default value of dtmArg3 to 5/5/98:

OptArgProc(strArg1 As String, intArg2 As Integer, Optional dtmArg3 As Date = #5/5/98#)

When you pass arguments to a function procedure, you do not need to enclose them in parentheses unless the function returns a value. For example, the first statement in the following returns Yes or No from the MsgBox() function, and the second only displays a message and does not return a value.

strAnswer = MsgBox("Are you here on business?", 4, "Question 1") 
MsgBox "Well done!", 0

Figure 3.7 shows an example of a custom procedure that uses a built-in VBA function to compute the monthly payment on a $100,000 loan with a 7.5% annual percentage rate and a 30-year duration. The complete code appears in Listing 3.1.

Listing 3.1. Calculating a payment amount.

Sub GetPmt() 
Dim Fmt As String, varLoanVal As Variant, varFuture As Variant 
Dim varAPR As Variant, varTotPmts As Variant, Payment As Variant 
varLoanVal = 100000             `Loan amount $100,000 
varFuture = 0                   `Loan paid off at end of period. 
varAPR = 0.075                  `Interest rate 7.5%. 
varTotPmts = 360                `Monthly payments for 30 years. 
Fmt = "$###,###,##0.00"         `Define payment format. 
`The arguments are passed to the Pmt() function. 
Payment = Pmt(varAPR / 12, varTotPmts, -varLoanVal, varFuture) 
MsgBox "Your payments will be: " & Format(Payment, Fmt) _ 
   & " per month."

End Sub

Figure 3.7. A procedure using arguments to compute monthly payments.

The Dim statements declare the Fmt variable as a string and all the rest as Variant because they are numbers to be used in the calculation. Numbers that are not intended for calculations, such as Zip codes, can be declared as string variables. Then four of the variables are explicitly evaluated in the next four statements.

The Fmt variable defines the format for the monthly payment amount displayed in the message box. The next statement invokes the built-in function Pmt() and passes the variable arguments to it in the proper order, separated by commas. The last statement displays a message box with the results of the calculation. This procedure would be more useful if it displayed an input box requesting the loan information from the user; however, this is a simpler example.

Arguments usually are passed by reference to a variable, but you can also pass them by value by using the ByVal keyword in the procedure declaration. In Figure 3.8, the procedure GetOct passes a value to a function named Octal to find the octal equivalent to a decimal number. The function Octal is defined as receiving the argument as an integer value and uses the built-in function Oct() to return the octal equivalent. The procedure code appears in Listing 3.2.

Figure 3.8. A procedure calls a function that expects an argument by value.

Listing 3.2. Passing arguments by value.

Private Sub GetOct() 
Dim X As Integer, intOctVal As Integer 
X = 394                     `Set value of argument. 
intOctVal = Octal(X)        `Calls function Octal() 
MsgBox "The octal value of " & X & " is " & intOctVal 
End Sub 
Function Octal(ByVal intOctArg As Integer) 
   Octal = Oct(intOctArg)   `Computes octal value of argument. 
    
End Function

---

NOTE: Passing arguments by value can slow down the execution of the procedure. When you pass an argument by reference, you are really giving the procedure a pointer to the argument, which takes up only four bytes of space for any data type. Passing an argument by value copies the value, which can require up to 16 bytes and take longer to pass as well as use up more memory.

---

Some methods, such as the SelectObject method, require arguments before they can execute the corresponding action. The only argument the SelectObject method requires is objecttype, which must be one of the intrinsic constants: acForm, acMacro, acModule, acQuery, acReport, or acTable. Two optional arguments are the specific object name and whether to select the object in the database window or to select an object that is already open. For example, the statement

DoCmd.SelectObject acForm, "Switchboard", True

runs the SelectObject action to open a form named Switchboard in the database window. AcForm is the intrinsic constant for the Access form object.

In Chapter 5, you will see many examples of including arguments when calling procedures and methods. The DoCmd.SelectObject statement is one of them.

DAO Objects

In 1993, Microsoft Corporation introduced its Jet DBEngine and Data Access Objects (DAOs), which greatly enhanced Visual Basic 3.0 as a database application tool. The Jet became the primary means of connecting to and maintaining data in desktop databases. In contrast with Access objects such as forms and reports that are static, DAO objects are all related to the underlying data and are dynamic.

Figure 3.9 illustrates the DAO object hierarchy. All of the objects in the tree also form collections. For example, you might have more than one database in your workspace and probably more than one table definition in your database. To go even higher in the tree, there may be a workspaces collection on a network served by the same DBEngine.

Figure 3.9. The DAO object hierarchy.

Many of the DAO objects are familiar to Access users, but a few might be new. Table 3.5 describes the DAO objects.

Table 3.5. The DAO objects.

All the DAO objects in Table 3.5 can be referred to by declaring object variables. For example, the following statements declare database and query variables and then assign the current database to the variable dbs. The third statement begins to define a new query named Calif, and the last statement specifies the query name:

Dim dbs As Database, qry As Query 
Set dbs = CurrentDB() 
Set qry = dbs.CreateQueryDef() 
qry.Name = "Calif"

Next, you would add some SQL statements in VBA code that define the query and open a form with the new query as the record source. The form would display only those records that meet the criteria in the SQL SELECT statement.

Each DAO object has a corresponding set of properties, methods, events, and functions:

• The nearly 100 DAO object properties include FieldSize, DefaultType, KeepLocal, OrdinalPosition, SourceTable, and Required.
  
  
• DAO objects can use over 50 methods including CreateField, CreateQueryDef, CreateRelation, FindPrevious, and MoveNext.
  
  
• There are 11 data events including AfterUpdate, BeforeInsert, Current, Delete, and NotInList.
  
  
• Eleven functions, all dealing with field values, can be used with DAO objects. These include First(), Last(), Max(), Min(), Avg(), and Sum().

After you begin working in VBA code, the distinction between Access objects and DAO objects will begin to blur, and you will view them both simply as members of the Access programming family.

Several objects, methods, properties, and statements have been deleted from Visual Basic 4.0, but they are still supported in the interest of backward compatibility. For example, the terms Dynaset and Snapshot are no longer used, so all the CreateDynaset and CreateSnapshot methods have been replaced by the OpenRecordset method. Refer to the DAO Reference Help for more details about obsolete features.

Naming Conventions

Access imposes certain rules when naming variables and constants:

• All names must begin with a letter.
  
  
• The name can contain only letters, numbers, and the underscore symbol. No punctuation or spaces.
  
  
• The name can contain up to 255 characters.
  
  
• The name cannot contain any keywords or other words reserved by Access, such as IF, LOOP, CLOSE, OR, and MOD.

In addition to these rules, programmers over the years have gradually accepted a naming convention that makes their code easier to decipher months later. Such standard terminology also makes the code understandable to another programmer. You saw an example earlier when a constant was named using the con prefix (which is actually a tag, as you will see later). The intrinsic constants also have tags that indicate their host library. Tags are in all lowercase letters, and the names are written with mixed case.

The naming conventions outlined here are included in the Leszynski Naming Conventions (LNC) and have been in use for Visual Basic development since 1995. The following is the general structure of the LNC naming convention, where BaseName is the name of the object unadorned by add-ons:

[
prefix][
tag]
BaseName[
qualifier][
suffix]

The BaseName is the only part of the name that can contain multiple uppercase letters. The tag helps to characterize the base name and identify the class of object. The prefix is an identifier before the tag that narrows the tag character. The prefix can indicate the scope or another characteristic of the variable.

A qualifier is an add-on to the base name that describes how the object appears in context. For example, intAttendMax is the maximum value in the Attend set of values. A suffix adds more detailed information about the object to discriminate between two members of the set that might appear identical without the qualifier. Suffixes are often separated from the base name by an underscore for better visibility.

The most useful element of the naming conventions is the tag. Table 3.6 gives some examples of tags used with VBA and Access objects.

Table 3.6. Examples of using tags with VBA and Access objects.

Data variables have their own set of tags, depending on the type of variable. The tags used for different data types were shown earlier in Table 3.2.

For more information about the hundreds of other examples of LNC, see Leszynski Naming Conventions for Microsoft Access, Version 95.1 for Access 1.x, 2.x, 7.x, published by Kwery Corporation, 1995.

Controlling the Program Flow

The last remaining major elements in event-driven programs are those that control the program flow. In addition to those statements that call a sub or function procedure, there are four major areas of flow control: exiting or pausing the program, branching to another part of the program, looping through the program code, and making decisions about what to do next.

With the End, Exit, and Stop keywords, you can quit running the program altogether, only leave the procedure and return to the previous procedure, or suspend processing while you do something else. Another keyword, DoEvents, yields the application so the operating system can attend to other events. Branching to another part of the program can be conditional, unconditional, or temporary.

The decision-making statements don't actually branch to another location in the program; instead, they choose which set of statements to execute. The If...Then...Else statement executes a series of statements if the condition is met and a second set of statements if not. When there is an expression that has several different values, you can use the Select Case statement, which executes a specific set of statements corresponding to the value of the expression.

Looping lets you repeat a series of program statements a specific number of times or until a certain condition is met. One type of loop repeats the code while or until the condition is True. Another type executes the code a specific number of times. The third type repeats the set of statements for each object in the specified collection, such as all records in the recordset or all forms in the forms collection.

Table 3.7 lists and briefly describes the keywords used to control program flow.

Table 3.7. Flow control keywords.

Each of the flow control statements has a definite syntax that must be followed. Here are some examples:

The On...GoSub syntax is as follows:

On 

expression 
GoSub 
destinationlist

where the expression is numeric and evaluates (or is rounded) to an integer between 0 and 255. The destinationlist is a list of line numbers or labels, each of which corresponds to a value resulting from the expression.

The following is the Do...Loop syntax:

Do 
[{
While|
Until} 
condition]       `Sets up condition for loop. 
                                  `You must use While or Until. 
  [
statements]                    `Do while (or until) condition is met. 
  [
Exit Do]                       `Jumps to next statement after Loop. 
  [
statements]                    `Repeated while (or until) condition is met. 

Loop 
                              `Ends loop.

The following is the If...Then...Else syntax:

If 
[
conditions] 
Then [
ifstatements] [
Else 
elsestatements] 
End If

where at least one condition is required and the ifstatements are executed if the conditions evaluate to True. Otherwise, the elsestatements are executed. The block of code must end with an End If line.

You will see many examples of how to control program flow in the remaining chapters of this book. Refer to the Visual Basic Reference Help for specific information about each of the keywords.

Summary

This chapter has presented a lot of detail about object-oriented, event-driven programming and the elements that are behind the process. You were introduced to variables and constants, how to declare and use them, and what types are available.

One of the uses for variables is to pass them to a procedure or method as an argument to be used during execution.

DAO objects present the dynamic side of database management by addressing all the features of an Access database that are concerned with the data itself: the recordsets, queries, relations, and so on.

Finally, the elements of an event-driven program must include ways to conditionally change the flow of processing. VBA includes strategies for branching to a different course or to one of a set of different courses, or looping until a specified condition exists.

Chapter 4 shows how to create a new database, and Chapter 5 puts much of this chapter's knowledge to use.

© Copyright, Macmillan Computer Publishing. All rights reserved.