Microsoft Office 2000/Visual Basic Programmer's Guide

Other Objects, Properties, and Methods of the Application Object

This section presents an overview of some of the Access objects you will use most often when you are using VBA to work with Access. For complete documentation about all objects in the Access object model and their methods and properties, search the Microsoft Access Visual Basic Reference Help index for the name of the item you want more information about.

Creating, Opening, and Closing an Access Application

You can create a new database, or open and close an existing database, from within Access or by using Automation from another application. The methods discussed in this section are typically used in Automation from another application. If your code is running inside Access, the code typically works with the currently open database, and using these methods is not necessary.

Note   If you are working in another application and you need to access only the data in a database (tables or queries), and not objects such as forms or reports, you use ADO to access the data you need. For more information about using ADO and DAO to work with data in Office applications, see the chapters in Part 3, "Working with Data in Office Solutions."

You use the NewCurrentDatabase method to create a new .mdb-type database. You use the OpenCurrentDatabase and CloseCurrentDatabase methods to open and close an existing .mdb-type database. The following sample is designed to be run from any Office application. It opens the Northwind Traders sample database and prints the portion of the Product Catalog report specified in the OpenReport method:

Sub PrintReport(strCategoryName As String)
   Dim acApp         As Access.Application
   Dim strDBPath     As String

   Const DB_PATH As String = _
      "c:\program files\microsoft office\office\samples\northwind.mdb"

   Set acApp = New Access.Application
   With acApp
      .OpenCurrentDatabase DB_PATH
      ' Print the Product Catalog report.
      .DoCmd.OpenReport "Catalog", acViewNormal, , _
         "CategoryName = '" & strCategoryName & "'"
   End With
   acApp.Quit
   Set acApp = Nothing
End Sub

The PrintReport procedure is available in the modAccessPrintExample module in ExcelSamples.xls file in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.

You use the NewAccessProject, OpenAccessProject, or CreateAccessProject method to open or create an .adp-type database. The NewAccessProject method creates a new .adp-type database and causes it to become active, whereas the CreateAccessProject method only creates an .adp file on disk. You use the OpenAccessProject method to open an existing .adp-type database and the CloseCurrentDatabase method to close an .adp-type database.

Once you create a new database or have a database open, you can use other methods of the Application object to create new Access objects. For example, you use the CreateForm and CreateControl methods to create forms and controls on forms. You use the CreateReport and CreateReportControl methods to create reports and controls on reports. You use the CreateDataAccessPage method to create data access pages. To programmatically add controls to a data access page, you must use script or the Dynamic HTML (DHTML) object model to work with the HTML directly.

Note   Although the methods discussed above let you programmatically create a database and the objects it contains, these methods are typically used only in wizards or add-ins. Normally, you create the database and its objects through the Access user interface and then work with these objects programmatically by using VBA code run from Access or another Office application. For more information about creating and using wizards and add-ins, see Chapter 11, "Add-ins, Templates, Wizards, and Libraries."

Understanding the CurrentData and CurrentProject Objects

In previous versions of Access, you can use DAO objects and their methods and properties to get information about forms, reports, macros, tables, fields, relationships, and queries. For example, you can use Document objects to get information about the tables and queries in a database. There are separate Container objects representing forms, reports, scripts (Access macros), tables (tables and queries), and modules. Each of these Container objects contains a collection of Document objects representing all the objects of the specified type in the current database. Each Document object contains only summary information about each object and does not provide access to the properties of the object or the data it contains. You use DAO Recordset objects to work with the data in a table or query, and you use members of the Forms or Reports collection to work with forms and reports themselves.

However, in Access 2000, DAO is no longer the default programmatic way to interact with data and objects that contain data; therefore, Access 2000 has two new objects — CurrentData and CurrentProject — that contain collections of AccessObject objects, which are used in place of the Container and Document objects available through DAO in previous versions.

Access 2000 uses the CurrentData object to store collections of AccessObject objects that are administered by the database engine; for example, tables and queries in .mdb-type databases, and database diagrams, stored procedures, tables, and views in .adp-type databases. Information about each collection of objects is stored in a collection where each object is represented as an AccessObject object. For example, information about tables is contained in the AllTables collection and information about views is stored in the AllViews collection. To access the CurrentData object, you use the CurrentData property of the Application object. When code is running in an add-in or library database, you would use the CodeData object to refer to the objects managed by the add-in or library database. The CodeData property of the Application object returns the CodeData object.

Note   AccessObject objects contain information about the objects that contain data, but do not provide access to the data itself. To work with data, you use ADO or DAO. For more information about using ADO and DAO to work with data, see the chapters in Part 3, "Working with Data in Office Solutions."

You use the CurrentProject property of the Application object to get information about the Access objects in a database, such as data access pages, forms, macros, modules, and reports. The CurrentProject property of the Application object returns the CurrentProject object, which contains collections of AccessObject objects as well as information about the name, path, and connection of the database itself. For example, the AllForms collection contains information about all the forms in a database, and the AllReports collection contains information about all the reports in the database. When code is running in an add-in or library database, the CodeProject object contains the collections of AccessObject objects in the add-in or library database. The CodeProject property of the Application object returns the CodeProject object.

An AccessObject object exposes the following properties you can use to get information about an object: IsLoaded, Name, Parent, Properties, and Type. These properties are described in the following table.

Note   Collections of AccessObject items are indexed beginning with a value of 0 for the first item in the collection, 1 for the second item, and so on.

The following sample shows how you can use the IsLoaded property to determine if a form, report, or data access page is currently loaded:

With CurrentProject
   Select Case intObjectType
      Case acForm
         IsObjectOpen = .AllForms(strObjName).IsLoaded
      Case acReport
         IsObjectOpen = .AllReports(strObjName).IsLoaded
      Case acDataAccessPage
         IsObjectOpen = .AllDataAccessPages(strObjName).IsLoaded
      Case Else
         Err.Raise ERR_INVALIDOBJTYPE
   End Select
End With

The intObjectType variable would be passed to a procedure as an argument of type acObjectType.

The next sample illustrates how to add custom properties to a form:

Sub AddCustomFormProperty(strFormName As String, _
                         strPropName As String, _
                         varPropValue As Variant)
   ' This procedure illustrates how to add custom
   ' properties to the Properties collection that
   ' is associated with an AccessObject object.
   
   With CurrentProject.AllForms(strFormName).Properties
      .Add strPropName, varPropValue
   End With
End Sub

The AddCustomFormProperty procedure is available in the modSolutionsUtilities module in Solutions9.mdb in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.

Working with the Screen Object

Other Office applications have properties that return a reference to active objects. For example, Word has the ActiveDocument property to determine which document currently has the focus. Excel has properties to return the active Workbook, Worksheet, Cell, Chart, and Window objects. Similarly, PowerPoint has the ActivePresentation property to determine the active presentation.

In Access, you use the Screen object to work with the object or control that currently has the focus. The Screen object has properties that return a reference to the currently active control (on a form or report), data access page, datasheet, form, or report. These properties are useful in code that operates against an object and needs to know only the type of object. For example, the following line of code hides the currently active form:

Screen.ActiveForm.Visible = False

The next example shows how you can use the Screen object to determine which cell in a datasheet is selected:

MsgBox "The selected item is located at: Row " _
   & Screen.ActiveDatasheet.SelTop & ", Column " _
   & Screen.ActiveDatasheet.SelLeft

The Screen object also has properties you can use to work with the previously active control and the mouse pointer.

Important   If you try to refer to an object by using properties of the Screen object and there is no object of that type currently active, an error occurs.

Working with the DoCmd Object

The DoCmd object allows you to carry out various Access commands by using VBA. These commands are called actions when they are used in Access macros and are called methods of the DoCmd object when they are carried out in code.

Note   In other Office applications, the term "macro" is synonymous with a VBA procedure. In Access, macros are completely different from the VBA code you write in a procedure. For more information about Access macros, search the Microsoft Access Help index for "macros, overview," and then open the topic "Macros: What they are and how they work."

Two of the most common tasks that require methods of the DoCmd object are opening and closing Access objects. To open an Access object, you use the DoCmd object's OpenObject method, where Object represents the name of the object you want to open. For example, you use the OpenForm method to open a form, the OpenReport method to open a report, and the OpenQuery method to open a query. All of the OpenObject methods take arguments that specify the object to open and how to display the object. For example, the following code opens the Customers form as read-only in Form view (acNormal) and specifies that only customers in the USA be shown:

DoCmd.OpenForm FormName:="Customers", View:=acNormal, _
   WhereCondition:="Country = 'USA'", DataMode:=acFormReadOnly 

You can use the OpenReport method to open a report in Design view or Print Preview, or you can specify that the report be printed, as in the following example:

DoCmd.OpenReport ReportName:="CustomerPhoneList", _
   View:=acViewNormal, WhereCondition:="Country = 'USA'"

Note   When you use the acViewNormal constant in the view argument of the OpenReport method, the report is not displayed but is printed to the default printer.

You use the DoCmd object's Close method to close an Access object. You can use the optional arguments of the Close method to specify the object to close and whether to save any changes. The following example closes the Customers form without saving changes:

DoCmd.Close acForm, "Customers", acSaveNo

Note   All the arguments of the Close method are optional. If you use the method without specifying arguments, the method closes the currently active object.

You can use the DoCmd object's RunCommand method to run commands that appear on an Access menu or toolbar that do not have separate methods exposed in the Access object model. The RunCommand method uses a collection of enumerated constants to represent available menu and toolbar commands. For more information about the RunCommand method, search the Microsoft Access Visual Basic Reference Help index for "RunCommand method."

Working with the Modules Collection

The Modules collection contains a Module object representing each module that is currently opened for editing. The Module object may represent a standard or class module that is currently open in the Visual Basic Editor or a module associated with a form or report that is open in Design view. You can use the methods and properties of a Module object to get information about the code contained in the module or to insert procedures or lines of code. The objects in this collection are typically used by code running in an add-in or wizard.

For more information about the Modules collection and Module objects, search the Microsoft Access Visual Basic Reference Help index for "Modules collection" or "Module object."

Working with the References Collection

The References collection contains Reference objects representing each reference in the References dialog box (Tools menu in the Visual Basic Editor) to another project or object library. A new Access database contains four references by default. You can add or remove references by using the References dialog box or by using methods of the References collection in VBA code.

For more information about the References collection and Reference objects, search the Microsoft Access Visual Basic Reference Help index for "References collection" or "Reference object."