Collections, Objects, Properties, and Methods
You’ve already dealt with two of the main collections supported by Access 2007-Forms and Reports. The Forms collection contains all the form objects that are open in your application, and the Reports collection contains all the open report objects.
As you’ll learn in more detail later in this section, collections, objects, properties, and methods are organized in several object model hierarchies. An object has properties that describe the object and methods that are actions you can ask the object to execute. For example, a Form object has a Name property (the name of the form) and a Requery method (to ask the form to requery its record source). Many objects also have collections that define sets of other objects within the object. For example, a Form object has a Controls collection that is the set of all control objects (text boxes, labels, and so on) defined on the form.
You don’t need a thorough understanding of collections, objects, properties, and methods to perform most application tasks. It’s useful, however, for you to know how Access and Visual Basic organize these items so that you can better understand how Access works. If you want to study advanced code examples available in the many sample databases that you can download from public forums, you’ll need to understand collections, objects, properties, and methods and how to correctly reference them.
The Access Application Architecture
An Access 2007 desktop application (.accdb) has two major components-the application engine, which controls the programming and the user interface, and the Microsoft Access Database Engine (DBEngine), which controls the storage of data and the definition of all the objects in your database. An Access project (.adp) also uses the application engine, but it depends on its Connection object to define a link to the Microsoft SQL Server database that contains the tables, views, functions, and stored procedures used by the application.
As you’ll see later, Visual Basic supports two distinct object models (Data Access Objects-DAO, and ActiveX Data Objects-ADO) for manipulating objects stored by the database engine. Figure 19–12 shows the application architecture of Access.
Figure 19–12: You can explore objects in the Access application architecture from the Application object.
When you open a database, the application engine loads the appropriate object collections from the database and application files to enable it to list the names of all the tables, queries, views, database diagrams, stored procedures, forms, reports, macros, and modules to appear in the Navigation Pane. The application engine establishes the top-level Application object, which contains several useful collections, including a Forms collection (all the open forms), a Reports collection (all the open reports), a Modules collection (all the open modules, including form and report modules), a References collection (all Visual Basic library references), and a TempVars collection (all temporary variables created by macros). Each form and report, in turn, contains a Controls collection (all of the controls on the form or report). Among some of the more interesting properties of the Application object is the ADOConnectString property that contains the information you can use to connect to this database from another database.
| Note | For backward compatibility with earlier versions and database files in the .mdb format, the Access object architecture continues to support obsolete collections, objects, and properties. For example, the Application object continues to support a CommandBars collection to allow you to manipulate any custom menus or toolbars that might have been defined using Microsoft Office Access 2003 or earlier. The CurrentProject object continues to support the AIIDataAccessPages collection to allow you to find any data access pages defined in an older .mdb format file; however, the AIIDataAccessPages collection is hidden and appears only when you request it or instruct the Visual Basic Editor to show hidden members. Finally, the Screen object continues to support the ActiveDataAccessPage property, but only as a hidden property. |
The Application object also contains two special objects, the Screen object and the DoCmd object. The Screen object has six very useful properties: ActiveForm, ActiveReport, ActiveDatasheet, ActiveControl, PreviousControl, and MousePointer. Without knowing the actual names, you can reference the control (if any) that currently has the focus, the datasheet (if any) that has the focus, the form (if any) that has the focus, the report (if any) that has the focus, or the name of the control that previously had the focus. You can use the MousePointer property to examine the current status of the mouse pointer (arrow, I-beam, hourglass, and so on) and set the pointer. (Additional details about referencing properties of objects appear later in this chapter.) The DoCmd object lets you execute most macro actions within Visual Basic. See “Running Macro Actions and Menu Commands” on page 1026. If your application is an Access desktop database (.accdb), the DBEngine object under the Application object connects you to the Microsoft Access Database Engine (ACE) to manipulate its objects using the Data Access Objects (DAO) model.
Two properties allow you to directly find out the names of all objects stored in your database without having to call the database engine. In an Access desktop database (.accdb), you can find out the names of all your tables and queries via the CurrentData property. In an Access project file (.adp) that is connected to SQL Server, you can additionally find out the names of database diagrams, stored procedures, functions, and views via this same property. In either type of Access file, you can discover the names of all your forms, reports, macros, and modules via the CurrentProject property. Finally, the FullName property of the CurrentProject object tells you the full path and file name of your application file, and the Name property tells you the file name only.
The Data Access Objects (DAO) Architecture
The first (and older) of the two models you can use to fetch data and examine or create new data objects is the Data Access Objects (DAO) model. This model is best suited for use within Access desktop applications (.accdb) because it provides objects, methods, and properties specifically tailored to the way Access and the Access Database Engine work together. The latest version included with Access 2007 includes enhancements to manipulate the complex Attachment data type and multi-value fields. To use this model, you must ask Visual Basic to load a reference to the Microsoft Office 12.0 Access Database Engine Object Library. To verify that your project includes this reference, open any module in Design view and click References on the Tools menu. If you don’t see the check box for this library selected at the top of the References dialog box, scroll down the alphabetical list until you find the library, select its check box, and click OK to add the reference. Access 2007 creates this reference for you in any new database that you create.
| Inside Out-Is the Rumor That “DAO Is Dead” Really True? | Absolutely not! First, you need to know a bit of history. Beginning with version 9.0 (Access 2000), the Access development team introduced ActiveX Data Objects (ADO) to make it easier to work with SQL Server or other server databases as the data store for Access applications. ADO was touted as the “new direction” for data engine object models because it was designed to be more generic to work with different databases. Access 2000 also introduced the project file format (.adp) that lets you create an Access application linked directly to a database on SQL Server. Both Access 2000 and Access XP (2002) provided a default reference to the ADO library in a new database, and you had to add the DAO library if you wanted to use it. Microsoft also declared DAO “stable” (read: no new enhancements) and began distributing the Access JET database engine as part of Microsoft Data Access Components (MDAC) that you install with your operating system-Windows 98, Windows 2000, Windows XP, or Windows Vista. And so, the developer community began to think that DAO was “dead.” But DAO in many cases really works better if you’re building a desktop application. DAO gives you direct access not only to all your table and query definitions but also forms, reports, macros, and modules. Also, the record source for all forms and reports creates a DAO recordset, so it doesn’t make sense to try to use the entirely different ADO recordset object in your code. As of Access 2002, you can assign a recordset object you open in code directly to the Recordset property of a form. But if you’re using an ADO recordset, features that you expect to work-such as updating across a join or autolookup when you set a foreign key-don’t work correctly. In short, DAO was designed to work best with Access desktop applications. When Microsoft stopped providing DAO as a default reference in new databases, many in the developer community pointed out to Microsoft that this really isn’t a good idea for desktop applications. Microsoft listened to its users and changed the default library back to DAO in Office Access 2003. However, the Access development team couldn’t plan any major enhancements because the JET engine had become part of Windows. For Access 2007, the development team began creating its own new version of the JET engine-now called the Access Database Engine or ACE for short. ACE includes the new features to support the Attachment data type as well as multi-value fields, and it also supports all the features of the old JET engine, but uses an enhanced version of DAO. So no, DAO is not dead-it in a sense has been reborn in the new database engine for Access 2007. |
The Application object’s DBEngine property serves as a bridge between the application engine and the Access Database Engine. The DBEngine property represents the DBEngine object, which is the top-level object in the DAO hierarchy. Figure 19–13 shows you a diagram of the hierarchy of collections defined in the DAO model.
Figure 19–13: The Data Access Objects (DAO) model is specifically designed to manipulate data objects in an Access desktop database.
The DBEngine object controls all the database objects in your database through a hierarchy of collections, objects, and properties. When you open an Access database, the DBEngine object first establishes a Workspaces collection and a default Workspace object (the first object in the Workspaces collection). If you are opening a secured database created in the prior version format (.mdb, .mde) and your workgroup is secured, Access prompts you for a password and a user ID so that the DBEngine can create a User object in the Users collection and a Group object in the Groups collection within the default workspace. If your workgroup is not secured, the DBEngine signs you on as a default user called Admin.
Finally, the DBEngine creates a Database object within the Databases collection of the default Workspace object. If your prior version format file is secured, the DBEngine uses the current User and/or Group object information to determine whether you’re authorized to access any of the objects within the database.
After the DBEngine creates a Database object, the application engine determines whether the database contains any potentially untrustworthy objects. Any database containing tables, queries, macros or Visual Basic code is deemed potentially untrustworthy. If the database is signed with a certificate that you have accepted as trustworthy or the database resides in a trusted location, the application engine enables all code. If the database is not trusted, the application engine displays a security warning message and provides the option to temporarily enable the database.
Next, the application engine checks the database’s application options to find out whether to open a display form, load an application icon, and display a title or to use one or more of the other application options. You can set these options when you have your database open by clicking the Microsoft Office Button, clicking Access Options, and clicking the Current Database category in the Access Options dialog box. After checking the application options, the application engine checks to see whether a macro group named Autoexec exists in the database. If it finds Autoexec, the application engine runs this macro group. In versions 1 and 2, of Access, you’d often use the Autoexec macro group to open a startup form and run startup routines. In Access 2007, however, you should use the application options to specify a display form, and then use the event procedures or embedded macros of the startup form to run your startup routines.
See Chapter 24, “The Finishing Touches,” for details on creating startup properties and custom Ribbons.
You can code Visual Basic procedures that can create additional Database objects in the Databases collection by opening additional .accdb files. Each open Database object has a Containers collection that the DBEngine uses to store the definition (using the Documents collection) of all your tables, queries, forms, reports, macros, and modules.
You can use the TableDefs collection to examine and modify existing tables. You can also create new TableDef objects within this collection. Each TableDef object within the TableDefs collection has a Fields collection that describes all the fields in the table, and an Indexes collection (with a Fields collection for each Index object) that describes any indexes that you created on the table. Likewise, the Relations collection contains Relation objects that describe how tables are related and what integrity rules apply between tables, and each Relation object has a Fields collection that describes the fields that participate in the relation.
The QueryDefs collection contains QueryDef objects that describe all the queries in your database. You can modify existing queries or create new ones. Each QueryDef object has a Parameters collection for any parameters required to run the query and a Fields collection that describes the fields returned by the query. Finally, the Recordsets collection contains a Recordset object for each open recordset in your database, and the Fields collection of each Recordset object tells you the fields in the recordset.
To reference any object within the DAO model, you can always start with the DBEngine object. If you want to work in the current database, that Database object is always the first database in the Databases collection of the first Workspace object. For example:
Dim dbMyDB As DAO.Database Set dbMyDB = DBEngine.Workspaces(0).Databases(0)
Access also provides a handy shortcut object to the current database called CurrentDb. So, you can also establish a pointer to the current database as follows:
Set dbMyDB = CurrentDb
| Note | In one of the examples at the end of this chapter, you’ll learn how to create a new TableDef object and then open a Recordset object on the new table to insert rows. You can find code examples in the Conrad Systems Contacts application that manipulate objects using both DAO and ADO. |
The ActiveX Data Objects (ADO) Architecture
With Access 2000, Microsoft introduced a more generic set of data engine object models to provide references not only to objects stored by the Access Database Engine but also to data objects stored in other database products such as SQL Server. These models are called the ActiveX Data Objects (ADO) architecture. With Access 97 (version 8.0), you could download the Microsoft Data Access Components from the Microsoft Web site to be able to use the ADO model. Access 2000 and Access XP (2002) provided direct support for ADO with built-in libraries and direct references to key objects in the model from the Access Application object. As noted earlier, Access 2003 and Access 2007 provide a default reference to the Data Access Objects library (DAO), not ADO.
Because these models are designed to provide a common set of objects across any data engine that supports the ActiveX Data Objects, they do not necessarily support all the features you can find in the DAO architecture that was specifically designed for the Access Database Engine. For this reason, if you are designing an application that will always run with the Access Database Engine, you are better off using the DAO model. If, however, you expect that your application might one day “upsize” to an ActiveX data engine such as SQL Server, you should consider using the ADO architecture as much as possible. If you create your Access application as an Access project (.adp) linked to SQL Server, you should use only the ADO models.
Figure 19–14 shows you the two major models available under the ADO architecture. The basic ADODB model lets you open and manipulate recordsets via the Recordset object and execute action or parameter queries via the Command object. The ADO Extensions for DDL and Security model (ADOX) allows you to create, open, and manipulate tables, views (non-parameter unordered queries), and procedures (action queries, parameter queries, ordered queries, functions, triggers, or procedures) within the data engine Catalog object (the object that describes the definition of objects in your database). You can also examine and define Users and Groups collections defined in the Catalog object with ADOX.
Figure 19–14: The ActiveX Data Objects (ADODB) and ActiveX Data Objects Extensions for DDL and Security (ADOX) models provide another way to work with the data and objects in your database.
To use the ADODB model, you must instruct Visual Basic to load a reference to the Microsoft ActiveX Data Objects Library. For objects in the ADOX model, you need the Microsoft ADO Extensions for DDL and Security Library. (You should normally find only one version on your computer. If you find multiple versions in the list, select the latest one.) To verify that your project includes these references, open any module in Design view and click References on the Tools menu. If you don’t see the check boxes for these libraries selected at the top of the References dialog box, scroll down the alphabetical list until you find the library you need, select its check box, and click OK to add the reference. Access 2007 does not automatically create a reference to the ADODB library for you in any new database that you create.
Note that there are some objects in common between DAO, ADODB, and ADOX. If you use multiple models in an application, you must be careful to qualify object declarations. For example, a Recordset object type in the DAO model is DAO.Recordset, whereas a Recordset in the ADODB model is ADODB.Recordset. You cannot freely interchange a DAO recordset with an ADODB recordset-they are completely different objects.
The link to ADODB and ADOX is via the CurrentProject.Connection property. After you open an ADODB.Connection object, you can work with other collections, objects, and properties within the ADODB model. Likewise, by establishing an ADOX.Catalog object and setting its Connection property, you can work with any collection, object, or property within the ADOX model.
For all objects within either ADODB or ADOX, you must first establish a base object (connection or catalog, respectively). For example:
Dim cn As ADODB.Connection, rst As New ADODB.Recordset Set cn = CurrentProject.Connection rst.Open = "tblContacts", cn
Or
Dim catThisDB As New ADOX.Catalog, tbl As ADOX.Table Set catThisDB.ActiveConnection = CurrentProject.Connection Set tbl = catThisDB.Tables("tblContacts") | Note | One of the extensive examples at the end of this chapter uses ADO exclusively to manipulate recordsets in the Conrad Systems Contacts sample database. |
Referencing Collections, Objects, and Properties
In the previous chapter, you were introduced to the most common way to reference objects in the Forms and Reports collections, controls on open forms and reports, and properties of controls. There are two alternative ways to reference an object within a collection. The three ways to reference an object within a collection are as follows:
-
CollectionName![Object Name] This is the method you used in the previous chapter. For example: Forms![frmContacts].
-
CollectionName("Object Name”) This method is similar to the first method but uses a string constant (or a string variable) to supply the object name, as in Forms(“frmContacts”) or Forms(strFormName).
-
CollectionName(RelativeObjectNumber) Visual Basic numbers objects within most collections from zero (0) to CollectionName.Count minus 1. You can determine the number of open forms by referring to the Count property of the Forms collection: Forms.Count. You can refer to the second open form in the Forms collection as Forms(1).
Forms and Reports are relatively simple because they are top-level collections within the application engine. As you can see in Figure 19–13, when you reference a collection or an object maintained by the DBEngine, the hierarchy of collections and objects is quite complex. If you want to find out the number of Workspace objects that exist in the Workspaces collection, for example, you need to reference the Count property of the Workspaces collection like this:
DBEngine.Workspaces.Count
(You can create additional workspaces from Visual Basic code.)
Using the third technique described above to reference an object, you can reference the default (first) Workspace object by entering the following:
DBEngine.Workspaces(0)
Likewise, you can refer to the currently open database in a desktop application (.accdb) by entering the following:
DBEngine.Workspaces(0).Databases(0)
When you want to refer to an object that exists in an object’s default (or only) collection (see Figures 22–13 and 22–14), you do not need to include the collection name. Therefore, because the Databases collection is the default collection for the Workspaces collection, you can also refer to the currently open database by entering the following:
DBEngine.Workspaces(0)(0)
As you can see, even with this shorthand syntax, object names can become quite cumbersome if you want to refer, for example, to a particular field within an index definition for a table within the current database in the default Workspace object-or a column within an index definition for a table within the current catalog. For example, using this full syntax, you can reference the name of the first field in the tblContacts table in Contacts.accdb like this:
DBEngine(0)(0).TableDefs("tblContacts").Fields(0).Name (Whew!) If for no other reason, object variables are quite handy to help minimize name complexity.
In particular, you can reduce name complexity by using an object variable to represent the current database. When you set the variable to the current database, you can call the CurrentDb function rather than use the database’s full qualifier. For example, you can declare a Database object variable, set it to the current database by using the CurrentDb function, and then use the Database object variable name as a starting point to reference the TableDefs, QueryDefs, and Recordsets collections that it contains. (See “Assigning an Object Variable-Set Statement” on page 991 for the syntax of the Set statement.) Likewise, if you are going to work extensively with fields in a TableDef object or columns in a Table object, you are better off establishing an object variable that points directly to the TableDef or Table object. For example, you can simplify the complex expression to reference the name of the first field in the tblContacts table in Contacts.accdb like this:
Dim db As DAO.Database, tdf As DAO.TableDef Set db = CurrentDb Set tdf = db.Tabledefs![tblContacts] Debug.Print tdf.Fields(0).Name
| Inside Out-Should I Use CurrentDb or DBEngine.Workspaces(0).Databases(0)? | When you use DBEngine.Workspaces(0).Databases(0) (or DBEngine(0)(0)) to set a database object, Visual Basic establishes a pointer to the current database. You can have only one object variable set to the actual copy of the current database, and you must never close this copy. A safer technique is to set your database variable using the CurrentDb function. Using this technique opens a new database object that is based on the same database as the current one. You can have as many copies of the current database as you like, and you can close them when you finish using them. When you use CurrentDb to establish a pointer to your database, Visual Basic refreshes all the collections and keeps them current. If you want to ensure that the collections are current (for example, to be aware of any added or deleted tables or queries), you must refresh the collections yourself when you use DBEngine(0)(0). The one small advantage to DBEngine(0)(0) is that it is more efficient because it does not refresh all collections when you establish a pointer to it. |
When to Use “!” and “.”
You’ve probably noticed that a complex, fully qualified name of an object or a property in Access 2007 or Visual Basic contains exclamation points (!) and periods (.) that separate the parts of the name.
Use an exclamation point preceding a name when the name refers to an object that is in the preceding object or collection of objects. A name following an exclamation point is generally the name of an object you created (such as a form or a table). Names following an exclamation point must be enclosed in brackets ([]) if they contain embedded blank spaces or a special character, such as an underscore (_). You must also enclose the name of an object you created in brackets if the name is also an Access or SQL reserved word. For example, most objects have a Name property-if you name a control or field “Name,” you must use brackets when you reference your object.
To make this distinction clear, you might want to get into the habit of always enclosing in brackets names that follow an exclamation point, even though brackets are not required for names that don’t use blank spaces or special characters. Access automatically inserts brackets around names in property sheets, design grids, and action arguments.
Use a period preceding a name that refers to a collection name, a property name, or the name of a method that you can perform against the preceding object. (Names following a period should never contain blank spaces.) In other words, use a period when the following name is of the preceding name (as in the TableDefs collection of the Databases(0) object, the Count property of the TableDefs collection, or the MoveLast method of the DAO Recordset object). This distinction is particularly important when referencing something that has the same name as the name of a property. For example, the reference
DBEngine.Workspaces(0).Databases(0).TableDefs(18).Name
refers to the name of the nineteenth TableDef object in the current database. In the Contacts.accdb database, if you use Debug.Print or the Immediate window to display this reference, Visual Basic returns the value tblCompanyContacts. However, the reference
DBEngine.Workspaces(0).Databases(0).TableDefs(18)![Name]
refers to the contents of a field called Name (if one exists) in the nineteenth TableDef object in the current database. In the Conrad Systems Contacts database, this reference returns an error because there is no Name field in the tblCompanyContacts table.
| Inside Out-What About Me? | If you spend some time looking at any of the code behind forms and reports in the sample databases, you’ll notice many references such as Me.Name or Me.ProductName. Whenever you write code in a form or report module, you’ll likely need to reference some of the controls on the form or report or some of the properties of the form or report. You already know that you can reference an open form by using, for example
Forms![frmProducts] And to reference a control on the open frmProducts form, you could use
Forms![frmProducts]![ProductName] Rather than type the collection name (Forms) and the form name (frmProducts) each time, you can use a shortcut-Me. This special keyword is a reference to the object where your code is running. Also, when Access opens a form, it loads the names of all controls you defined on the form as properties of the form-which are also properties of the Me object (It also does the same for controls on open reports.) So, you can reference the ProductName control in code behind the frmProducts form by entering:
Me.ProductName This can certainly make entering code faster. Also, because Me is an object, your code executes more quickly. |
| Inside Out-Is It Possible to Reference in Visual Basic Variables Created by Macros? | You bet! As you learned in Chapter 18, “Automating Your Application with Macros,” you can use the new SetTempVar, RemoveTempVar, and RemoveAllTempVars actions to create, modify, and inspect values that you can pass from one macro to another. If you create an application that uses both macros and Visual Basic, you can also create, modify, and inspect these variables by using the TempVars collection. Unlike most collections in Access where you must first create an object before you can reference it, you can both create and set a macro temporary variable by simply assigning a value to a name in the TempVars collection. For example, to create and set a temporary variable called MyTempVar, use the following:
TempVars!MyTempVar = "Value to pass to a macro" Temporary variables are the Variant data type, so you can assign a string, a number, or a date/time value to a member of the TempVars collection. To delete a temporary variable, use the Remove method as follows:
TempVars.Remove MyTempVar To remove all temporary variables, use the RemoveAll method as follows:
TempVars.RemoveAll But be careful. If you reference a temporary variable that does not exist yet, you won’t get any error. If you misspell a temporary variable name, Access temporarily creates the variable and returns the value Null. |
Assigning an Object Variable-Set Statement
Use the Set statement to assign an object or object reference to an object variable.
Syntax
Set objectvariablename = [New] objectreference
Notes
As noted earlier, you can use object variables to simplify name references. Also, using an object variable is less time-consuming than using a fully qualified name. At run time, Visual Basic must always parse a qualified name to first determine the type of object and then determine which object or property you want. If you use an object variable, you have already defined the type of object and established a direct pointer to it, so Visual Basic can quickly go to that object. This is especially important if you plan to reference, for example, many controls on a form. If you create a form variable first and then assign the variable to point to the form, referencing controls on the form via the form variable is much simpler and faster than using a fully qualified name for each control.
You must first declare objectvariablename using a Dim, Private, Public, or Static statement. The object types you can declare include AccessObject, Application, ADOX.Catalog, ADOX.Column, ADODB.Command, ADOX.Command, ADODB.Connection, DAO.Container, Control, DAO.Database, DAO.Document, ADODB.Error, DAO.Error, ADODB.Field, DAO.Field, DAO.Field2, Form, ADOX.Group, DAO.Group, ADOX.Index, DAO.Index, ADOX.Key, ADODB.Parameter, DAO.Parameter, ADOX.Procedure, ADODB.Property, ADOX.Property, DAO.Property, DAO.QueryDef, ADODB.Recordset, DAO.Recordset, DAO.Recordset2, DAO.Relation, Report, ADOX.Table, DAO.TableDef, ADOX.User, DAO.User, ADOX.View, and DAO.Workspace object. You can also declare a variable as the generic Object data type and set it to any object (similar to the Variant data type). In addition, you can declare a variable as an instance of the class defined by a class module. The object type must be compatible with the object type of objectreference. You can use another object variable in an object-reference statement to qualify an object at a lower level. (See the examples that follow.) You can also use an object method to create a new object in a collection and assign that object to an object variable. For example, it’s common to use the OpenRecordset method of a QueryDef or TableDef object to create a new Recordset object. See the example in the next section, “Object Methods.”
An object variable is a reference to an object, not a copy of the object. You can assign more than one object variable to point to the same object and change a property of the object. When you do that, all variables referencing the object will reflect the change as well. The one exception is that several Recordset variables can refer to the same recordset, but each can have its own Bookmark property pointing to different rows in the recordset. If you want to create a new instance of an object, include the New keyword.
Examples
To create a variable reference to the current database, enter the following:
Dim dbMyDB As DAO.Database Set dbMyDB = CurrentDb
To create a variable reference to the tblContacts table in the current database using the dbMyDB variable defined above, enter the following:
Dim tblMyTable As DAO.TableDef Set tblMyTable = dbMyDB![tblContacts]
Notice that you do not need to explicitly reference the TableDefs collection of the database, as in dbMyDB.TableDefs![tblContacts] or dbMyDB.TableDefs(“tblContacts”), because TableDefs is the default collection of the database. Visual Basic assumes that [tblContacts] refers to the name of an object in the default collection of the database.
To create a variable reference to the Notes field in the tblContacts table using the tblMyTable variable defined above, enter the following:
Dim fldMyField As DAO.Field Set fldMyField = tblMyTable![Notes]
Again, you do not need to include a specific reference to the Fields collection of the TableDef object, as in tblMyTable.Fields![Notes], because Fields is the default collection
To create a variable reference to the catalog for the current database, enter the following:
Dim catThisDB As New ADOX.Catalog catThisDB.ActiveConnection = CurrentProject.Connection
Note that you must use the New keyword because there’s no way to open an existing catalog without first establishing a connection to it. You open a catalog by declaring it as a new object and assigning a Connection object to its ActiveConnection property. The preceding example takes advantage of the existence of the Application.Current-Project.Connection property rather than first setting a Connection object. If you already have another Catalog object open, you can create a copy of it by using
Dim catCopy As ADOX.Catalog Set catCopy = catThisDB
To create a variable reference to the tblContacts table in the current database using the catThisDB variable defined above, enter the following:
Dim tblMyTable As ADOX.Table Set tblMyTable = catThisDB![tblContacts]
Notice that you do not need to explicitly reference the Tables collection of the database, as in catThisDB.Tables![tblContacts] or catThisDB.Tables(“tblContacts”), because Tables is the default collection of the catalog. Visual Basic assumes that [tblContacts] refers to the name of an object in the default collection of the catalog.
To create a variable reference to the Notes column in the tblContacts table using the tblMyTable variable defined above, enter the following:
Dim colMyColumn As ADOX.Column Set colMyColumn = tblMyTable![Notes]
Again, you do not need to explicitly reference the Columns collection of the Table object, as in tblMyTable.Columns![Notes] because the Columns collection is the default collection of a Table object.
Object Methods
When you want to apply an action to an object in your database (such as open a query as a recordset or go to the next row in a recordset), you apply a method of either the object or an object variable that you have assigned to point to the object. In some cases, you’ll use a method to create a new object. Many methods accept parameters that you can use to further refine how the method acts on the object. For example, you can tell the DAO OpenRecordset method whether you’re opening a recordset on a local table, a dynaset (a query-based recordset), or a read-only snapshot.
Visual Basic supports many different object methods-far more than there’s room to properly document in this book. Perhaps one of the most useful groups of methods is the group you can use to create a recordset and then read, update, insert, and delete rows in the recordset.
Working with DAO Recordsets
To create a recordset, you must first declare a Recordset object variable. Then open the recordset using the DAO OpenRecordset method of the current database (specifying a table name, a query name, or an SQL statement to create the recordset) or the OpenRecordset method of a DAO.QueryDef, DAO.TableDef, or other DAO.Recordset object. (As you’ll learn in “Working with ADO Recordsets,” if you’re working in ADO, you use the Open method of a New ADODB.Recordset object.)
In DAO, you can specify options to indicate whether you’re opening the recordset as a local table (which means you can use the Seek method to quickly locate rows based on a match with an available index), as a dynaset, or as a read-only snapshot. For updatable recordsets, you can also specify that you want to deny other updates, deny other reads, open a read-only recordset, open the recordset for append only, or open a readonly forward scroll recordset (which allows you to move only forward through the records and only once).
The syntax to use the OpenRecordset method of a Database object is as follows:
Set RecordSetObject = DatabaseObject.OpenRecordset(source, [type], [options], [lockoptions])
RecordSetObject is a variable you have declared as DAO.Recordset, and DatabaseObject is a variable you have declared as DAO.Database. Source is a string variable or literal containing the name of a table, the name of a query, or a valid SQL statement. Table 19–2 describes the settings you can supply for type, options, and lockoptions.
| Setting | Description |
|---|---|
| Type (Select one) | |
| dbOpenTable | Returns a table recordset. You can use this option only when source is a table local to the database described by the Database object. Source cannot be a linked table. You can establish a current index in a table recordset and use the Seek method to find rows using the index. If you do not specify a type, OpenRecordset returns a table if source is a local table name. |
| dbOpenDynaset | Returns a dynaset recordset. Source can be a local table, a linked table, a query, or an SQL statement. You can use the Find methods to search for rows in a dynaset recordset. If you do not specify a type, OpenRecordset returns a dynaset if source is a linked table, a query, or an SQL statement. |
| dbOpenSnapshot | Returns a read-only snapshot recordset. You won’t see any changes made by other users after you open the recordset. You can use the Find methods to search for rows in a snapshot recordset. |
| dbOpenForwardOnly | Returns a read-only snapshot recordset that you can move forward through only once. You can use the MoveNext method to access successive rows. |
| Options (You can select multiple options, placing a plus sign between option names to add them together) | |
| dbAppendOnly | Returns a table or dynaset recordset that allows inserting new rows only. You can use this option only with the dbOpenTable and dbOpenDynaset types. |
| dbSeeChanges | Asks Access to generate a run-time error in your code if another user changes data while you are editing it in the recordset. |
| dbDenyWrite | Prevents other users from modifying or inserting records while your recordset is open. |
| dbDenyRead | Prevents other users from reading records in your open recordset. |
| dblnconsistent | Allows you to make changes to all fields in a multiple table recordset (based on a query or an SQL statement), including changes that would be inconsistent with any join defined in the query. For example, you could change the customer identifier field (foreign key) of an orders table so that it no longer matches the primary key in an included customers table-unless referential integrity constraints otherwise prevent you from doing so. You cannot include both dblnconsistent and dbConsistent. |
| dbConsistent | Allows you to only make changes in a multiple table recordset (based on a query or an SQL statement) that are consistent with the join definitions in the query. For example, you cannot change the customer identifier field (foreign key) of an orders table so that its value does not match the value of any customer row in the query. You cannot include both dblnconsistent and dbConsistent. |
| Lockoptions (Select one) | |
| dbPessimistic | Asks Access to lock a row as soon as you place the row in an editable state by executing an Edit method. This is the default if you do not specify a lock option. |
| dbOptimistic | Asks Access to not attempt to lock a row until you try to write it to the database with an Update method. This generates a run-time error if another user has changed the row after you executed the Edit method. |
Open table as spreadsheet For example, to declare a recordset for the tblFacilities table in the Housing Reservations (Housing.accdb) database and open the recordset as a table so that you can use its indexes, enter the following:
Dim dbHousing As DAO.Database Dim rcdFacilities As DAO.RecordSet Set dbHousing = CurrentDb Set rcdFacilities = dbHousing.OpenRecordSet("tblFacilities", _ dbOpenTable) To open the qryContactProducts query in the Conrad Systems Contacts database (Contacts.accdb) as a dynaset, enter the following:
Dim dbContacts As DAO.Database Dim rcdContactProducts As DAO.RecordSet Set dbContacts = CurrentDb Set rcdContactProducts = _ dbContacts.OpenRecordSet("qryContactProducts") (Note that opening a recordset as a dynaset is the default when the source is a query.)
| Note | Any table recordset or dynaset recordset based on a table is updatable. When you ask Access to open a dynaset on a table, Access internally builds a query that selects all columns from the table. A dynaset recordset based on a query will be updatable if the query is updatable. See “Limitations on Using Select Queries to Update Data” on page 468 for details. |
After you open a recordset, you can use one of the Move methods to move to a specific record. Use recordset.MoveFirst to move to the first row in the recordset. Other Move methods include MoveLast, MoveNext, and MovePrevious. If you want to move to a specific row in a dynaset recordset, use one of the Find methods. You must supply a string variable containing the criteria for finding the records you want. The criteria string looks exactly like an SQL WHERE clause but without the WHERE keyword. (See Article 2, “Understanding SQL,” on the companion CD.) For example, to find the first row in the qryContactProducts query’s recordset whose SoldPrice field is greater than $200, enter the following:
rcdContactProducts.FindFirst "SoldPrice > 200"
To delete a row in an updatable recordset, move to the row you want to delete and then use the Delete method. For example, to delete the first row in the qryContactProducts query’s recordset that hasn’t been invoiced yet (the Invoiced field is false), enter the following:
Dim dbContacts As DAO.Database Dim rcdContactProducts As DAO.RecordSet Set dbContacts = CurrentDb Set rcdContactProducts = _ dbContacts.OpenRecordSet("qryContactProducts") rcdContactProducts.FindFirst "Invoiced=0" ' Test the recordset NoMatch property for "not found" If Not rcdContactProducts.NoMatch Then rcdContactProducts.Delete End If If you want to update rows in a recordset, move to the first row you want to update and then use the Edit method to lock the row and make it updatable. You can then refer to any of the fields in the row by name to change their values. Use the Update method on the recordset to save your changes before moving to another row. If you do not use the Update method before you move to a new row or close the recordset, the database discards your changes.
For example, to increase by 10 percent the SoldPrice entry of the first row in the rcdContactProducts query’s recordset whose SoldPrice value is greater than $200, enter the following:
Dim dbContacts As DAO.Database Dim rcdContactProducts As DAO.RecordSet Set dbContacts = CurrentDb Set rcdContactProducts = _ dbContacts.OpenRecordSet("qryContactProducts") rcdContactProducts.FindFirst "SoldPrice > 200" ' Test the recordset NoMatch property for "not found" If Not rcdContactProducts.NoMatch Then rcdContactProducts.Edit rcdContactProducts![SoldPrice] = _ rcdContactProducts![SoldPrice] * 1.1 rcdContactProducts.Update End If To insert a new row in a recordset, use the AddNew method to start a new row. Set the values of all required fields in the row, and then use the Update method to save the new row. For example, to insert a new company in the Conrad Systems Contacts tblCompanies table, enter the following:
Dim dbContacts As DAO.Database Dim rcdCompanies As DAO.RecordSet Set dbContacts = CurrentDb Set rcdCompanies = _ dbContacts.OpenRecordSet("tblCompanies") rcdCompanies.AddNew rcdCompanies![CompanyName] = "Winthrop Brewing Co." rcdCompanies![Address] = "155 Riverside Ave." rcdCompani es![City] = "Wi nthrop" rcdCompanies![StateOrProvince] = "WA" rcdCompanies![PostalCode] = "98862" rcdCompanies![PhoneNumber]= "(509) 555-8100" rcdCompanies.Update Note that because all the main data tables in Contacts.accdb are linked tables, rcdCompanies is a dynaset recordset, not a table recordset.
Manipulating Complex Data Types Using DAO
New in Access 2007 are complex data types-the Attachment data type or any field defined as multi-value. A complex data type lets you store multiple values or objects in a field within a single record. Access 2007 accomplishes this by building hidden tables that contain one row per multiple value stored. You can manipulate these rows in a recordset in code, but only using DAO.
To work with data in a complex data type field, you must first open a recordset on the table containing the field. You can either open the table directly or open a query that includes the table and its complex field(s). The secret to dealing with complex fields is the Value property of the field in the recordset returns a DAO.Recordset2 object. So, you can set a declared DAO.Recordset2 variable to the Value property to open a recordset on the hidden table. You can manipulate this recordset exactly as you can any other DAO recordset, including using the Find and Move methods, and the Edit, AddNew, Update, and Delete methods.
When the complex field is a multi-value field, the recordset returned from the Value property of the parent field contains a single field called Value. You’ll find one row per multiple value stored in the complex field. When the complex field is an Attachment data type, the recordset returned from the Value property of the parent field contains three fields-FileData, FileName, and FileType. The FileData field in an attachment complex recordset supports one method, LoadFromFile, that lets you insert the complex OLE data into the record by supplying a file location and name.
The tblContacts table in the Contacts sample database contains both a multi-value field (ContactType) and an attachment field (Photo). In the modExamples module in the Contacts.accdb database, you can find the following code that displays in the Immediate window the values from both fields for all contact records.
Public Sub ListContactComplex() ' An example of listing all the complex values in the Contacts table Dim db As DAO.Database, rst As DAO.Recordset, rstComplex As DAO.Recordset2 Dim fld As DAO.Field2 ' Point to this database Set db = CurrentDb ' Open a recordset on tblContacts Set rst = db.OpenRecordset("SELECT * FROM tblContacts") ' Loop through all the records Do Until rst.EOF ' Dump out the ID and name Debug.Print rst!ContactID, rst!LastName, rst!FirstName ' Get the contact type complex field Set rstComplex = rst!ContactType.Value ' Loop through them all Do Until rstComplex.EOF ' Dump out each value Debug.Print " ", "Contact Type: ", rstComplex!Value ' Get the next rstComplex.MoveNext Loop ' Get the Photo Attachment recordset Set rstComplex = rst!Photo.Value ' Loop though them all Do Until rstComplex.EOF ' Dump out the data Debug.Print " ", "Photo FileName: ", rstComplex!FileName, _ " File Type: ", rstComplex!FileType ' Get the next rstComplex.MoveNext Loop ' Get the next contact rst.MoveNext Loop ' Close out rst.Close Set rst = Nothing Set rstComplex = Nothing Set db = Nothing End Sub If you want to find the record for John Viescas and add the value Trainer to the ContactType field, do the following:
Dim db As DAO.Recordset, rst As DAO.Recordset, rstComplex As DAO.Recordset2 ' Set a pointer to the current database Set db = CurrentDb ' Open the contacts table Set rst = db.OpenRecordset("tblContacts", dbOpenDynaset) ' Find the record for Viescas rst.FindFirst "LastName = 'Viescas'" ' Make sure we found it If Not rst.NoMatch Then ' Get the ContactType recordset Set rstComplex = rst!ContactType.Value ' Add a new row rstComplex.AddNew ' Insert the new value rstComplex.Value = "Trainer" ' Save the new value rstComplex.Update End If ' Close out rst.Close Set rst = Nothing Set rstComplex = Nothing Set db = Nothing To find the contact record for John Viescas, check for the value Trainer in the ContactType field, and delete it if it exists, do the following:
Dim db As DAO.Recordset, rst As DAO.Recordset, rstComplex As DAO.Recordset2 ' Set a pointer to the current database Set db = CurrentDb ' Open the contacts table Set rst = db.OpenRecordset("tblContacts", dbOpenDynaset) ' Find the record for Viescas rst.FindFirst "LastName = 'Viescas'" ' Make sure we found it If Not rst.NoMatch Then ' Get the ContactType recordset Set rstComplex = rst!ContactType.Value ' See if Trainer exists rstComplex.FindFirst "Value = 'Trainer '" ' If it exists, If Not rstComplex.NoMatch Then ' Delete it rstComplex.Delete End If End If ' Close out rst.Close Set rst = Nothing Set rstComplex = Nothing Set db = Nothing To check to see if the Photo field for contact Jeff Conrad contains a file named JeffConrad.docx and add it if it does not, the code is as follows:
Dim db As DAO.Recordset, rst As DAO.Recordset, rstComplex As DAO.Recordset2 ' Set a pointer to the current database Set db = CurrentDb ' Open the contacts table Set rst = db.OpenRecordset("tblContacts", dbOpenDynaset) ' Find the record for Conrad rst.FindFirst "LastName = 'Conrad'" ' Make sure we found it If Not rst.NoMatch Then ' Get the Photo recordset Set rstComplex = rst!Photo.Value ' See if the JeffConrad.docx file exists rstComplex.FindFirst "FileName = 'JeffConrad.docx'" ' If it does not exist, If rstComplex.NoMatch Then ' Start a new attachment record rstComplex.Addnew ' Load the file rstComplex!FileData.LoadFromFile _ "C:\Microsoft Press\Access 2007 Inside Out\Documents\JeffConrad.docx" ' Save the new row rstComplex.Update End If End If ' Close out rst.Close Set rst = Nothing Set rstComplex = Nothing Set db = Nothing
Working with ADO Recordsets
Recordsets in ADO offer many of the same capabilities and options as recordsets in DAO, but the terminology is somewhat different. Because you will most often use ADO with data stored in a server database such as SQL Server, the options for an ADO recordset are geared toward server-based data. For example, ADO uses the term cursor to refer to the set of rows returned by the server. Fundamentally, a cursor is a pointer to each row you need to work with in code. Depending on the options you choose (and the options supported by the particular database server), a cursor might also be read-only, updatable, or forward-only. A cursor might also be able to reflect changes made by other users of the database (a keyset or dynamic cursor), or it might present only a snapshot of the data (a static cursor).
To open an ADO recordset, you must use the Open method of a new ADO Recordset object. The syntax to use the Open method of a Recordset object is as follows:
RecordSetObject.Open [source], [connection], [cursortype], [locktype], [options]
RecordSetObject is a variable you have declared as a New ADO.Recordset. Source is a Command object, a string variable, or string literal containing the name of a table, the name of a view (the SQL Server term for a query), the name of a stored procedure, the name of a function that returns a table, or a valid SQL statement. A stored procedure might be a parameter query or a query that specifies sorting of rows from a table or view. A function might also accept parameters. If you supply a Command object as the source, you do not need to supply a connection (you define the connection in the Command object). Otherwise, connection must be the name of a Connection object that points to the target database.
Table 19–3 describes the settings you can supply for cursortype, lockoptions, and options.
| Setting | Description |
|---|---|
| CursorType (Select one) | |
| adOpenForwardOnly | Returns a read-only snapshot cursor (recordset) that you can move forward through only once. You can use the MoveNext method to access successive rows. If you do not supply a CursorType setting, adOpenForwardOnly is the default. |
| adOpenKeyset | Returns a Keyset cursor. This is roughly analogous to a DAO dynaset. If you are using ADO to open a recordset against a source in an Access .accdb file, you should use this option to obtain a recordset that behaves most like a DAO recordset. In this type of cursor, you will see changes to rows made by other users, but you will not see new rows added by other users after you have opened the cursor. |
| adOpenDynamic | Returns a dynamic cursor. This type of cursor lets you see not only changes made by other users but also added rows. Note, however, that certain key properties you might depend on in a DAO recordset such as RecordCount might not exist or might always be zero. |
| adOpenStatic | Returns a read-only snapshot cursor. You won’t be able to see changes made by other users after you’ve opened the cursor. |
| LockType (Select one) | |
| adLockReadOnly | Provides no locks. The cursor is read-only. If you do not provide a lock setting, this is the default. |
| adLockPessimistic | Asks the target database to lock a row as soon as you place the row in an editable state by executing an Edit method. |
| adLockOptimistic | Asks the target database to not attempt to lock a row until you try to write it to the database with an Update method. This generates a run-time error in your code if another user has changed the row after you executed the Edit method. You should use this option when accessing rows in an Access .accdb file. |
| Options (You can combine one Cmd setting with one Async setting with a plus sign) | |
| adCmdText | Indicates that source is an SQL statement. |
| adCmdTable | Indicates that source is a table name (or a query name in a desktop database). In DAO, this is analogous to opening a dynaset recordset on a table. |
| adCmdTableDirect | Indicates that source is a table name. This is analogous to a DAO dbOpenTable. |
| adCmdStoredProc | Indicates that source is a stored procedure. In DAO, this is analogous to opening a dynaset on a sorted query. |
| adAsyncFetch | After fetching the initial rows to populate the cursor, additional fetching occurs in the background. If you try to access a row that has not been fetched yet, your code will wait until the row is fetched. |
| adAsyncFetchNonBlocking | After fetching the initial rows to populate the cursor, additional fetching occurs in the background. If you try to access a row that has not been fetched yet, your code will receive an end of file indication. |
For example, to declare a recordset for the tblFacilities table in the Housing Reservations database (Housing.accdb) and open the recordset as a table so you can use its indexes, enter the following:
Dim cnThisConnect As ADODB.Connection Dim rcdFacilities As New ADODB.RecordSet Set cnThisConnect = CurrentProject.Connection rcdFacilities.Index = "PrimaryKey" rcdBooks.Open "tblFacilities", cnThisConnect, adOpenKeyset, _ adLockOptimistic, adCmdTableDirect
Note that you must establish the index you want to use before you open the recordset.
To open the qryContactProducts query in the Conrad Systems Contacts database as a keyset, enter the following:
Dim cnThisConnect As DODB.Connection Dim rcdContactProducts As New ADODB.RecordSet Set cnThisConnect = CurrentProject.Connection rcdContactProducts.Open "qryContactProducts", _ cnThisConnect, dOpenKeyset, dLockOptimistic, _ adCmdTable
After you open a recordset, you can use one of the Move methods to move to a specific record. Use recordset.MoveFirst to move to the first row in the recordset. Other Move methods include MoveLast, MoveNext, and MovePrevious. If you want to search for a specific row in the recordset, use the Find method or set the recordset’s Filter property. Unlike the Find methods in DAO, the Find method in ADO is limited to a single simple test on a column in the form “<column-name> <comparison> <comparisonvalue>”. Note that to search for a Null value, you must say: “[SomeColumn]=Null”, not “[SomeColumn] Is Null” as you would in DAO. Also, <comparison> can be only <, >, <=, >=, <>, =, or LIKE. Note that if you want to use the LIKE keyword, you can use either the ANSI wildcards “%” and “_” or the Access ACE/JET wildcards “*” and “?”, but the wildcard can appear only at the end of the <comparison-value> string.
If you want to search for rows using a more complex filter, you must assign a string variable or an expression containing the criteria for finding the records you want to the Filter property of the recordset. This limits the rows in the recordset to only those that meet the filter criteria. The criteria string must be made up of the simple comparisons that you can use with Find, but you can include multiple comparisons with the AND or OR Boolean operators.
For example, to find the first row in the qryContactProducts query’s recordset whose SoldPrice field is greater than $200, enter the following:
rcdContactProducts.MoveFirst rcdContactProducts.Find SoldPrice > 200" ' EOF property will be true if nothing found If Not rcdContactProducts.EOF Then ' Found a record!
To find all rows in qryContactProducts where the product was sold after February 1, 2007, and SoldPrice is greater than $200, enter the following:
rcdContactProducts.Filter = & "DateSold > #2/1/2007# AND SoldPrice > 200" ' EOF property will be true if filter produces no rows If Not rcdODetails.EOF Then ' Found some rows!
To delete a row in a keyset, simply move to the row you want to delete and then use the Delete method. For example, to delete the first row in the qryContactProducts query’s recordset that hasn’t been invoiced yet (the Invoiced field is false), enter the following:
Dim cnThisConnect As ADODB.Connection Dim rcdContactProducts As New ADODB.RecordSet Set cnThisConnect = CurrentProject.Connection rcdContactProducts.Open "qryContactProducts", _ cnThisConnect, adOpenKeyset, adLockOptimistic, _ adCmdTable rcdContactProducts.MoveFirst rcdContactProducts.Find "Invoiced = 0" ' Test the recordset EOF property for "not found" If Not rcdContactProducts.EOF Then rcdContactProducts.Delete End If
If you want to update rows in a recordset, move to the first row you want to update. Although ADO does not require you to use the Edit method to lock the row and make it updatable, you can optionally use the Edit method to signal your intention to the database engine. You can refer to any of the updatable fields in the row by name to change their values. You can use the Update method on the recordset to explicitly save your changes before moving to another row. ADO automatically saves your changed row when you move to a new row. If you need to discard an update, you must use the CancelUpdate method of the recordset object.
For example, to increase by 10 percent the SoldPrice entry of the first row in the rcdContactProducts query’s recordset whose SoldPrice value is greater than $200, enter the following:
Dim cnThisConnect As ADODB.Connection Dim rcdContactProducts As New ADODB.RecordSet Set cnThisConnect = CurrentProject.Connection rcdContactProducts.Open "qryContactProducts", _ cnThisConnect, adOpenKeyset, adLockOptimistic, _ adCmdTable rcdContactProducts.Filter "SoldPrice > 200" ' Test the recordset EOF property for "not found" If Not rcdContactProducts.EOF Then rcdContactProducts![SoldPrice] = _ rcdContactProducts![SoldPrice] * 1.1 rcdContactProducts.MoveNext End If
To insert a new row in a recordset, use the AddNew method to start a new row. Set the values of all required fields in the row, and then use the Update method to save the new row. For example, to insert a new company in the Conrad Systems Contacts tblCompanies table, enter the following:
Dim cnThisConnect As ADODB.Connection Dim rcdCompanies As New ADODB.RecordSet Set cnThisConnect = CurrentProject.Connection rcdCompanies.Open "tblCompanies", cnThisConnect, _ adOpenKeyset, adLockOptimistic, adCmdTable rcdCompanies.AddNew rcdCompanies![CompanyName] = "Winthrop Brewing Co." rcdCompanies![Address] = "155 Riverside Ave." rcdCompanies![City] = "Winthrop" rcdCompanies![StateOrProvince] = "WA" rcdCompanies![PostalCode] = "98862" rcdCompanies![PhoneNumber] = "(509) 555-8100" rcdCompanies.Update
Other Uses for Object Methods
As you’ll learn later in this chapter in more detail, you must use a method of the DoCmd object to execute the equivalent of most macro actions within Visual Basic. You must use the RunCommand method of either the Application or DoCmd object to execute commands you can find on any of the Access menus.
You can also define a public function or subroutine (see the next section) within the module associated with a Form or Report object and execute that procedure as a method of the form or report. If your public procedure is a function, you must assign the result of the execution of the method to a variable of the appropriate type. If the public procedure is a subroutine, you can execute the form or report object method as a Visual Basic statement. For more information about object methods, find the topic about the object of interest in Help, and then click the Methods hyperlink.
EAN: 2147483647
Pages: 234