Working with Access 2003 s DoCmd Methods

Working with Access 2003's DoCmd Methods

Some of the DoCmd methods duplicate menu commands, such as Print, Close, and Apply Filter/Sort. Other methods substitute for mouse actions. For example, you can use the SelectObject method to select a database object in the same way that you select an open window by clicking it or select a database object in the Database window by clicking the object's name. Other DoCmd methods provide capabilities that aren't available through menu commands, such as Beep, which emits a beep sound, or MsgBox, which displays a custom message. It's a better practice, however, to execute Beep and MsgBox directly with VBA statements.

DoCmd Methods by Task

Table 28.2 lists available DoCmd methods grouped by task. Access 2.0 provided 47 macro actions; Access 95 added 2 new DoCmd methods: Save and SetMenuItem. Access 2000 replaced the DoMenuItem action or method with the RunCommand method, which lets you execute any native menu choice or standard toolbar button. The AddMenu item in the Method column of Table 28.2 is an Access 95 and earlier macro action; it's not a method of the DoCmd object and can't be executed from Access VBA code.

Table 28.2. DoCmd Methods Grouped by Task

Task by Category	Method
Manipulating
Copyor rename a database object	CopyObject, Rename
Delete a database object	DeleteObject
Open a table, query, form, report, module	OpenTable, OpenQuery, OpenForm, OpenReport, OpenModule
Open one of the new Access objects	OpenDataAccessPage OpenDiagram OpenStoredProcedure OpenView
Close a database object	Close
Save a database object	Save
Print the current database object	PrintOut,
Select a database window object	SelectObject
Copy or rename an object	CopyObject, Rename
Update data or update the screen	RepaintObject, Requery, ShowAllRecords
Set the value of a field, control, or property	SetValue
Executing
Carry out a menu command	RunCommand
Run a query	OpenQuery, RunSQL
Run a macro or a VBA procedure	RunMacro, RunCode
Run another Windows or DOS application	RunApp
Stop execution of a macro	StopMacro, StopAllMacros
Stop execution of Access	Quit
Stop execution following an event	CancelEvent
Working with Data in Forms and Reports
Select or sort records	ApplyFilter
Find a record	FindRecord, FindNext
Move to a particular location	GoToControl, GoToRecord, GoToPage
Importing and Exporting Data
Output data from a table, query, exporting data form, report, or module in .xls, .rtf, or .txt formats	OutputAs
Include in an email message data from a table, query, form, report, or module in .xls, .rtf, or .txt format	SendObject
transfer data between	TransferDatabase,
Access and other data formats	TransferSpreadsheet, TransferText, TransferSQLDatabase, CopyDatabaseFile
Miscellaneous
Create a custom menubar	AddMenu, SetMenuItem
Sound a beep	Beep
Display or hide a toolbar	ShowToolbar
Send keystrokes to Access or a Windows application	SendKeys
Display an hourglass	Hourglass
Display or hide system information	Echo, SetWarnings
Display custom messages	MsgBox

Note

Methods applicable to macros such as RunMacro, RunCode, StopMacro, and StopAllMacros are obsolete in Access 2003; these DoCmd methods are included for backward compatibility only.

Arguments of DoCmd Methods

Most DoCmd methods require additional information as arguments to specify how the method works. For example, when you use the OpenForm method, you must specify the name of the form to open as the strFormName argument. Also, to specify whether you want to display the Form, Design, Print Preview, or Datasheet view, you must use the intView argument. To specify whether you want to allow editing or adding new records, you must use the intDataMode argument. Finally, to specify whether you want the form to be hidden, behave like a dialog, or be in normal mode, you must use the intWindowMode argument. You specify the values of arguments of the Integer data type by substituting Access intrinsic constants, which use the ac prefix, as in

 DoCmd.OpenForm strFormName, acNormal, strFilterName, strCriterion, acEdit, acDialog, strOpenArg 

The acNormal, acEdit, and acDialog argument values are Access intrinsic constant values for the intView, intDataMode, and intWindowMode arguments, respectively. You can also specify the numeric value of the constant, but there's no guarantee that the numeric values of Access 2003 constants will remain the same in future versions of Access. Thus, using the names of Access intrinsic constants is better programming practice than supplying numeric values for method arguments. When you type DoCmd in the Visual Basic Editor, the statement autocompletion feature lists the Access constants that are applicable to each argument of the method.

The DoCmd.RunCommand method lets you execute any Access menu choice. The method requires a single acCmdMenuChoiceName Access command constant argument, such as acCmdApplyFilterSort, to specify the action to perform. To see the list (called an enumeration or enum) of all command-related constants, select acCommand in Object Browser's Classes list.

The OutputTo, TransferDatabase, TransferSpreadsheet, and TransferText methods deserve special attention by application developers. These bulk transfer methods greatly simplify the data interchange between Access and other Office 2003 applications, such as Excel and Word. The more complex DoCmd methods, together with Access 2003's flexible report generation capabilities, are often the deciding factor when choosing between Visual Basic 6+ or Access 2003 for developing database front ends. Visual Basic doesn't offer equivalents of the bulk transfer Access methods.

The new TransferSQLDatabase and CopyDatabaseFile methods only apply to ADP and SQL Server databases.